테이블 명세서 (SQL 2005)

출처 : TAEYO.NET

 

 

DECLARE @KEY VARCHAR
SET @KEY = ''

--DECLARE @KEY2 VARCHAR
--SET @KEY2 = ' '

DECLARE @TABLE_INFO TABLE
 (
    TABLE_NAME VARCHAR(250) , 
    TABLE_COMMENT VARCHAR(1000) , 
    IDX VARCHAR(50) , 
    COLUMN_NAME VARCHAR(250) , 
    COLUMN_COMMENT VARCHAR(1000) , 
    LENGTH VARCHAR(50) , 
    PK VARCHAR(10) , 
    TYPE VARCHAR(50) , 
    TYPE2 VARCHAR(100) , 
    ISNULLABLE VARCHAR(10)
 )   
 
 DECLARE @COLUMN_INFO TABLE
 (
    TABLE_NAME VARCHAR(250) , 
    COLUMN_NAME VARCHAR(250) , 
    COLUMN_COMMENT VARCHAR(1000)  
 )   
 
 DECLARE @VAR_TABLE_COMMENTS TABLE
 (
    OBJNAME  VARCHAR(1000) , 
    COMMENT VARCHAR(1000)  
 )   
 
 -- 코멘트를 담아두기 위한 테이블 변수 생성
 INSERT INTO @VAR_TABLE_COMMENTS SELECT CONVERT(VARCHAR(1000),OBJNAME), CONVERT
(VARCHAR(1000),VALUE)
 FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', NULL, NULL , DEFAULT) 
 WHERE NAME = 'MS_DESCRIPTION'
 
 -- 테이블정보를 담기
 INSERT INTO @TABLE_INFO
    SELECT 
          UPPER(DBO.SYSOBJECTS.NAME) AS TABLE_NAME,   
          ISNULL(TABLE_COMMENTS.COMMENT,'') AS TABLE_COMMENT,
          DBO.SYSCOLUMNS.COLORDER AS IDX,   
          UPPER(DBO.SYSCOLUMNS.NAME) AS COLUMN_NAME,   
          '' AS COLUMN_COMMENT ,
          DBO.SYSCOLUMNS.LENGTH AS LENGTH,  
          --CASE WHEN DBO.SYSCOLUMNS.COLSTAT = 1 THEN 'Y' ELSE 'N' END AS PK,  
          CASE WHEN PK_FK.COLUMN_NAME IS NOT NULL AND SUBSTRING
(PK_FK.CONSTRAINT_NAME,1,2) = 'PK' THEN 'Y' ELSE '' END AS PK,  
          UPPER(DBO.SYSTYPES.NAME) AS TYPE , 
          CASE   WHEN  
                   UPPER(DBO.SYSTYPES.NAME)
= 'VARCHAR' OR  
                   UPPER(DBO.SYSTYPES.NAME)
= 'NVARCHAR' OR  
                   UPPER(DBO.SYSTYPES.NAME) = 'CHAR'
OR  
                   UPPER(DBO.SYSTYPES.NAME)
= 'NCHAR'  
                   THEN    UPPER(DBO.SYSTYPES.NAME)
+ '(' + CONVERT(VARCHAR,DBO.SYSCOLUMNS.PREC) + ')'   
                WHEN 
                   UPPER(DBO.SYSTYPES.NAME)
= 'DECIMAL' OR  
                   UPPER(DBO.SYSTYPES.NAME)
= 'NUMERIC'  
                   THEN    UPPER(DBO.SYSTYPES.NAME)
+ '(' + CONVERT(VARCHAR,DBO.SYSCOLUMNS.PREC) + ', ' + CONVERT
(VARCHAR,DBO.SYSCOLUMNS.SCALE) + ')'   
                ELSE UPPER(DBO.SYSTYPES.NAME)   
          END AS TYPE2 , 
          CASE WHEN DBO.SYSCOLUMNS.ISNULLABLE = 1 THEN 'Y' ELSE '' END AS
