Ver Todas las Llaves Primarias de Una Base de Datos

Ejemplo que permite ver todas las llaves primarias de las tablas de una base de datos.

---Llaves primarias de una tabla

SELECT i.name AS NombreIndice,

OBJECT_NAME(ic.OBJECT_ID) AS NombreTabla,

COL_NAME(ic.OBJECT_ID,ic.column_id) AS NombreColumna

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID

AND i.index_id = ic.index_id and i.is_primary_key = 1 

Visualizar el código fuente de todos los triggers de una base de datos

Como visualizar el código fuente de todos los triggers de una base de datos.

SELECT  m.definition + char(13) +' Go '

FROM sys.sql_modules as M inner join sys.objects as O

on M.object_id=o.object_id

where O.type='Tr'

Limpiar el log de Transacciones SQL Server

Script de Ejemplo que permite limpiar y reducir el log de transacciones de una base de datos, no es posible limpiar el log sin realizar primero un backup del log, realizaremos nuestro ejemplo con una base de datos a la que llamaremos PrimaveraNew

--Para Limpiar el Log de Transacciones es necesario realizar un Backup del Log

Backup log PrimaveraNeW

to disk  ='C:\test\BackupLog.bak'

--Una vez hecho el backup consultamos el nombre lógico de los archivos del log

sp_helpdb PrimaveraNeW

 Resultado:

-- Antes de truncar el log cambiamos el modelo de recuperación a  SIMPLE.

ALTER DATABASE PrimaveraNeW

SET RECOVERY SIMPLE;

GO

--Reducimos el log de transacciones a  1 MB.

DBCC SHRINKFILE (PrimaveraNeW_Log, 1);

GO

-- Cambiamos nuevamente el modelo de recuperación a Completo.

ALTER DATABASE PrimaveraNeW

SET RECOVERY FULL;

GO

Reconstruir Todos los Indices de una base de datos.

El proceso de volver a crear un índice quita y vuelve a crear el índice. Quita la fragmentación, utiliza espacio en disco al compactar las páginas según el valor de factor de relleno especificado o existente y vuelve a ordenar las filas del índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción.

En este ejemplo utilizamos un cursor para obtener los nombres de las tablas de la base de datos, para luego con esto,  hacer en un ciclo WHILE que regenere todos los índices de cada tabla.

--Regeneracion de Indices, solo situarse en la BD necesaria

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'BASE TABLE' and table_schema='dbo'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC ('ALTER INDEX ALL ON [' + @TableName +

 '] REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON)')

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

Error: "Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'... Microsoft SQL Server, Error 2601"

La documentación de Microsoft dice que para resolver este error es necesario actualizar el SQL con el Service Pack 3 y el  Cumulative Update 2, sin embargo luego de hacer esto en mi base de datos seguía marcando este error al querer hacer un backup o una reducción de tamaño de archivos.

Investigando un poco más, detecte que el problema es ocasionado por Change Tracking o Seguimiento de Cambios, por lo que una vez deshabilitada esta opción tanto en las tablas como en la base de datos ya fue posible realizar backups y reducción de la base de datos.

El Change Tracking o seguimiento de Cambios  es una solución ligera que proporciona un mecanismo de seguimiento de cambios eficaz para las aplicaciones, una vez configurado el seguimiento de cambios para una tabla, cualquier instrucción DML que afecte a las filas en la tabla, registra  la información de seguimiento de cambios pertinente para cada fila modificada.

--Resolver error provocado por Change Tracking o Seguimiento de Cambios

--Desactivar tabla por tabla el Change Tracking

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT b.name

FROM sys.change_tracking_tables a

INNER JOIN sysobjects b

ON b.id=a.object_id

ORDER BY b.name

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec ('ALTER TABLE ['+@TableName+'] DISABLE  CHANGE_TRACKING')

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

--Desactivar el seguimiento de cambios en la base de datos

ALTER DATABASE [Nombre Base de Datos] SET CHANGE_TRACKING = OFF

Luego procedemos a realizar el backup o la instrucción ShrinkDB y al terminar volvemos a activar el seguimiento de cambios.

--Activar el seguimiento de cambios en la base de datos

ALTER DATABASE [Nombre Base de Datos] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 1 DAYS, AUTO_CLEANUP = ON)

Scrip para comparar un Trigger Vrs Un Constraint SQL Server

En el siguiente ejercicio compararemos como funciona una restricción check contra un trigger de tipo after  los dos cumpliendo la misma función, validando que no se permita ingresar menores de 18 años.

Tengamos en cuenta que el trigger es reactivo es decir se ejecuta después que el dato es insertado, eliminado o modificado segun sea el tipo de trigger, mientras que la restricción (constraint) es proactivo es ejecutado antes de insertar, modificar o eliminar el dato.

--Agregar campo a la tabla alter table estudiantes add fechanacimiento date --Poner una restriccion que solo permita mayores de edad alter table estudiantes add constraint Ck_fecha check (Datediff(Year,fechanacimiento,getdate()) >=18) select * from estudiantes --Probar la restruccion con una diferentes fechas UPDATE ESTUDIANTES SET fechanacimiento='01-01-1975' WHERE ID_Estudiante=1 go UPDATE ESTUDIANTES SET fechanacimiento='01-01-2014' WHERE ID_Estudiante=1 go --Borrar la restriccion para hacer lo mismo pero con un trigger alter table estudiantes drop constraint Ck_fecha go /*Crear el trigger, como es posible que se ingresen varias filas al mismo tiempo creamos un cursor y revisaremos dato por dato insertado y de no cumplir lo eliminaremos*/ CREATE TRIGGER COMPROBAR_FECHA ON ESTUDIANTES FOR INSERT AS BEGIN DECLARE @ID_Estudiante varchar(20) DECLARE @FechaNac varchar(255) DECLARE TableCursor CURSOR FOR SELECT ID_Estudiante, FechaNacimiento FROM ESTUDIANTES OPEN TableCursor FETCH NEXT FROM TableCursor INTO @ID_Estudiante, @FechaNac WHILE @@FETCH_STATUS = 0 BEGIN if Datediff(Year,@FechaNac,getdate()) <=18      Delete Estudiantes where id_estudiante=@ID_Estudiante FETCH NEXT FROM TableCursor INTO @ID_Estudiante, @FechaNac END CLOSE TableCursor DEALLOCATE TableCursor END --Probar el trigger insertando varios datos al mismo tiempo Insert into estudiantes ( nombre, fechanacimiento) values ('Juan Perez','01-01-1970'), ('Luis Perez','01-01-2014'), ('Ana Perez','01-01-1970')