ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • 테이블 명세서 만들기..므흣~
    DB/Ms.SQL 2012. 3. 6. 11:00
    SELECT A.COLUMN_NAME
    , CASE WHEN B.DESCRIPTION IS NULL THEN '' ELSE B.DESCRIPTION END AS DESCRIPTION
    ,CASE WHEN A.IS_NULLABLE = 'YES' THEN '' ELSE 'N' END AS IS_NULLABLE 
    , A.DATA_TYPE
    , CASE WHEN A.DATA_TYPE IN ('nvarchar','char','nchar','varchar') THEN CONVERT(NVARCHAR,CHARACTER_MAXIMUM_LENGTH) 
       WHEN A.DATA_TYPE IN ('numeric','decimal') THEN CONVERT(NVARCHAR,NUMERIC_PRECISION) + ',' + CONVERT(NVARCHAR,NUMERIC_SCALE)
    ELSE '' END AS DATA_TYPE
    , CASE WHEN C.TABLE_NAME IS NOT NULL THEN 'PK' ELSE '' END PK
     
    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
    WHERE A.TABLE_NAME ='테이블 명'
    

    'DB > Ms.SQL' 카테고리의 다른 글

    특수한 데이터 형식  (0) 2012.02.05
    [MSSQL] 소유자변경  (0) 2011.06.23
    데이터 생성기법  (0) 2009.09.24
    BULK INSERT  (0) 2008.07.22
    ..조인과 하위쿼리..  (0) 2007.08.21
Designed by Tistory.