MSSQL2000, 2005 테이블 명세서 추출 쿼리문
--//SQL Database documentation script
--//Description: T-SQL script to generate the database document for SQL server 2000/2005
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 '<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>'
set nocount on
if @SqlVersion = '2000'
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 if @SqlVersion = '2005'
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
Set @maxi = @@rowcount
set @i = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="550px" align="center"><tr><td colspan="3" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="550px" align="center"><tr><th>Sr</th><th>Object</th><th>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>' + Type + '</td></tr>'
from #Tables where id = @i
print @Output
set @i = @i + 1
end
print '</table><br />'
set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output = '<tr><th 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"><tr><td align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><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"><tr><td><b>Table Columns</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Nullable</th><th>Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Nullable,'N') + '</td><td>' + 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"><tr><td><b>Refrence Keys</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Reference To</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>[' + 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"><tr><td><b>Default Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Value</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>' + 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"><tr><td><b>Check Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Definition</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td width="150px">' + isnull(col,'') + '</td><td>' + 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"><tr><td><b>Triggers</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'') + '</td><td></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"><tr><td><b>Indexes</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Type</th><th>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 width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @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 width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output + '</td></tr>'
end
print '</table><br />'
end
Set @i = @i + 1;
--Print @Output
end
Print '</body>'
Print '</html>'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes
set nocount off
출처: http://www.taeyo.pe.kr/pds/Content.aspx?SEQ=3171&TBL=UPLOAD&PGN=1
지르며 맞는 실행에 대답한 키스숏은
<img src="http://cm8282.com/3/4.jpg">
<p><br></p>
<div style="width: 1px; height: 1px; overflow: hidden">
<div align="center">
카지노사이트-http://gom77.com
바카라사이트-http://aa.gom77.com
온라인카지노-http://bb.gom77.com
온라인바카라-http://cc.gom77.com
카지노 게임-http://dd.gom77.com
룰렛사이트-http://ee.gom77.com
모바일바카라-http://ff.gom77.com
모바일카지노-http://gg.gom77.com
모바일슬롯머신-http://hh.gom77.com
모바일룰렛-http://ii.gom77.com
온라인토토-http://jj.gom77.com
온라인슬롯머신-http://kk.gom77.com
온라인룰렛-http://ll.gom77.com
온라인블랙잭-http://mm.gom77.com
경마사이트-http://nn.gom77.com
비트코인사이트-http://oo.gom77.com
카지노사이트-http://cm8282.com
온라인카지노-http://aa.cm8282.com
무료 카지노 게임-http://ak.cm8282.com
우리카지노-http://am.cm8282.com
룰렛사이트-http://aoa.cm8282.com
카지노바카라-http://bm.cm8282.com
강원랜드후기-http://ca.cm8282.com
호텔카지노-http://cf.cm8282.com
아바타카지노-http://cm.cm8282.com
아바타바카라-http://cn.cm8282.com
카지노추천-카지노주소-http://cs.cm8282.com
cod카지노-http://cv.cm8282.com
모바일블랙잭-http://eg.cm8282.com
호게임-http://et.cm8282.com
온라인포커-http://ex.cm8282.com
온라인슬롯머신-http://ff.cm8282.com
카지노놀이터-http://gg.cm8282.com
카지노게임사이트-http://gm.cm8282.com
온라인블랙잭-http://gp.cm8282.com
인터넷카지노-http://hh.cm8282.com
카지노싸이트-http://hp.cm8282.com
모바일바카라-http://in.cm8282.com
모바일카지노-http://io.cm8282.com
무료 블랙잭 게임-블랙잭전략-http://jj.cm8282.com
바카라이기는방법-바카라필승전략-http://ka.cm8282.com
온라인바카라-http://kk.cm8282.com
바카라타이-바카라게임방법-바카라잘하는방법-http://la.cm8282.com
바카라확률-바카라페어-바카라규칙-http://lt.cm8282.com
바카라사이트-http://mm8282.com
인터넷바카라-http://mk.mm8282.com
먹튀검증사이트-http://mo.mm8282.com
모바일홀덤-http://na.mm8282.com
스포츠중계사이트-http://nb.mm8282.com
모바일슬롯머신-http://ng.mm8282.com
온라인룰렛-http://nn.mm8282.com
룰렛배당률-룰렛전략-http://no.mm8282.com
모바일룰렛-http://ns.mm8282.com
블랙잭사이트-http://od.mm8282.com
식보사이트-http://oh.mm8282.com
홀덤사이트-http://ok.mm8282.com
온라인홀덤-http://ol.mm8282.com
포커사이트-http://oo.mm8282.com
경마사이트-http://oz.mm8282.com
온라인경마-http://pa.mm8282.com
모바일경마-http://pc.mm8282.com
온라인맞고-http://pm.mm8282.com
일본av배우사이트-http://px.mm8282.com
마사지사이트-http://qin.mm8282.com
안마사이트-http://qq.mm8282.com
채팅사이트-http://ra.mm8282.com
야동사이트-http://scv.mm8282.com
바둑이사이트-http://xx.mm8282.com
온라인토토-http://yo.mm8282.com
사설토토사이트-메이저놀이터-http://zm.mm8282.com
바카라게임사이트-http://zo.mm8282.com
85409355</div>
모바일룰렛-http://ii.gom77.com
온라인토토-http://jj.gom77.com
온라인슬롯머신-http://kk.gom77.com
온라인룰렛-http://ll.gom77.com
온라인블랙잭-http://mm.gom77.com
경마사이트-http://nn.gom77.com
비트코인사이트-http://oo.gom77.com
다이사이사이트-http://pp.gom77.com
바카라이기는노하우-http://rr.gom77.com
바카라숫자흐름-http://rr.gom77.com
바카라백전백승-http://ss.gom77.com
카지노슬롯게임-http://ss.gom77.com
강원랜드이기는방법-http://tt.gom77.com
카지노사이트-http://cm8282.com
온라인카지노-http://aa.cm8282.com
카지노게임-http://ak.cm8282.com
우리카지노-http://am.cm8282.com
룰렛사이트-http://aoa.cm8282.com
카지노바카라-http://bm.cm8282.com
강원랜드후기-http://ca.cm8282.com
호텔카지노-http://cf.cm8282.com
아바타카지노-http://cm.cm8282.com
아바타바카라-http://cn.cm8282.com
카지노추천-http://cs.cm8282.com
카지노주소-http://cs.cm8282.com
cod카지노-http://cv.cm8282.com
모바일블랙잭-http://eg.cm8282.com
호게임-http://et.cm8282.com
온라인포커-http://ex.cm8282.com
온라인슬롯머신-http://ff.cm8282.com
카지노놀이터-http://gg.cm8282.com
카지노게임사이트-http://gm.cm8282.com
온라인블랙잭-http://gp.cm8282.com
인터넷카지노-http://hh.cm8282.com
카지노싸이트-http://hp.cm8282.com
모바일바카라-http://in.cm8282.com
모바일카지노-http://io.cm8282.com
블랙잭게임-http://jj.cm8282.com
블랙잭전략-http://jj.cm8282.com
바카라이기는방법-http://ka.cm8282.com
바카라필승전략-http://ka.cm8282.com
온라인바카라-http://kk.cm8282.com
바카라타이-http://la.cm8282.com
바카라게임방법-http://la.cm8282.com
바카라잘하는방법-http://la.cm8282.com
바카라확률-http://lt.cm8282.com
바카라페어-http://lt.cm8282.com
바카라규칙-http://lt.cm8282.com
바카라사이트-http://mm8282.com
인터넷바카라-http://mk.mm8282.com
먹튀검증사이트-http://mo.mm8282.com
모바일홀덤-http://na.mm8282.com
스포츠중계사이트-http://nb.mm8282.com
모바일슬롯머신-http://ng.mm8282.com
온라인룰렛-http://nn.mm8282.com
룰렛배당률-http://no.mm8282.com
룰렛전략-http://no.mm8282.com
모바일룰렛-http://ns.mm8282.com
블랙잭사이트-http://od.mm8282.com
식보사이트-http://oh.mm8282.com
홀덤사이트-http://ok.mm8282.com
온라인홀덤-http://ol.mm8282.com
포커사이트-http://oo.mm8282.com
경마사이트-http://oz.mm8282.com
온라인경마-http://pa.mm8282.com
모바일경마-http://pc.mm8282.com
온라인맞고-http://pm.mm8282.com
av사이트-http://px.mm8282.com
마사지사이트-http://qin.mm8282.com
안마사이트-http://qq.mm8282.com
채팅사이트-http://ra.mm8282.com
야동사이트-http://scv.mm8282.com
바둑이사이트-http://xx.mm8282.com
온라인토토-http://yo.mm8282.com
사설토토사이트-http://zm.mm8282.com
메이저놀이터-http://zm.mm8282.com
바카라게임사이트-http://zo.mm8282.com
카지노사이트-http://gom77.com
바카라사이트-http://aa.gom77.com
온라인카지노-http://bb.gom77.com
온라인바카라-http://cc.gom77.com
카지노게임-http://dd.gom77.com
룰렛사이트-http://ee.gom77.com
모바일바카라-http://ff.gom77.com
모바일카지노-http://gg.gom77.com