收获MSSQL 表构造中字段的备考、主键等信息的sql

1、MSSQL2000 复制代码 代码如下: SELECT
表名 = case when a.colorder=1 then d.name else ” end, 表说明 = case
when a.colorder=1 then isnull(f.value,”) else ” end, 字段序号 =
a.colorder, 字段名 = a.name, 标识 = case when COLUMNPROPERTY(
a.id,a.name,’IsIdentity’)=1 then ‘√’else ” end, 主键 = case when
exists(SELECT 1 FROM sysobjects where xtype=’PK’ and parent_obj=a.id
and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid
FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then ‘√’ else ”
end, 类型 = b.name, 占用字节数 = a.length, 长度 =
COLUMNPROPERTY(a.id,a.name,’PRECISION’), 小数位数 =
isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0), 允许空 = case when
a.isnullable=1 then ‘√’else ” end, 默认值 = isnull(e.text,”), 字段说明
= isnull(g.[value],”) FROM syscolumns a left join systypes b on
a.xusertype=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 left join sysproperties f on d.id=f.id and f.smallid=0
where d.name=’FI_dept’ –如果只查询指定表,加上此条件 order by
a.id,a.colorder 2、MSSQL2005 use test–数据库 go
–2005实现字段属性统计(2000里的系统表sysproperties描述表、字段不存在,2005里用sys.extended_properties视图替代)
select [表名]=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 where parent_object_id=a.object_id and type=N’PK’
and name in (select Name from sys.indexes where index_id in (select
indid from sysindexkeys where and 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,
[长度]=ColumnProperty(a.object_id,a.Name,’Precision’),
[小数]=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 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 [/code] 结果:

–自增方面的數據

SELECT
    表名       = case when a.colorder=1 then d.name else ” end,
    表说明     = case when a.colorder=1 then isnull(f.value,”) else ”
end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1
then ‘√’else ” end,
    主键       = case when exists(SELECT 1 FROM sysobjects where
xtype=’PK’ and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id
AND colid=a.colid))) then ‘√’ else ” end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,’PRECISION’),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0),
    允许空     = case when a.isnullable=1 then ‘√’else ” end,
    默认值     = isnull(e.text,”),
    字段说明   = isnull(g.[value],”)
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=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 
left join
    sysproperties f
on
    d.id=f.id and f.smallid=0

Select so.name Table_name, –表名字
sc.name Iden_Column_name, –自增字段名字
ident_current(so.name) curr_value, –自增字段当前值
ident_incr(so.name) incr_value, –自增字段增长值
ident_seed(so.name) seed_value –自增字段种子值
from sysobjects so
Inner Join syscolumns sc
on so.id = sc.id
and columnproperty(sc.id, sc.name, ‘IsIdentity’) = 1
Where upper(so.name) = upper(表名)

order by
    a.id,a.colorder

=====================MS SQL 2000============================