Incendio Forestal
sexy-sql
sexy-sql

En esta gélida tarde de día sábado, queremos profundizar un poco la generación del diccionario de datos desde SQL Server que publicamos en este enlace, dado que hemos recibido algunas dudas sobre como podemos incorporar las llaves foráneas.
Para esto, debemos tener en consideración que las llaves foráneas son almacenadas en dos tablas: sys.foreign_keys y sys.foreign_key_columns

 

foreign_keys y foreign_key_columns

La tabla sys.foreign_keys contiene la información básica de nuestra llave foránea, de manera tal que nos permite identificar el nombre, fecha de creación, fecha de modificación y las tablas involucradas.
La tabla sys.foreign_key_columns contiene el detalle de la llave fóranea. Contiene el identificador único de ambas tablas y de ambas columnas. Adicionalmente, contiene el identificador único de la llave fóranea, de manera de poder asociar esta tabla con la tabla foreign_kays

 

Resultado

Dicho lo anterior, lo único que resta para poder agregar la información de las llaves foráneas a nuestro diccionario de datos, es enlazar la información de estas tablas con la tabla que recibe la llave foránea. De esta manera, podemos tener el siguiente script:

SELECT	T.name AS tabla, 
		C.name AS columna, 
		TYP.name AS tipo, 
		CASE WHEN C.is_nullable=0 THEN 'Falso' ELSE 'Verdadero' END AS EsNulo,
		CASE WHEN C.is_identity=0 THEN 'Falso' ELSE 'Verdadero' END AS EsAutonumerico,				
		CASE WHEN IND.is_unique=0 THEN 'Falso' ELSE 'Verdader' END AS EsUnico,
		CASE WHEN IND.is_primary_key=0 THEN 'Falso' ELSE 'Verdadero' END AS EsPrimaryKey,
		CASE WHEN F.parent_object_id is not null then F.Tabla + '.' + F.Columna else '' END AS FK,
		COMTS.VALUE AS descripcion		
		,C.*
FROM sys.TABLES T
INNER JOIN sys.COLUMNS C ON T.object_id=C.object_id
INNER JOIN sys.systypes TYP ON TYP.xtype=C.system_type_id
LEFT JOIN sys.extended_properties COMTS ON COMTS.major_id=C.object_id AND COMTS.minor_id=C.column_id

-- Se obtienen los datos de la llaves foraneas en caso de existir
Left join (
	select TFK.name as Tabla, CFK.name as Columna, FK.parent_object_id, FKD.parent_column_id
	from sys.foreign_keys FK
	inner join sys.foreign_key_columns FKD on FKD.constraint_object_id=FK.object_id
	inner join sys.columns CFK on CFK.column_id=FKD.referenced_column_id and CFK.object_id=FKD.referenced_object_id
	inner join sys.tables TFK on TFK.object_id=CFK.object_id
) F on F.parent_object_id=T.object_id and F.parent_column_id=C.column_id

-- Considerando que una columna puede estar asociado a más de un indice,
-- nos aseguramos de traer un solo registro por columna para no duplicar
-- los registros.
LEFT JOIN (
	SELECT	I.object_id, 
			IC.column_id,
			MIN(I.index_id) AS index_id, 
			MAX(CAST(I.is_unique AS INT)) AS is_unique, 
			MAX(CAST(I.is_primary_key AS INT)) AS is_primary_key
	FROM sys.indexes I 
	INNER JOIN sys.index_columns IC ON I.object_id=IC.object_id AND I.index_id=IC.index_id
	GROUP BY I.object_id, IC.column_id
) IND ON IND.object_id=C.object_id AND IND.column_id=C.column_id
WHERE T.name<>'sysdiagrams' AND TYP.name<>'sysname'
ORDER BY T.name, C.column_id
diccionario-de-datos-resultado-grilla
diccionario-de-datos-resultado-grilla

No hay comentarios