MSSQL 테이블/컬럼 스키마 조회 및 참조관계 확인

테이블의 주석 및 컬럼의 정보를 조회하는 쿼리입니다.

 

 
SELECT A.TABLE_NAME, 
       B.VALUE AS TABLE_COMMENT,
       A.DATA_TYPE,
       ISNULL(
           CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), 
           CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + 
           CAST(A.NUMERIC_SCALE AS VARCHAR)
       ) AS COLUMN_LENGTH,
       A.COLUMN_DEFAULT, 
       A.IS_NULLABLE,
       C.value AS COLUMN_COMMENT,
       D.CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) AS A
  LEFT JOIN SYS.extended_properties (NOLOCK) AS B    -- 테이블의 COMMENT 가져오기 
                                             ON B.major_id = OBJECT_ID(A.TABLE_NAME)
                                            AND B.minor_id = 0
  LEFT JOIN SYS.extended_properties (NOLOCK) AS C    -- 컬럼의 COMMENT 가져오기 
                                             ON C.major_id = OBJECT_ID(A.TABLE_NAME)
                                            AND C.minor_id = A.ORDINAL_POSITION
  LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE (NOLOCK) AS D
                                                         ON D.TABLE_NAME = A.TABLE_NAME
                                                        AND D.COLUMN_NAME = A.COLUMN_NAME
 WHERE 1=1
   AND A.TABLE_NAME LIKE '%' + 'TABLE_NAME' + '%'
   AND A.COLUMN_NAME LIKE '%' + '' + '%'
 ORDER BY A.TABLE_NAME ASC, 
          LEFT(D.CONSTRAINT_NAME, 2) DESC, 
          A.ORDINAL_POSITION ASC
;
 
 

 

 

참조 관계를 조회하는 쿼리입니다. 

 

 
SELECT A.NAME AS KEY_NAME, 
       OBJECT_NAME(A.PARENT_OBJECT_ID) AS TABLE_NAME, 
       COL_NAME(B.PARENT_OBJECT_ID, B.PARENT_COLUMN_ID) AS CONSTRAINT_COLUMN_NAME, 
       OBJECT_NAME(A.REFERENCED_OBJECT_ID) AS REFERENCED_OBJECT,
       COL_NAME(B.REFERENCED_OBJECT_ID, B.REFERENCED_COLUMN_ID) AS REFERENCED_COLUMN,
       A.is_disabled,
       A.delete_referential_action,
       A.update_referential_action
  FROM sys.foreign_keys AS A
 INNER JOIN SYS.foreign_key_columns AS B
                                    ON B.constraint_object_id = A.object_id
 WHERE 1=1
   AND A.name = 'FK_NAME'

 

 

 
 
-- 테이블 정보 조회 
 
DECLARE @FIND_TYPE INT;
DECLARE @FIND_WORD NVARCHAR(100);
 
-- 1: TABLE(=) / 2: TABLE(LIKE) / 3: COLUMN(=) / 4: COLUMN(LIKE)
SET @FIND_TYPE = 1;
SET @FIND_WORD = 'LOT';
 
 
SELECT A.TABLE_NAME, 
       B.VALUE AS TABLE_COMMENT,
       A.COLUMN_NAME,
       C.value AS COLUMN_COMMENT,(SELECT value FROM sys.fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', A.TABLE_NAME, 'column', A.COLUMN_NAME)) AS COLUMN_COMMENT,
       A.DATA_TYPE,
       ISNULL(
           CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), 
           CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + 
           CAST(A.NUMERIC_SCALE AS VARCHAR)
       ) AS COLUMN_LENGTH,
       A.IS_NULLABLE + (CASE WHEN A.COLUMN_DEFAULT IS NULL THEN '' ELSE ' [' + A.COLUMN_DEFAULT + ']' END) NULLABLE_DEFAULT,
       D.CONSTRAINT_NAME, 
       REFERENCE_TABLE + '(' + E.REFERENCE_COLUMN + ')' AS REFERENCES_COLUMNS
  FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) AS A
  LEFT JOIN SYS.extended_properties (NOLOCK) AS B    -- 테이블의 COMMENT 가져오기 
                                             ON B.major_id = OBJECT_ID(A.TABLE_NAME)
                                            AND B.minor_id = 0
  LEFT JOIN SYS.extended_properties (NOLOCK) AS C    -- 컬럼의 COMMENT 가져오기 
                                             ON C.major_id = OBJECT_ID(A.TABLE_NAME)
                                            AND C.minor_id = A.ORDINAL_POSITION
  LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE (NOLOCK) AS D
                                                         ON D.TABLE_NAME = A.TABLE_NAME
                                                        AND D.COLUMN_NAME = A.COLUMN_NAME
 OUTER APPLY (
              SELECT OBJECT_NAME(A.REFERENCED_OBJECT_ID) AS REFERENCE_TABLE, 
                     STUFF(
                           (
                            SELECT ',', COL_NAME(B.REFERENCED_OBJECT_ID, B.REFERENCED_COLUMN_ID)
                              FROM SYS.foreign_key_columns AS B
                             WHERE 1=1
                               AND B.constraint_object_id = A.object_id
                               FOR XML PATH('') 
                           ),
                     1,1,'') AS REFERENCE_COLUMN
                FROM sys.foreign_keys AS A
               WHERE 1=1
                 AND A.name = D.CONSTRAINT_NAME
             ) AS E
 WHERE 1=1
   AND ((1=1 AND 1!=@FIND_TYPE) OR (1 = @FIND_TYPE AND A.TABLE_NAME = @FIND_WORD))
   AND ((1=1 AND 2!=@FIND_TYPE) OR (2 = @FIND_TYPE AND A.TABLE_NAME LIKE '%' + @FIND_WORD + '%'))
   AND ((1=1 AND 3!=@FIND_TYPE) OR (3 = @FIND_TYPE AND A.COLUMN_NAME = @FIND_WORD))
   AND ((1=1 AND 4!=@FIND_TYPE) OR (4 = @FIND_TYPE AND A.COLUMN_NAME LIKE '%' + @FIND_WORD + '%'))
 ORDER BY A.TABLE_NAME ASC, 
          A.ORDINAL_POSITION ASC
;