[MSSQL] 주석 없는 개체 생성 스크립트

• 테이블

SELECT 'EXEC   sp_addextendedproperty ''MS_Description'', N''?'', ''user'', dbo, ''table'', ' + A.name
,	A.NAME	AS TABLE_NAME         -- Table Name
,	C.VALUE	AS TABLE_DESCRIPTION  -- Table Description
FROM SYSOBJECTS A WITH (NOLOCK)
INNER JOIN SYSUSERS B WITH (NOLOCK)        
ON	A.UID = B.UID
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES C WITH (NOLOCK)
ON C.MAJOR_ID = A.ID
AND C.MINOR_ID = 0
AND C.NAME = 'MS_Description'
WHERE 1=1
AND A.TYPE = 'U'
AND C.VALUE IS NULL
--AND A.NAME NOT LIKE N'%TEST%' -- 제거해야할 테이블이 있다면 추가
ORDER BY A.[name]--, D.COLORDER

 

• 컬

SELECT 	'EXEC   sp_addextendedproperty ''MS_Description'', N''?'', ''user'', dbo, ''table'', ' + A.name +' , ''column'',' + D.NAME	
,D.COLORDER                AS COLUMN_IDX            -- Column Index
, A.NAME                    AS TABLE_NAME            -- Table Name
, D.NAME                    AS COLUMN_NAME            -- Column Name
, E.VALUE                    AS COLUMN_DESCRIPTION    -- Column Description
--, F.DATA_TYPE                AS TYPE                    -- Column Type
--, F.CHARACTER_OCTET_LENGTH    AS LENGTH                -- Column Length
--, F.IS_NULLABLE            AS IS_NULLABLE            -- Column Nullable
--, F.COLLATION_NAME            AS COLLATION_NAME        -- Column Collaction Name
FROM SYSOBJECTS A WITH (NOLOCK)
INNER JOIN SYSUSERS B WITH (NOLOCK)        ON A.UID = B.UID
INNER JOIN SYSCOLUMNS D WITH (NOLOCK)        ON D.ID = A.ID
INNER JOIN INFORMATION_SCHEMA.COLUMNS F WITH (NOLOCK)
ON A.NAME = F.TABLE_NAME
AND D.NAME = F.COLUMN_NAME
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E WITH (NOLOCK)
ON E.MAJOR_ID = D.ID
AND E.MINOR_ID = D.COLID
AND E.NAME = 'MS_Description'  
WHERE 1=1
AND A.TYPE = 'U'
--AND A.NAME NOT LIKE N'%TEST%' -- 제거해야할 테이블이 있다면 추가
AND E.VALUE IS NULL
ORDER BY A.[name], D.COLORDER