Categoría: Sin categorizar

por Fran Lens 2016-09-05

Generar Script de Creación de Primary Key en tablas Heap

El otro día cuando realizaba un Health Check en un servidor SQL Server de un cliente, ejecuté el típico script de tablas sin Primary Key, devolvió más de 200 tablas, el resultado era de esperar ya que es una base de datos que fue creciendo sin control ni mantenimiento.

Nunca está de mas recordar una frase que todo DBA tendría que tener escrita a fuego “Todas las tablas tienen que tener una Primary Key“(y un índice clustered), si no sabéis porqué, este enlace os ayudará a entenderlo mejor.

Teniendo el punto anterior claro, me puse a buscar un script que me auto generara la Primary Key y su índice clustered asociado, después de un rato buscando en Google no encontré nada, por lo que finalmente me tocó crear el script.

Aquí os lo dejo los dos scripts que realicé para que no os pase como a mí:

En mi caso me encontré con que algunas tablas ya tenían creado un campo Identity, por lo que simplemente había que generar el código de creación de la PK utilizando el campo Identity existente.

-- Generar Script de Creación de Primary Key en tablas con Identity y sin PK 
SELECT S.NAME AS Schemaname
	,O.NAME AS Tablename
	,C.NAME AS IdentityColumn
	,'ALTER TABLE ' + QUOTENAME(S.NAME) + '.' + QUOTENAME(O.NAME) + ' ADD CONSTRAINT [PK_' + O.NAME + '] PRIMARY KEY CLUSTERED ( ' + QUOTENAME(c.NAME) + ' ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]' AS CreatePKScript
FROM sys.columns C
INNER JOIN sys.objects O ON O.object_id = C.object_id
INNER JOIN sys.tables T ON T.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE COLUMNPROPERTY(object_id(O.NAME), C.NAME, 'IsIdentity') = 1
	AND OBJECTPROPERTY(object_id(O.NAME), 'TableHasPrimaryKey') = 0
	AND O.type = 'u'
ORDER BY O.NAME

El resto de tablas no tenían ni campo Identity ni PK, por lo que había que generar primero el código de creación del campo Identity y el código de creación de la PK utilizando el nuevo campo.

-- Generar Script de Creación de campo Identity y Primary Key en tablas sin Identity y sin PK 
SELECT DISTINCT S.NAME AS SchemaName
	,O.NAME AS TableName
	,'ALTER TABLE ' + QUOTENAME(S.NAME) + '.' + QUOTENAME(O.NAME) + ' ADD [' + O.NAME + '_PK] INT IDENTITY(1,1)' AS CreateIdentityScript
	,'ALTER TABLE ' + QUOTENAME(S.NAME) + '.' + QUOTENAME(O.NAME) + ' ADD CONSTRAINT [PK_' + O.NAME + '] PRIMARY KEY CLUSTERED ( [' + O.NAME + '_PK] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]' AS CreatePKScript
FROM sys.columns C
INNER JOIN sys.objects O ON O.object_id = C.object_id
INNER JOIN sys.tables T ON T.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE OBJECTPROPERTY(object_id(O.NAME), 'TableHasPrimaryKey') = 0
	AND O.type = 'u'
	AND O.NAME NOT IN (
		SELECT O.NAME
		FROM sys.columns C
		INNER JOIN sys.objects O ON O.object_id = C.object_id
		WHERE COLUMNPROPERTY(object_id(O.NAME), C.NAME, 'IsIdentity') = 1
			AND O.type = 'u'
		)
ORDER BY O.NAME