Las siguientes consultas se pueden utilizar para la generación de diccionario de datos en SQL Server.
- Lista de tablas, descripción y cantidad de registros
- Lista de vistas y descripción
- Lista de campos y descripción
- Lista de llaves foráneas
- Lista de vistas, columnas y descripción
- Numero de columnas por tablas
- Lista de indices
1. Lista de tablas, descripción y cantidad de registros
SELECT SCHEMA_NAME(TAB.SCHEMA_ID) AS SCHEMA_NAME,
TAB.NAME AS TABLE_NAME,
TAB.CREATE_DATE AS CREATED,
TAB.MODIFY_DATE AS LAST_MODIFIED,
P.ROWS AS NUM_ROWS,
EP.VALUE AS COMMENTS
FROM SYS.TABLES TAB
INNER JOIN (SELECT DISTINCT P.OBJECT_ID, SUM(P.ROWS) ROWS
FROM SYS.TABLES T
INNER JOIN SYS.PARTITIONS P ON P.OBJECT_ID = T.OBJECT_ID
GROUP BY P.OBJECT_ID, P.INDEX_ID) P
ON P.OBJECT_ID = TAB.OBJECT_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES EP
ON TAB.OBJECT_ID = EP.MAJOR_ID
AND EP.NAME = 'MS_DESCRIPTION'
AND EP.MINOR_ID = 0
AND EP.CLASS_DESC = 'OBJECT_OR_COLUMN'
ORDER BY SCHEMA_NAME,
TABLE_NAME;
2. Lista de vistas y descripción
SELECT SCHEMA_NAME(V.SCHEMA_ID) AS SCHEMA_NAME,
V.NAME AS VIEW_NAME,
V.CREATE_DATE AS CREATED,
V.MODIFY_DATE AS LAST_MODIFIED,
M.DEFINITION,
EP.VALUE AS COMMENTS
FROM SYS.VIEWS V
LEFT JOIN SYS.EXTENDED_PROPERTIES EP
ON V.OBJECT_ID = EP.MAJOR_ID
AND EP.NAME = 'MS_DESCRIPTION'
AND EP.MINOR_ID = 0
AND EP.CLASS_DESC = 'OBJECT_OR_COLUMN'
INNER JOIN SYS.SQL_MODULES M
ON M.OBJECT_ID = V.OBJECT_ID
ORDER BY SCHEMA_NAME,
VIEW_NAME;
3. Lista de campos y descripción
SELECT SCHEMA_NAME(TAB.SCHEMA_ID) AS SCHEMA_NAME,
TAB.NAME AS TABLE_NAME,
COL.NAME AS COLUMN_NAME,
T.NAME AS DATA_TYPE,
T.NAME +
CASE WHEN T.IS_USER_DEFINED = 0 THEN
ISNULL('(' +
CASE WHEN T.NAME IN ('BINARY', 'CHAR', 'NCHAR',
'VARCHAR', 'NVARCHAR', 'VARBINARY') THEN
CASE COL.MAX_LENGTH
WHEN -1 THEN 'MAX'
ELSE
CASE WHEN T.NAME IN ('NCHAR',
'NVARCHAR') THEN
CAST(COL.MAX_LENGTH/2
AS VARCHAR(4))
ELSE CAST(COL.MAX_LENGTH
AS VARCHAR(4))
END
END
WHEN T.NAME IN ('DATETIME2', 'DATETIMEOFFSET',
'TIME') THEN
CAST(COL.SCALE AS VARCHAR(4))
WHEN T.NAME IN ('DECIMAL', 'NUMERIC') THEN
CAST(COL.PRECISION AS VARCHAR(4)) + ', ' +
CAST(COL.SCALE AS VARCHAR(4))
END + ')', '')
ELSE ':' +
(SELECT C_T.NAME +
ISNULL('(' +
CASE WHEN C_T.NAME IN ('BINARY', 'CHAR',
'NCHAR', 'VARCHAR', 'NVARCHAR',
'VARBINARY') THEN
CASE C.MAX_LENGTH
WHEN -1 THEN 'MAX'
ELSE
CASE WHEN T.NAME IN
('NCHAR',
'NVARCHAR') THEN
CAST(C.MAX_LENGTH/2
AS VARCHAR(4))
ELSE CAST(C.MAX_LENGTH
AS VARCHAR(4))
END
END
WHEN C_T.NAME IN ('DATETIME2',
'DATETIMEOFFSET', 'TIME') THEN
CAST(C.SCALE AS VARCHAR(4))
WHEN C_T.NAME IN ('DECIMAL', 'NUMERIC') THEN
CAST(C.PRECISION AS VARCHAR(4)) + ', '
+ CAST(C.SCALE AS VARCHAR(4))
END + ')', '')
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TYPES AS C_T
ON C.SYSTEM_TYPE_ID = C_T.USER_TYPE_ID
WHERE C.OBJECT_ID = COL.OBJECT_ID
AND C.COLUMN_ID = COL.COLUMN_ID
AND C.USER_TYPE_ID = COL.USER_TYPE_ID
)
END AS DATA_TYPE_EXT,
CASE WHEN COL.IS_NULLABLE = 0 THEN 'N'
ELSE 'Y' END AS NULLABLE,
CASE WHEN DEF.DEFINITION IS NOT NULL THEN DEF.DEFINITION
ELSE '' END AS DEFAULT_VALUE,
CASE WHEN PK.COLUMN_ID IS NOT NULL THEN 'PK'
ELSE '' END AS PRIMARY_KEY,
CASE WHEN FK.PARENT_COLUMN_ID IS NOT NULL THEN 'FK'
ELSE '' END AS FOREIGN_KEY,
CASE WHEN UK.COLUMN_ID IS NOT NULL THEN 'UK'
ELSE '' END AS UNIQUE_KEY,
CASE WHEN CH.CHECK_CONST IS NOT NULL THEN CH.CHECK_CONST
ELSE '' END AS CHECK_CONTRAINT,
CC.DEFINITION AS COMPUTED_COLUMN_DEFINITION,
EP.VALUE AS COMMENTS
FROM SYS.TABLES AS TAB
LEFT JOIN SYS.COLUMNS AS COL
ON TAB.OBJECT_ID = COL.OBJECT_ID
LEFT JOIN SYS.TYPES AS T
ON COL.USER_TYPE_ID = T.USER_TYPE_ID
LEFT JOIN SYS.DEFAULT_CONSTRAINTS AS DEF
ON DEF.OBJECT_ID = COL.DEFAULT_OBJECT_ID
LEFT JOIN (
SELECT INDEX_COLUMNS.OBJECT_ID,
INDEX_COLUMNS.COLUMN_ID
FROM SYS.INDEX_COLUMNS
INNER JOIN SYS.INDEXES
ON INDEX_COLUMNS.OBJECT_ID = INDEXES.OBJECT_ID
AND INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID
WHERE INDEXES.IS_PRIMARY_KEY = 1
) AS PK
ON COL.OBJECT_ID = PK.OBJECT_ID
AND COL.COLUMN_ID = PK.COLUMN_ID
LEFT JOIN (
SELECT FC.PARENT_COLUMN_ID,
FC.PARENT_OBJECT_ID
FROM SYS.FOREIGN_KEYS AS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC
ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
GROUP BY FC.PARENT_COLUMN_ID, FC.PARENT_OBJECT_ID
) AS FK
ON FK.PARENT_OBJECT_ID = COL.OBJECT_ID
AND FK.PARENT_COLUMN_ID = COL.COLUMN_ID
LEFT JOIN (
SELECT C.PARENT_COLUMN_ID,
C.PARENT_OBJECT_ID,
'CHECK' CHECK_CONST
FROM SYS.CHECK_CONSTRAINTS AS C
GROUP BY C.PARENT_COLUMN_ID,
C.PARENT_OBJECT_ID
) AS CH
ON COL.COLUMN_ID = CH.PARENT_COLUMN_ID
AND COL.OBJECT_ID = CH.PARENT_OBJECT_ID
LEFT JOIN (
SELECT INDEX_COLUMNS.OBJECT_ID,
INDEX_COLUMNS.COLUMN_ID
FROM SYS.INDEX_COLUMNS
INNER JOIN SYS.INDEXES
ON INDEXES.INDEX_ID = INDEX_COLUMNS.INDEX_ID
AND INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID
WHERE INDEXES.IS_UNIQUE_CONSTRAINT = 1
GROUP BY INDEX_COLUMNS.OBJECT_ID,
INDEX_COLUMNS.COLUMN_ID
) AS UK
ON COL.COLUMN_ID = UK.COLUMN_ID
AND COL.OBJECT_ID = UK.OBJECT_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES AS EP
ON TAB.OBJECT_ID = EP.MAJOR_ID
AND COL.COLUMN_ID = EP.MINOR_ID
AND EP.NAME = 'MS_DESCRIPTION'
AND EP.CLASS_DESC = 'OBJECT_OR_COLUMN'
LEFT JOIN SYS.COMPUTED_COLUMNS AS CC
ON TAB.OBJECT_ID = CC.OBJECT_ID
AND COL.COLUMN_ID = CC.COLUMN_ID
ORDER BY SCHEMA_NAME,
TABLE_NAME,
COLUMN_NAME;
4. Lista de llaves foráneas
SELECT SCHEMA_NAME(TAB.SCHEMA_ID) AS TABLE_SCHEMA_NAME,
TAB.NAME AS TABLE_NAME,
COL.NAME AS COLUMN_NAME,
FK.NAME AS CONSTRAINT_NAME,
SCHEMA_NAME(TAB_PRIM.SCHEMA_ID) AS PRIMARY_TABLE_SCHEMA_NAME,
TAB_PRIM.NAME AS PRIMARY_TABLE_NAME,
COL_PRIM.NAME AS PRIMARY_TABLE_COLUMN,
SCHEMA_NAME(TAB.SCHEMA_ID) + '.' + TAB.NAME + '.' +
COL.NAME + ' = ' + SCHEMA_NAME(TAB_PRIM.SCHEMA_ID) + '.' +
TAB_PRIM.NAME + '.' + COL_PRIM.NAME AS JOIN_CONDITION,
CASE
WHEN COUNT(*) OVER (PARTITION BY FK.NAME) = 1 THEN 'Y'
ELSE 'N'
END AS COMPLEX_FK,
FKC.CONSTRAINT_COLUMN_ID AS FK_PART
FROM SYS.TABLES AS TAB
INNER JOIN SYS.FOREIGN_KEYS AS FK
ON TAB.OBJECT_ID = FK.PARENT_OBJECT_ID
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FKC
ON FK.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID
INNER JOIN SYS.COLUMNS AS COL
ON FKC.PARENT_OBJECT_ID = COL.OBJECT_ID
AND FKC.PARENT_COLUMN_ID = COL.COLUMN_ID
INNER JOIN SYS.COLUMNS AS COL_PRIM
ON FKC.REFERENCED_OBJECT_ID = COL_PRIM.OBJECT_ID
AND FKC.REFERENCED_COLUMN_ID = COL_PRIM.COLUMN_ID
INNER JOIN SYS.TABLES AS TAB_PRIM
ON FK.REFERENCED_OBJECT_ID = TAB_PRIM.OBJECT_ID
ORDER BY TABLE_SCHEMA_NAME,
TABLE_NAME,
PRIMARY_TABLE_NAME,
FK_PART;
5. Lista de vistas, columnas y descripción
SELECT SCHEMA_NAME(V.SCHEMA_ID) AS SCHEMA_NAME,
V.NAME AS VIEW_NAME,
COL.NAME AS COLUMN_NAME,
T.NAME AS DATA_TYPE,
T.NAME +
CASE WHEN T.IS_USER_DEFINED = 0 THEN
ISNULL('(' +
CASE WHEN T.NAME IN ('BINARY', 'CHAR', 'NCHAR',
'VARCHAR', 'NVARCHAR', 'VARBINARY') THEN
CASE COL.MAX_LENGTH
WHEN -1 THEN 'MAX'
ELSE
CASE
WHEN T.NAME IN ('NCHAR',
'NVARCHAR') THEN
CAST(COL.MAX_LENGTH/2
AS VARCHAR(4))
ELSE CAST(COL.MAX_LENGTH
AS VARCHAR(4))
END
END
WHEN T.NAME IN ('DATETIME2',
'DATETIMEOFFSET', 'TIME') THEN
CAST(COL.SCALE AS VARCHAR(4))
WHEN T.NAME IN ('DECIMAL', 'NUMERIC') THEN
CAST(COL.PRECISION AS VARCHAR(4)) + ', ' +
CAST(COL.SCALE AS VARCHAR(4))
END + ')', '')
ELSE ':' +
(SELECT C_T.NAME +
ISNULL('(' +
CASE WHEN C_T.NAME IN ('BINARY', 'CHAR',
'NCHAR', 'VARCHAR', 'NVARCHAR',
'VARBINARY') THEN
CASE C.MAX_LENGTH
WHEN -1 THEN 'MAX'
ELSE CASE WHEN T.NAME IN
('NCHAR',
'NVARCHAR')
THEN CAST(C.MAX_LENGTH/2
AS VARCHAR(4))
ELSE CAST(C.MAX_LENGTH
AS VARCHAR(4))
END
END
WHEN C_T.NAME IN ('DATETIME2',
'DATETIMEOFFSET', 'TIME') THEN
CAST(C.SCALE AS VARCHAR(4))
WHEN C_T.NAME IN ('DECIMAL', 'NUMERIC') THEN
CAST(C.PRECISION AS VARCHAR(4)) +
', ' + CAST(C.SCALE AS VARCHAR(4))
END + ')', '')
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TYPES AS C_T
ON C.SYSTEM_TYPE_ID = C_T.USER_TYPE_ID
WHERE C.OBJECT_ID = COL.OBJECT_ID
AND C.COLUMN_ID = COL.COLUMN_ID
AND C.USER_TYPE_ID = COL.USER_TYPE_ID
)
END AS DATA_TYPE_EXT,
CASE WHEN COL.IS_NULLABLE = 0 THEN 'N' ELSE 'Y' END AS NULLABLE,
EP.VALUE AS COMMENTS
FROM SYS.VIEWS AS V
JOIN SYS.COLUMNS AS COL
ON V.OBJECT_ID = COL.OBJECT_ID
LEFT JOIN SYS.TYPES AS T
ON COL.USER_TYPE_ID = T.USER_TYPE_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES AS EP
ON V.OBJECT_ID = EP.MAJOR_ID
AND COL.COLUMN_ID = EP.MINOR_ID
AND EP.NAME = 'MS_DESCRIPTION'
AND EP.CLASS_DESC = 'OBJECT_OR_COLUMN'
ORDER BY SCHEMA_NAME,
VIEW_NAME,
COLUMN_NAME;
6. Número de columnas por tablas
SELECT SCHEMA_NAME(TAB.SCHEMA_ID) AS SCHEMA_NAME,
TAB.NAME AS TABLE_NAME,
COUNT(*) AS COLUMNS
FROM SYS.TABLES AS TAB
INNER JOIN SYS.COLUMNS AS COL
ON TAB.OBJECT_ID = COL.OBJECT_ID
GROUP BY SCHEMA_NAME(TAB.SCHEMA_ID),
TAB.NAME
ORDER BY COUNT(*) DESC;
7. Lista de indices
SELECT
TABLENAME = T.NAME,
INDEXNAME = IND.NAME,
INDEXID = IND.INDEX_ID,
COLUMNID = IC.INDEX_COLUMN_ID,
COLUMNNAME = COL.NAME,
IND.*,
IC.*,
COL.*
FROM
SYS.INDEXES IND
INNER JOIN
SYS.INDEX_COLUMNS IC ON IND.OBJECT_ID = IC.OBJECT_ID AND IND.INDEX_ID = IC.INDEX_ID
INNER JOIN
SYS.COLUMNS COL ON IC.OBJECT_ID = COL.OBJECT_ID AND IC.COLUMN_ID = COL.COLUMN_ID
INNER JOIN
SYS.TABLES T ON IND.OBJECT_ID = T.OBJECT_ID
WHERE
IND.IS_PRIMARY_KEY = 0
AND IND.IS_UNIQUE = 0
AND IND.IS_UNIQUE_CONSTRAINT = 0
AND T.IS_MS_SHIPPED = 0
ORDER BY
T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID;
Esto da error: “WHEN COUNT(*) OVER (PARTITION BY FK.NAME) > 1 THEN ‘Y’
ELSE ‘N'” ese ;