SQL 表结构查询

 --查询数据库中所有的表
select name from sysobjects where xtype='u'
--查询数据库中所有的触发器
select name from sysobjects where xtype='tr'

--查询数据库中所有的存储过程
select name from sysobjects where type='P' and (name not like 'dt_%') and name not like 'sp_%'

--查询表结构
SELECT 表名=case when a.colorder=1 then d.name 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 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,'')
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
where d.name='CARD_RECORD_TBL'
Order by a.id,a.colorder

--查询表结构
select a.colid as ID,a.name as 列名,b.name as 类型,
(case b.name when 'decimal' then a.xprec else a.length end) as 长度,
(case b.name when 'decimal' then a.xscale else 0 end) as 小数位数,
case a.isnullable when 1 then '是' else '否' end as 允许空
from syscolumns a,systypes b
where a.id=object_id('CARD_RECORD_TBL') and a.xtype=b.xtype
order by id