提取数据库内所有表的字段详细说明
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(*)
fromsysobjects
where (name in
(selectname
fromsysindexes
where (id = a.id) and (indid in
(selectindid
fromsysindexkeys
where (id = a.id) and (colid in
(selectcolid
fromsyscolumns
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'字段说明'
fromsyscolumns a
left joinsystypes b
on a.xtype=b.xusertype
inner joinsysobjects d
on a.id=d.id and d.xtype='u' and d.name<>'dtproperties'
left joinsyscomments e
on a.cdefault=e.id
left joinsysproperties g
on a.id=g.id and a.colid =g.smallid
order by object_name(a.id),a.colorder
获取表结构的sql语句
declare @tablename varchar(255)
set @tablename='sysobjects' ---替换成要查询的表名
select case isnull(i.name, '')
when '' then ''
else '*'
end as ispk,
object_name(a.id) as t_name,
a.name as c_name,
isnull(substring(m.text, 1, 254), '') as pbc_init,
t.name as f_datatype,
case isnull(typeproperty(t.name, 'scale'), '')
when '' then cast(a.prec as varchar)
else cast(a.prec as varchar) + ',' + cast(a.scale as varchar)
end as f_scale,
a.isnullable as f_isnullable
from syscolumns as a
join systypes as t
on (a.xtype = t.xusertype and a.id = object_id(@tablename) )
left join ( sysindexes as i
join syscolumns as a1
on ( i.id = a1.id and a1.id = object_id(@tablename) and (i.status & 0x800) = 0x800 and a1.colid <= i.keycnt) )
on ( a.id = i.id and a.name = index_col(@tablename, i.indid, a1.colid) )
left join syscomments as m
on ( m.id = a.cdefault and objectproperty(a.cdefault, 'isconstraint') = 1 )
order by a.colid asc
