HuhuShoW IS NULL

아래와 같은 스크립트를 이용한다.
2K5 이후부터는 시스템 테이블의 구조가 변경되긴 하지만 2K 호환용 시스템 뷰가 있기때문에 사용할 수 있다.

declare @name varchar(128)
set @name = 'tbMemberEmail'


declare @col_table varchar(8000)
set @col_table = ''

select @col_table = @col_table + '|-
| ' + CASE WHEN P.colid IS NOT NULL THEN '''''''' + c.[name] + '''''''' ELSE c.[name] END + ' || ' + t.[name]
+ case when c.xtype IN (167,175) then '('+ convert(varchar(10),c.[length]) +')' else '' end
+ ' || ' + ISNULL((select substring([text],2,len([text])-2) from dbo.syscomments where id = c.cdefault),'')
+ ' || ' + CASE isnullable WHEN 0 THEN 'N' WHEN 1 THEN 'Y' ELSE '' END
+ ' || description
'
from syscolumns as c inner join systypes as t
on c.xtype = t.xtype
left outer join
(
select kp.colid from
sysobjects as op
inner join sysindexes as ip on op.name = ip.name
inner join sysindexkeys as kp on kp.id = op.parent_obj and ip.indid = kp.indid
where op.parent_obj = object_id(@name) and op.xtype = 'PK'
) as p on c.colid = p.colid
where c.[id] = object_id(@name) order by c.colid


declare @col_index varchar(8000), @icnt tinyint, @icnt2 tinyint
declare @rcnt tinyint, @rcnt2 tinyint

set @col_index = ''
set @icnt = 0
set @rcnt = 0

select @icnt = COUNT(*), @icnt2 = MIN(indid)
from sysindexes where [id] = object_id(@name) and indid > 0 and name not like '[_]%'

while @icnt > 0
begin
    select
    @col_index = @col_index + '
*' + [name] + ' - ' + CASE indid WHEN 1 THEN '(Unique/)Clustered' ELSE '(Unique)' END
    from sysindexes where [id] = object_id(@name) and indid = @icnt2

    select @rcnt = COUNT(*), @rcnt2 = MIN(keyno)
    from sysindexkeys where [id] = object_id(@name) and indid = @icnt2

    while @rcnt > 0
    begin
        select @col_index = @col_index + '
**' + c.name + ' - (ASC/DESC)'
        from sysindexkeys as k inner join syscolumns as c on k.id = c.id and k.colid = c.colid
        where k.[id] = object_id(@name) and k.indid = @icnt2 and k.keyno = @rcnt2
        set @rcnt = @rcnt - 1
        select top 1 @rcnt2 = keyno
        from sysindexkeys where id = OBJECT_ID(@name) and indid = @icnt2 and keyno > @rcnt2
        order by keyno asc
    end
   
    set @icnt = @icnt -1
    select top 1 @icnt2 = indid
    from sysindexes where [id] = object_id(@name) and indid > @icnt2 and name not like '[_]%'
    order by indid asc
    set @col_index = @col_index + '
    <br/>'
end


declare @col_df varchar(8000)

set @col_df = ''

select @col_df = @col_df + '
*' + o.name + '
**' + col.name + ' - default : ' + substring(c.[text],2,len(c.[text])-2) + '
<br/>'
from sysobjects as o inner join syscomments as c on o.id = c.id
inner join sysconstraints as cc on o.id = cc.constid
inner join syscolumns as col on cc.id = col.id and cc.colid = col.colid
where o.parent_obj = object_id(@name) and o.xtype = 'D'



declare @col_fk varchar(8000), @rname varchar(100), @rkey varchar(100), @rkey1 varchar(100)
declare @rkey2 varchar(100), @rkey3 varchar(100), @rkey4 varchar(100), @rkey5 varchar(100)
declare @fkey varchar(100), @fkey1 varchar(100), @fkey2 varchar(100), @fkey3 varchar(100)
declare @fkey4 varchar(100), @fkey5 varchar(100)

select
@rname = o.name,
@rkey = object_name(r.rkeyid),
@rkey1 = (select name from syscolumns where id = r.rkeyid and colid = r.rkey1),
@rkey2 = (select name from syscolumns where id = r.rkeyid and colid = r.rkey2),
@rkey3 = (select name from syscolumns where id = r.rkeyid and colid = r.rkey3),
@rkey4 = (select name from syscolumns where id = r.rkeyid and colid = r.rkey4),
@rkey5 = (select name from syscolumns where id = r.rkeyid and colid = r.rkey5),
@fkey = object_name(r.fkeyid),
@fkey1 = (select name from syscolumns where id = r.fkeyid and colid = r.fkey1),
@fkey2 = (select name from syscolumns where id = r.fkeyid and colid = r.fkey2),
@fkey3 = (select name from syscolumns where id = r.fkeyid and colid = r.fkey3),
@fkey4 = (select name from syscolumns where id = r.fkeyid and colid = r.fkey4),
@fkey5 = (select name from syscolumns where id = r.fkeyid and colid = r.fkey5)
from sysobjects as o inner join sysreferences as r on o.id = r.constid
where o.parent_obj = object_id(@name) and o.xtype = 'F'

set @col_fk = ISNULL('
*' + @rname + '
**기본키 : ' + @rkey + '
***' + @rkey1 + case when @rkey2 IS NULL then '' else '
***'+ @rkey2 end + case when @rkey3 IS NULL then '' else '
***'+ @rkey3 end + case when @rkey4 IS NULL then '' else '
***'+ @rkey4 end + case when @rkey5 IS NULL then '' else '
***'+ @rkey5 end + '
**외래키 : ' + @fkey + '
***' + @fkey1 + case when @fkey2 IS NULL then '' else '
***'+ @fkey2 end + case when @fkey3 IS NULL then '' else '
***'+ @fkey3 end + case when @fkey4 IS NULL then '' else '
***'+ @fkey4 end + case when @fkey5 IS NULL then '' else '
***'+ @fkey5 end + '
<br/>','
<br/>')


PRINT
'
===테이블명===
<br/>
'
+
@name
+
'
<br/>
<br/>
----
<br/>
===테이블 설명===
<br/>
<br/>
----
<br/>
===테이블 명세===
<br/>
<!--PK의 경우 ''''''PK''''''로 굵게 표시-->
{|border="1" color="black" width="100%" align="left"
! 칼럼명 !! 자료형 !! 기본값 !! NULL허용 !! 메모
' +
@col_table
+
'
|}
<br/>
----
<br/>

===색인==='
+
@col_index
+
'
----
<br/>
===관계키===
<!-- 테이블명은 링크를 걸어준다 -->
<!-- 물리적으로 제약조건을 생성하지 않았을 경우 fa_name에 미생성_ 이라고 표기 -->
<!-- 작성 시 주석 제거
*fk_name
**기본키 : table_name
***col1
***col2
**외래키 : table_name
***col1
***col2
**참조무결성설정
작성 시 주석 제거 -->'
+ @col_fk + '
----
<br/>
===제약조건===
<!-- 작성 시 주석 제거 기본값
*DF_name
**colname - default : df_value
작성 시 주석 제거 -->'
+ @col_df +
'
<br/>
[[분류:table]]
'

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.