XACT_STATE()

SELECT XACT_STATE()


IF XACT_STATE() = 1
ROLLBACK;

1 - TRANSACTION BEGIN
0 - NO TRANSACTION

TRY CATCH

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO

sp_spaceused 'Campaigns'


Name rows reserved data index_size unused

Campaigns 0 40 KB 16 KB 24 KB 0 KB

Sysobjects

--'sysobjects' tiene objectos (tablas, Stores, Funcions, etc)
--'syscolumns' tiene objectos (tablas, Stores, Funcions, etc)

SELECT SO.NAME, SC.NAME

FROM sysobjects SO INNER JOIN syscolumns SC
ON SO.ID = SC.ID

WHERE SO.XTYPE = N'U'

ORDER BY SO.NAME, SC.NAME

Column name	Data type	Description
name	sysname	Object name
id	int	Object identification number
xtype	char(2)	Object type. Can be one of the following object types: AF = Aggregate function (CLR) C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint L = Log FN = Scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = In-lined table-function IT = Internal table P = Stored procedure PC = Assembly (CLR) stored-procedure PK = PRIMARY KEY constraint (type is K) RF = Replication filter stored procedure S = System table SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TF = Table function TR = SQL DML Trigger TT = Table type U = User table UQ = UNIQUE constraint (type is K) V = View X = Extended stored procedure
uid	smallint	Schema ID of the owner of the object. For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner. Important If you use any of the following SQL Server DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects.CREATE | ALTER | DROP USERCREATE | ALTER | DROP ROLECREATE | ALTER | DROP APPLICATION ROLECREATE SCHEMA ALTER AUTHORIZATION ON OBJECT Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog.
info	smallint	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
status	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
base_schema_ver	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
replinfo	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
parent_obj	int	Object identification number of the parent object. For example, the table ID if it is a trigger or constraint.
crdate	datetime	Date the object was created.
ftcatid	smallint	Identifier of the full-text catalog for all user tables registered for full-text indexing, and 0 for all user tables that are not registered.
schema_ver	int	Version number that is incremented every time the schema for a table changes. Always returns 0.
stats_schema_ver	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
type	char(2)	Object type. Can be one of the following values: AF = Aggregate function (CLR) C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint FN = Scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued functionIF = In-lined table-function IT - Internal table K = PRIMARY KEY or UNIQUE constraint L = Log P = Stored procedure PC = Assembly (CLR) stored-procedure R = Rule RF = Replication filter stored procedure S = System table SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TF = Table function TR = SQL DML Trigger TT = Table type U = User table V = View X = Extended stored procedure
userstat	smallint	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
sysstat	smallint	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
indexdel	smallint	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
refdate	datetime	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
version	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
deltrig	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
instrig	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
updtrig	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
seltrig	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
category	int	Used for publication, constraints, and identity.
cache	smallint	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Transacciones - ACID

En concreto ACID es un acrónimo de Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad en español.

Atomicidad: es la propiedad que asegura que la operación se ha realizado o no, y por lo tanto ante un fallo del sistema no puede quedar a medias.
Consistencia: es la propiedad que asegura que sólo se empieza aquello que se puede acabar. Por lo tanto se ejecutan aquellas operaciones que no van a romper la reglas y directrices de integridad de la base de datos.
Aislamiento: es la propiedad que asegura que una operación no puede afectar a otras. Esto asegura que la realización de dos transacciones sobre la misma información sean independientes y no generen ningún tipo de error.
Durabilidad: es la propiedad que asegura que una vez realizada la operación, ésta persistirá y no se podrá deshacer aunque falle el sistema.
Cumpliendo estas 4 condiciones o propiedades se considera ACID Compliant.


IMPLICIT TRANSACTIONS: SET IMPLICIT_TRANSACTIONS ON

EXPLICIT TRANSACTIONS: BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION