求按如下要求获得SQL字典--表结构

Question

数据库名,表名,字段序号,架构,字段名,标识,主键,类型,长度,精度,小数位数,允许空,默认值,创建时间,更改时间,表说明,字段说明
master    MSreplication_options 1 dbo optname nvarchar 256 128 0 2008-07-09 16:53:54.490 2008-07-09 16:53:54.490
master    MSreplication_options 2 dbo value bit 1 1 0 2008-07-09 16:53:54.490 NULL
......大家可以参考的资源:
SELECT TOP 100 PERCENT --a.id, 
      CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, 
      CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, 
      a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, 
      a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 
      CASE WHEN EXISTS
          (SELECT 1
         FROM dbo.sysindexes si INNER JOIN
               dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
               dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
               dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
         WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 
      b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') 
      AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, 
      CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') 
      AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, 
      CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
      dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
      dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND 
      d.status >= 0 LEFT OUTER JOIN
      dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
      sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND 
      g.name = 'MS_Description' LEFT OUTER JOIN
      sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND 
      f.name = 'MS_Description'
ORDER BY d.name, 字段序号

Answer 1

建议尽量使用目录视图，比如sysobjects 用sys.objects代替，因为往后的版本可能就不会让你访问sysobjects

Answer 2

SQL2oo5参照
http://blog.csdn.net/roy_88/article/details/1914264

Answer 3

select
[数据库名] = db_name(),
[架构名] = s.name, 
    [表名]=c.Name,
    [表说明]=isnull(f.[value],''),
    [列名]=a.Name,
    [列序号]=a.Column_id,
    [标识]=case when is_identity=1 then '√' else '' end,
    [主键]=case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name 
                        join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)
                    then '√' else '' end,
    [类型]=b.Name,
    [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G' 
            when b.Name='xml' then '2^31-1字节/2G'
            else rtrim(a.[max_length]) end,
    [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1'
                else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,
    [小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
    [是否为空]=case when a.is_nullable=1 then '√' else '' end,
    [列说明]=isnull(e.[value],''),
    [默认值]=isnull(d.text,'')    
from 
    sys.columns a
left join
    sys.types b on a.user_type_id=b.user_type_id
inner join
    sys.objects c on a.object_id=c.object_id and c.Type='U'
left join 
sys.schemas s on c.schema_id = s.schema_id
left join
    syscomments d on a.default_object_id=d.ID
left join
    sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 
left join
    sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class = 1

Answer 4

declare @database_name nvarchar(1000);
declare @sql nvarchar(max);
declare database_cursor cursor for
select name
from sys.databases
order by database_id;set @sql = '';open database_cursor;
fetch next from database_cursor into @database_name;while @@fetch_status = 0
begin
set @sql = @sql + 'select [数据库名] = ''' + @database_name + ''',';
set @sql = @sql + '[架构名] = s.name collate Chinese_PRC_CI_AS,';
set @sql = @sql + '[表名]=c.Name collate Chinese_PRC_CI_AS,';
set @sql = @sql + '[表说明]=isnull(f.[value],''''),';
set @sql = @sql + '[列名]=a.Name collate Chinese_PRC_CI_AS,';
set @sql = @sql + '[列序号]=a.Column_id,';
set @sql = @sql + '[标识]=case when is_identity=1 then ''√'' else '''' end,';
set @sql = @sql + '[主键]=case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N''PK'' and x.Name=y.Name '; 
    set @sql = @sql + 'join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id) ';
    set @sql = @sql + 'then ''√'' else '''' end,';
set @sql = @sql + '[类型]=b.Name collate Chinese_PRC_CI_AS,';
set @sql = @sql + '[字节数]=case when a.[max_length]=-1 and b.Name!=''xml'' then ''max/2G'' '; 
set @sql = @sql + 'when b.Name=''xml'' then ''2^31-1字节/2G'' ';
set @sql = @sql + 'else rtrim(a.[max_length]) end,';
set @sql = @sql + '[长度]=case when ColumnProperty(a.object_id,a.Name,''Precision'')=-1 then ''2^31-1'' ';
set @sql = @sql + 'else rtrim(ColumnProperty(a.object_id,a.Name,''Precision'')) end,';
set @sql = @sql + '[小数]=isnull(ColumnProperty(a.object_id,a.Name,''Scale''),0),';
set @sql = @sql + '[是否为空]=case when a.is_nullable=1 then ''√'' else '''' end,';
set @sql = @sql + '[列说明]=isnull(e.[value],''''),';
set @sql = @sql + '[默认值]=isnull(d.text,'''') collate Chinese_PRC_CI_AS ';
set @sql = @sql + 'from ' + @database_name + '.sys.columns a ';
set @sql = @sql + 'left join ' + @database_name + '.sys.types b on a.user_type_id=b.user_type_id ';
set @sql = @sql + 'inner join ' + @database_name + '.sys.objects c on a.object_id=c.object_id and c.Type=''U'' ';
set @sql = @sql + 'left join ' + @database_name + '.sys.schemas s on c.schema_id = s.schema_id ';
set @sql = @sql + 'left join ' + @database_name + '.sys.syscomments d on a.default_object_id=d.ID ';
set @sql = @sql + 'left join ' + @database_name + '.sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 ';
set @sql = @sql + 'left join ' + @database_name + '.sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class = 1 ';
set @sql = @sql + 'union all ';
fetch next from database_cursor into @database_name;
end;close database_cursor;
deallocate database_cursor;set @sql = reverse(substring(reverse(@sql),12, 800000));
exec(@sql);