显示数据库中所有表及其信息

ALTER PROCEDURE dbo.StoredProcedure1
AS
select (case when COLUMNPROPERTY( ss.id,ss.name,'IsIdentity')=1 then '1'else '0' end) N'标识',(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = ss.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = ss.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = ss.id) AND (name = ss.name))))))) AND (xtype = 'PK'))>0 then '1' else '0' end) N'主键',ss.colorder N'字段序号', (case when ss.isnullable=1 then '1'else '0' end) N'答应空', s.name as N'表名',ss.name as N'字段名' ,t.name as N'数据类型' , isnull(COLUMNPROPERTY(ss.id,ss.name,'Scale'),0) as N'小数位数', ss.length N'占用字节数', isnull(e.text,'') N'默认值', COLUMNPROPERTY(ss.id,ss.name,'PRECISION') as N'长度',g.[value] as N'字段描述' from sysobjects s Left join syscolumns ss on ss.id=s.id inner join systypes t On ( t.xtype=ss.xtype and t.name<>'sysname') inner join sysproperties g on (ss.id=g.id AND ss.colid = g.smallid) inner join sysindexkeys sk on (sk.id=ss.id and sk.colid=1) left join syscomments e
on ss.cdefault=e.id where s.Xtype='u' and s.name<>'dtproperties'
RETURN
--by huntfox