dataDictionarySQLFull

dataDictionarySQLFull.sql

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