ISNULLABLE 
       FROM    
          DBO.SYSCOLUMNS  
             INNER JOIN DBO.SYSOBJECTS  
                ON DBO.SYSCOLUMNS.ID = DBO.SYSOBJECTS.ID  
             INNER JOIN DBO.SYSTYPES  
                ON DBO.SYSCOLUMNS.XTYPE =
DBO.SYSTYPES.XTYPE  
             LEFT OUTER JOIN @VAR_TABLE_COMMENTS AS
TABLE_COMMENTS
                ON DBO.SYSOBJECTS.NAME =
TABLE_COMMENTS.OBJNAME

                 LEFT OUTER JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK_FK
                       ON DBO.SYSOBJECTS.NAME = CONVERT(VARCHAR(1000),PK_FK.TABLE_NAME)
                          AND DBO.SYSCOLUMNS.NAME = CONVERT(VARCHAR
(1000),PK_FK.COLUMN_NAME)
                          AND ( PK_FK.CONSTRAINT_NAME LIKE 'PK%' OR
PK_FK.CONSTRAINT_NAME LIKE 'FK%' )

 

       WHERE       
          DBO.SYSOBJECTS.XTYPE = 'U' 
          AND DBO.SYSTYPES.STATUS <> 1  
          AND  UPPER(DBO.SYSOBJECTS.NAME) LIKE @KEY + '%'
--          AND  UPPER(DBO.SYSOBJECTS.NAME) NOT LIKE '%' + @KEY2 + '%'

       ORDER BY   
          DBO.SYSOBJECTS.NAME,   
          DBO.SYSCOLUMNS.COLORDER  
 
 DECLARE   @TABLE_NAME VARCHAR(250)  
 DECLARE   @TABLE_COMMENT VARCHAR(1000)  
 DECLARE   @IDX VARCHAR(50)  
 DECLARE   @COLUMN_NAME VARCHAR(250)  
 DECLARE   @COLUMN_COMMENT VARCHAR(1000)  
 DECLARE   @LENGTH VARCHAR(50)  
 DECLARE   @PK VARCHAR(10)  
 DECLARE   @TYPE VARCHAR(50)  
 DECLARE   @TYPE2 VARCHAR(100)  
 DECLARE   @ISNULLABLE VARCHAR(10) 
 
 
 DECLARE TABLE_CURSOR 
    CURSOR FOR 
       SELECT TABLE_NAME , COLUMN_NAME , COLUMN_COMMENT FROM @TABLE_INFO
 
 OPEN TABLE_CURSOR
 FETCH NEXT FROM TABLE_CURSOR INTO @TABLE_NAME,@COLUMN_NAME,@COLUMN_COMMENT
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
       UPDATE @TABLE_INFO SET COLUMN_COMMENT = 
          ISNULL(
          (
             SELECT CONVERT(VARCHAR(1000),VALUE)
FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', @TABLE_NAME, 'COLUMN',
@COLUMN_NAME)
          ),'')
       WHERE TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME
    END
    FETCH NEXT FROM TABLE_CURSOR INTO @TABLE_NAME,@COLUMN_NAME,@COLUMN_COMMENT
 END
 
 CLOSE TABLE_CURSOR 
 DEALLOCATE TABLE_CURSOR 
 

-- 테이블 코멘트
 SELECT   
 UPPER( ISNULL(A.NAME,'') ) AS TABLE_NAME 
 , ISNULL(B.COMMENT,'') AS COMMENT 
 FROM   
 DBO.SYSOBJECTS  A 
 LEFT OUTER JOIN @VAR_TABLE_COMMENTS B 
     ON A.NAME = B.OBJNAME 
 WHERE   
 TYPE = 'U'   
 AND NAME LIKE @KEY + '%' 
-- AND NAME NOT LIKE '%' + @KEY2 + '%'
 ORDER BY NAME

-- 컬럼 코멘트들의 정보
SELECT * FROM @TABLE_INFO

 

 

참고로 Table , Column에 코멘트를 삽입,수정하는 문장은
sp_addextendedproperty , sp_updateextendedproperty를 이용하시면 됩니다.