--테이블 및 컬럼에 설명을 넣어보자
--tbl_01 테이블 설명 추가
exec sp_addextendedproperty N'MS_Description', N'01테이블', N'user', N'dbo', N'table', N'tbl_01'
GO
--tbl_01 테이블의 f_col1컬럼 설명 추가
exec sp_addextendedproperty N'MS_Description', N'첫컬럼', N'user', N'dbo', N'table', N'tbl_01', N'column', N'f_col1'
--설명 업데이트시 사용
exec sp_updateextendedproperty
--테이블 정보 확인
--value 컬럼값이 위에서 입력한 설명임.
Select X1.TABLE_QUALIFIER,X1.TABLE_OWNER, X1.TABLE_NAME, X1.COLUMN_NAME
,X1.TYPE_NAME, X1.LENGTH, X1.IS_NULLABLE,X1.value ,X1.COLUMN_DEF
,IS_PK = case
when X3.colid is null then null
else 'PK'
end
,IS_FK = case
when X2.colnm is null then null
else 'FK'
end
FROM
(
SELECT T2.GID, T2.TABLE_QUALIFIER,T2.TABLE_OWNER, T2.TABLE_NAME, T2.COLUMN_NAME
, T2.TYPE_NAME, T2.LENGTH, T2.IS_NULLABLE,T1.value ,T2.COLUMN_DEF, T2.colid
FROM
(
SELECT
GID = O.ID
,TABLE_QUALIFIER = convert(sysname,DB_NAME())
,TABLE_OWNER = convert(sysname,USER_NAME(o.uid))
,TABLE_NAME = convert(sysname,o.name)
,COLUMN_NAME = convert(sysname,c.name)
,TYPE_NAME = convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end)
,LENGTH = convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end)
,COLUMN_DEF = text
,IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)))
,c.colid
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on
c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = c.id
AND o.xtype = 'U'
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = 2)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype AND O.NAME<>'dtproperties'
) T2
LEFT OUTER JOIN (
select * from sysproperties
where name = 'MS_Description' and smallid != 0
) T1
on
T2.gid = T1.id
and T2.colid = T1.smallid
) X1
Left Outer join
(
select parent_obj [tb_id] , sc.name[colnm] , sc.colid from sysobjects so
inner join sysreferences sf on
so.id = sf.constid
inner join syscolumns sc on
(
sf.fkeyid = sc.id
and (
sf.fkey1 = sc.colid or sf.fkey2 = sc.colid
or sf.fkey3 = sc.colid or sf.fkey4 = sc.colid
or sf.fkey5 = sc.colid or sf.fkey6 = sc.colid
or sf.fkey7 = sc.colid or sf.fkey8 = sc.colid
or sf.fkey9 = sc.colid or sf.fkey10 = sc.colid
or sf.fkey11 = sc.colid or sf.fkey12 = sc.colid
or sf.fkey13 = sc.colid or sf.fkey14 = sc.colid
or sf.fkey15 = sc.colid or sf.fkey16 = sc.colid
)
)
where so.xtype='F'
) X2 on
X1.GID = X2.tb_id
and X1.colid = X2.colid
Left outer join (
select o.id, c.colid from sysobjects o, syscolumns c, sysindexes i, sysindexkeys k
where
o.id = c.id
and i.id = k.id
and i.indid = k.indid
and k.id = c.id
and k.colid = c.colid
and i.name like 'PK%'
) X3 on
X1.GID = X3.id
and X1.colid = X3.colid
order by X1.TABLE_QUALIFIER,X1.TABLE_OWNER, X1.TABLE_NAME,X1.colid