Diccionario de datos SQL Server

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;

DiccionarioSQL1

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;

DiccionarioSQL2

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; 

DiccionarioSQL3

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; 

DiccionarioSQL4

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; 

DiccionarioSQL5

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;

DiccionarioSQL6

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;

DiccionarioSQL7


Share on FacebookTweet about this on TwitterShare on Google+

Agregue un comentario

Su dirección de correo no se hará público. Los campos requeridos están marcados *