--//Author: Nitin Patel,Email: nitinpatel31@gmail.com --//Date:18-Feb-2008 --//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,Type Varchar(155),Nullable varchar(2),[description] varchar(4000)) create table #Fk(ID int IDentity(1,col Varchar(155),refObj varchar(155),refCol varchar(155)) create table #Constraint(ID int IDentity(1,deFinition varchar(1000)) create table #Indexes(ID int IDentity(1,Type Varchar(25),cols varchar(1000)) If (substring(@@VERSION,1,25 ) = 'Microsoft sql Server 2005') set @sqlVersion = '2005' else if (substring(@@VERSION,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,[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" ><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,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,[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) + ', 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), 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,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,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,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>' + 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,deFinition) select object_name(c.constID),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 & 128)/128) = '00100100') end else if @sqlVersion = '2005' begin insert into #Constraint (name,deFinition) select c.name,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></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,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 总结
以上是内存溢出为你收集整理的生成数据字典(sqlserver)全部内容，希望文章能够帮你解决生成数据字典(sqlserver)所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）