我最近需要将sql server2000中的某个库 结构导出来,输出到word或Excel中,需要知道库中若干个表的详细结构,网上一搜,的确牛人很多,一个sql就搞定。看来学无止境呀!!!
以下代码复制即可使用:
SELECT
( case when a.colorder = 1 then d.name else '' end ) N ' 表名 ' ,
a.colorder N ' 字段序号 ' ,
a.name N ' 字段名 ' ,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) N ' 标识 ' ,
( case when ( SELECT count ( * )
FROM sysobjects
WHERE (name in
( SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
( SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
( SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = ' PK ' )) > 0 then ' √ ' else '' end ) N ' 主键 ' ,
b.name N ' 类型 ' ,
a.length N ' 占用字节数 ' ,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as N ' 长度 ' ,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as N ' 小数位数 ' ,
( case when a.isnullable = 1 then ' √ ' else '' end ) N ' 允许空 ' ,
isnull (e. text , '' ) N ' 默认值 ' ,
isnull (g. [ value ] , '' ) AS N ' 字段说明 '
-- into ##qqp_528
FROM syscolumns a left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by object_name (a.id),a.colorder
( case when a.colorder = 1 then d.name else '' end ) N ' 表名 ' ,
a.colorder N ' 字段序号 ' ,
a.name N ' 字段名 ' ,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) N ' 标识 ' ,
( case when ( SELECT count ( * )
FROM sysobjects
WHERE (name in
( SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
( SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
( SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = ' PK ' )) > 0 then ' √ ' else '' end ) N ' 主键 ' ,
b.name N ' 类型 ' ,
a.length N ' 占用字节数 ' ,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as N ' 长度 ' ,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as N ' 小数位数 ' ,
( case when a.isnullable = 1 then ' √ ' else '' end ) N ' 允许空 ' ,
isnull (e. text , '' ) N ' 默认值 ' ,
isnull (g. [ value ] , '' ) AS N ' 字段说明 '
-- into ##qqp_528
FROM syscolumns a left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by object_name (a.id),a.colorder
在“查询分析器”中打开某一数据库,将以上代码复制后执行,即可出现库中所有表结构的详细信息。