'테이블'에 해당되는 글 1건
- 2009.02.13 SQL Server Table 명세서 출력 SP
출처 : sqler.pe.kr 에서 가져온 것 같음.
-- 2011년 3월 8일
-- 1. 디자인 깨지던 것 수정함.
-- 2. 테이블 설명 추가함.
-- p.s 예전부터 알고 있었는데 귀찮아서 안하다가 이제사 고침. 3년만에 고쳤네...
기본은 그대로 가져다 쓰고 약간 수정한 사항이 있고,
이후에는 html 등에서 사용가능하게 하면 좋을 것 같고,
엑셀변환도 클릭하면 되게끔 하면 좋을 것 같고,
sql 2000 시절에는 어떤 분이 만들어 두신 것 같은데...
sql 2005는 잘 없네요.
그래도 이게 어디냐...
SP도 아래와 같이 관리되면 좋을 텐데...
쩝....
p.s 2009년 5월 8일 추가 수정함.
table_desc.sql-- =============================================
-- SQL Database documentation script
-- Description: T-SQL script to generate the database document for SQL server 2000/2005
-- 기능 정의 : Database의 Table, View 에 대한 전체 명세서 출력
-- 수정일 : 2009년 2월 13일
-- 수정자 : 김민국
-- 수정내역 : 인쇄 시 편하게 보기 위해서 테이블 간에 page-break 추가.
-- 수정내역 : 전체 출력과 테이블명 검색 기능 구분 추가
-- 수정내역 : 단순 스크립트를 SP 형식으로 변경
-- 수정일 : 2009년 5월 8일
-- 수정자 : 김민국
-- 수정내역 : 상단 정렬 오류 수정
--
-- 실행 예 : exec table_desc '', ''
-- 실행 예 : exec table_desc '1', 'TB_KM_'
-- Drop PROCEDURE dbo.table_desc
-- =============================================
CREATE PROCEDURE [dbo].[table_desc]
@gubun varchar(1) -- '' : 전체 출력, '1' : 테이블명 검색 후 출력
,@TableNm varchar(50) -- 검색할 테이블명
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @SqlVersion varchar(5)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000)
create Table #Tables (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
If (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
set @SqlVersion = '2005'
else if (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server 2000')
set @SqlVersion = '2000'
else
set @SqlVersion = '2005'
Print '<html>'
Print '<head>'
Print '<title>::' + DB_name() + '::</title>'
Print '<style>'
Print ' body {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' td {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' th {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' background:#d3d3d3;'
Print ' }'
Print ' table'
Print ' {'
Print ' background:#d3d3d3;'
Print ' }'
Print ' tr'
Print ' {'
Print ' background:#ffffff;'
Print ' }'
Print ' </style>'
Print '</head>'
Print '<body>'
Print '<table border="0" cellspacing="0" cellpadding="0" width="750px">'
Print '<colgroup><col width="100%" /></colgroup>'
Print '<tr>'
Print '<td scope="col">'
set nocount on
if @SqlVersion = '2000'
begin
if @gubun = '' -- 전체 출력
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
order by table_type, table_schema, table_name
end
else -- 테이블 검색
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
where table_name like @TableNm +'%'
order by table_type, table_schema, table_name
end
end
else if @SqlVersion = '2005'
begin
if @gubun = '' -- 전체 출력
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
order by type, s.name, o.name
end
else -- 테이블 검색
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
and o.name like @TableNm + '%'
order by type, s.name, o.name
end
end
Set @maxi = @@rowcount
set @i = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="750px" align="left"><colgroup><col width="100%" /></colgroup><tr><td scope="col" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table><br /><br /><br /><br />'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px" align="left"><colgroup><col width="5%" /><col width="15%" /><col width="65%" /><col width="15%" /></colgroup><tr><th scope="col">Sr</th><th scope="col">Object</th><th scope="col">Description</th><th scope="col">Type</th></tr>'
While(@i <= @maxi)
begin
select @Output = '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + [description] + '</td><td>' + Type + '</td></tr>'
from #Tables where id = @i
print @Output
set @i = @i + 1
end
print '</table><br />'
print '<div style=''page-break-before: always;''><!--[if IE 7]><br style=''height:0; line-height:0''><![endif]--></div>'
Print '</td>'
Print '</tr>'
Print '</table>'
Print '<table border="0" cellspacing="0" cellpadding="0" width="750px">'
Print '<colgroup><col width="100%" /></colgroup>'
Print '<tr>'
Print '<td scope="col">'
set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output = '<tr><th scope="col" align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>', @description = [description]
from #Tables where id = @i
print '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col" align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />'
--table columns
truncate table #Columns
if @SqlVersion = '2000'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2000
Select c.name,
type_name(xtype) + (
case when (type_name(xtype) = 'varchar' or type_name(xtype) = 'nvarchar' or type_name(xtype) ='char' or type_name(xtype) ='nchar')
then '(' + cast(length as varchar) + ')'
when type_name(xtype) = 'decimal'
then '(' + cast(prec as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when isnullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(8000))
from syscolumns c
inner join #Tables t on t.object_id = c.id
left outer join sysproperties p on p.id = c.id and p.smallid = c.colid and p.name = 'MS_Description'
where t.id = @i
order by c.colorder
end
else if @SqlVersion = '2005'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2005
Select c.name,
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when is_nullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id = c.column_id and p.name = 'MS_Description'
where t.id = @i
order by c.column_id
end
Set @maxj = @@rowcount
set @j = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col" ><b>Table Columns</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="5%" /><col width="30%" /></colgroup><tr><th scope="col" >Sr.</th><th scope="col" >Name</th><th scope="c
ol" >Datatype</th><th scope="col" >Nullable</th><th scope="col" >Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + upper(isnull(Type,'')) + '</td><td scope="col" align="center">' + isnull(Nullable,'N') + '</td>
<td scope="col">' + isnull([description],'') + '</td></tr>'
from #Columns where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
--reference key
truncate table #FK
if @SqlVersion = '2000'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2000
select object_name(constid), s.name, object_name(rkeyid) , s1.name
from sysforeignkeys f
inner join sysobjects o on o.id = f.constid
inner join syscolumns s on s.id = f.fkeyid and s.colorder = f.fkey
inner join syscolumns s1 on s1.id = f.rkeyid and s1.colorder = f.rkey
inner join #Tables t on t.object_id = f.fkeyid
where t.id = @i
order by 1
end
else if @SqlVersion = '2005'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2005
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)
from sys.foreign_keys f
inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i
order by f.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Refrence Keys</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Column</th><t
h scope="col">Reference To</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + isnull(col,'') + '</td><td scope="col">[' + isnull(refObj,'N') + '].[' + isnull(refCol,'
N') + ']</td></tr>'
from #FK where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Default Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and
convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '10101000'
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), c.definition
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Default Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Column</th><t
h scope="col">Value</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + isnull(col,'') + '</td><td scope="col">' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Check Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and ( convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00101000'
or convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00100100')
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), definition
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Check Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Column</th><t
h scope="col">Definition</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + isnull(col,'') + '</td><td scope="col">' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Triggers
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name)
select tr.name
FROM sysobjects tr
inner join #Tables t on t.object_id = tr.parent_obj
where t.id = @i and tr.type = 'TR'
order by tr.name
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i
order by tr.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Triggers</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="45%" /><col width="50%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col"></td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Indexes
truncate table #Indexes
if @SqlVersion = '2000'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.indid = 0 then 'Heap' when i.indid = 1 then 'Clustered' else 'Nonclustered' end , c.name
from sysindexes i
inner join sysindexkeys k on k.indid = i.indid and k.id = i.id
inner join syscolumns c on c.id = k.id and c.colorder = k.colid
inner join #Tables t on t.object_id = i.id
where t.id = @i and i.name not like '_WA%'
order by i.name, i.keycnt
end
else if @SqlVersion = '2005'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end, col_name(i.object_id, c.column_id)
from sys.indexes i
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id
inner join #Tables t on t.object_id = i.object_id
where t.id = @i
order by i.name, c.column_id
end
Set @maxj = @@rowcount
set @j = 1
set @sr = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Indexes</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Type</th><th
scope="col">Columns</th></tr>'
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes where id = @j
if @last <> @current and @last <> ''
begin
print '<tr><td scope="col" align="center">' + Cast((@sr) as varchar) + '</td><td scope="col">' + @last + '</td><td scope="col">' + @typ + '</td><td scope="col">' + @Output + '</td></tr>'
set @Output = ''
set @sr = @sr + 1
end
select @Output = @Output + cols + '<br />' , @typ = type
from #Indexes where id = @j
set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print '<tr><td scope="col" align="center">' + Cast((@sr) as varchar) + '</td><td scope="col">' + @last + '</td><td scope="col">' + @typ + '</td><td scope="col">' + @Output + '</td></tr>'
end
print '</table><br />'
end
if @i < @maxi -- 마지막 페이지는 page-break 안함
begin
print '<div style=''page-break-before: always;''><!--[if IE 7]><br style=''height:0; line-height:0''><![endif]--></div>'
end
Set @i = @i + 1;
--Print @Output
end
Print '</td>'
Print '</tr>'
Print '</table>'
Print '</body>'
Print '</html>'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes
set nocount off
END
Prev
Rss Feed