[MSSQL] TABLE(테이블) 정의서 작성 쿼리

DB 테이블 정의서가 필요할 때 사용

 

SELECT  A.TABLE_NAME,
        A.ORDINAL_POSITION,
        CASE WHEN C.TABLE_NAME IS NOT NULL    THEN 'PK' ELSE ''  END PK ,
        A.COLUMN_NAME,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        IS_NULLABLE,
        COLUMN_DEFAULT,
		B.DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS A
	LEFT OUTER JOIN (SELECT a.object_id AS TABLE_ID,  
                        a.name AS TABLE_NAME, b.name AS COLUMN_NAME,  
                        b.column_id AS COLUMN_ID, c.value AS DESCRIPTION 
                  FROM sys.objects a 
                  LEFT JOIN sys.columns b ON a.object_id=b.object_id 
                  LEFT JOIN sys.extended_properties c ON  (a.object_id=c.major_id AND b.column_id=c.minor_id) 
                ) B  
		ON A.TABLE_NAME = B.TABLE_NAME  
		AND A.COLUMN_NAME = B.COLUMN_NAME 
	LEFT OUTER JOIN   INFORMATION_SCHEMA.KEY_COLUMN_USAGE C 
		ON A.TABLE_NAME = C.TABLE_NAME AND A.COLUMN_NAME = C.COLUMN_NAME 
ORDER BY TABLE_NAME, ORDINAL_POSITION

1) 쿼리 결과 화면

 

 

SSMS 에 파일로 한방에 뽑아내주면 좋으련만...