得到当前数据库中所有用户表信息

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 ##tx

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

====////sql////
declare @Sql nvarchar(2000)
set @Sql=

'SELECT  '+
' (case when a.colorder=1 then d.name else '+char(39)+''+char(39)+' end) N'+char(39)+'表名'+char(39)+', '+
' a.colorder N'+char(39)+'字段序号'+char(39)+', '+
' a.name N'+char(39)+'字段名'+char(39)+',   '+
' (case when COLUMNPROPERTY( a.id,a.name,'+char(39)+'IsIdentity'+char(39)+')=1 then '+char(39)+'√'+char(39)+'else '+char(39)+''+char(39)+' end) N'+char(39)+'标识'+char(39)+', '+
' (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 = '+char(39)+'PK'+char(39)+'))>0 then '+char(39)+'√'+char(39)+' else '+char(39)+''+char(39)+' end) N'+char(39)+'主键'+char(39)+', '+
' b.name N'+char(39)+'类型'+char(39)+', '+
' a.length N'+char(39)+'占用字节数'+char(39)+', '+
' COLUMNPROPERTY(a.id,a.name,'+char(39)+'PRECISION'+char(39)+') as N'+char(39)+'长度'+char(39)+', '+
' isnull(COLUMNPROPERTY(a.id,a.name,'+char(39)+'Scale'+char(39)+'),0) as N'+char(39)+'小数位数'+char(39)+', '+
' (case when a.isnullable=1 then '+char(39)+'√'+char(39)+'else '+char(39)+''+char(39)+' end) N'+char(39)+'允许空'+char(39)+', '+
' isnull(e.text,'+char(39)+''+char(39)+') N'+char(39)+'默认值'+char(39)+', '+
' isnull(g.[value],'+char(39)+''+char(39)+') AS N'+char(39)+'字段说明'+char(39)+' '+
--'--into ##tx '+
' '+
'FROM  syscolumns  a left join systypes b  '+
'on  a.xtype=b.xusertype '+
'inner join sysobjects d  '+
'on a.id=d.id  and  d.xtype='+char(39)+'U'+char(39)+' and  d.name<>'+char(39)+'dtproperties'+char(39)+' '+
'left join syscomments e '+
'on a.cdefault=e.id '+
'left join sysproperties g '+
'on a.id=g.id AND a.colid = g.smallid '

exec(@Sql)

===仅保留表名、字段名、字段类型和字段说明
declare @Sql nvarchar(2000)
set @Sql=

'SELECT  '+
--' (case when a.colorder=1 then d.name else '+char(39)+''+char(39)+' end) N'+char(39)+'表名'+char(39)+', '+
'  d.name  N'+char(39)+'表名'+char(39)+', '+
--' a.colorder N'+char(39)+'字段序号'+char(39)+', '+
' a.name N'+char(39)+'字段名'+char(39)+',   '+
--' (case when COLUMNPROPERTY( a.id,a.name,'+char(39)+'IsIdentity'+char(39)+')=1 then '+char(39)+'√'+char(39)+'else '+char(39)+''+char(39)+' end) N'+char(39)+'标识'+char(39)+', '+
--' (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 = '+char(39)+'PK'+char(39)+'))>0 then '+char(39)+'√'+char(39)+' else '+char(39)+''+char(39)+' end) N'+char(39)+'主键'+char(39)+', '+
' b.name N'+char(39)+'类型'+char(39)+', '+
--' a.length N'+char(39)+'占用字节数'+char(39)+', '+
--' COLUMNPROPERTY(a.id,a.name,'+char(39)+'PRECISION'+char(39)+') as N'+char(39)+'长度'+char(39)+', '+
--' isnull(COLUMNPROPERTY(a.id,a.name,'+char(39)+'Scale'+char(39)+'),0) as N'+char(39)+'小数位数'+char(39)+', '+
--' (case when a.isnullable=1 then '+char(39)+'√'+char(39)+'else '+char(39)+''+char(39)+' end) N'+char(39)+'允许空'+char(39)+', '+
--' isnull(e.text,'+char(39)+''+char(39)+') N'+char(39)+'默认值'+char(39)+', '+
' isnull(g.[value],'+char(39)+''+char(39)+') AS N'+char(39)+'字段说明'+char(39)+' '+
--'--into ##tx '+
' '+
'FROM  syscolumns  a left join systypes b  '+
'on  a.xtype=b.xusertype '+
'inner join sysobjects d  '+
'on a.id=d.id  and  d.xtype='+char(39)+'U'+char(39)+' and  d.name<>'+char(39)+'dtproperties'+char(39)+' '+
'left join syscomments e '+
'on a.cdefault=e.id '+
'left join sysproperties g '+
'on a.id=g.id AND a.colid = g.smallid '

exec(@Sql)

连字段说明都去掉
declare @Sql nvarchar(2000)
set @Sql=

'SELECT  '+
--' (case when a.colorder=1 then d.name else '+char(39)+''+char(39)+' end) N'+char(39)+'表名'+char(39)+', '+
'  d.name  N'+char(39)+'表名'+char(39)+', '+
--' a.colorder N'+char(39)+'字段序号'+char(39)+', '+
' a.name N'+char(39)+'字段名'+char(39)+',   '+
--' (case when COLUMNPROPERTY( a.id,a.name,'+char(39)+'IsIdentity'+char(39)+')=1 then '+char(39)+'√'+char(39)+'else '+char(39)+''+char(39)+' end) N'+char(39)+'标识'+char(39)+', '+
--' (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 = '+char(39)+'PK'+char(39)+'))>0 then '+char(39)+'√'+char(39)+' else '+char(39)+''+char(39)+' end) N'+char(39)+'主键'+char(39)+', '+
' b.name N'+char(39)+'类型'+char(39)+' '+
--' a.length N'+char(39)+'占用字节数'+char(39)+', '+
--' COLUMNPROPERTY(a.id,a.name,'+char(39)+'PRECISION'+char(39)+') as N'+char(39)+'长度'+char(39)+', '+
--' isnull(COLUMNPROPERTY(a.id,a.name,'+char(39)+'Scale'+char(39)+'),0) as N'+char(39)+'小数位数'+char(39)+', '+
--' (case when a.isnullable=1 then '+char(39)+'√'+char(39)+'else '+char(39)+''+char(39)+' end) N'+char(39)+'允许空'+char(39)+', '+
--' isnull(e.text,'+char(39)+''+char(39)+') N'+char(39)+'默认值'+char(39)+', '+
--' isnull(g.[value],'+char(39)+''+char(39)+') AS N'+char(39)+'字段说明'+char(39)+' '+
--'--into ##tx '+
' '+
' FROM  syscolumns  a left join systypes b  '+
'on  a.xtype=b.xusertype '+
'inner join sysobjects d  '+
'on a.id=d.id  and  d.xtype='+char(39)+'U'+char(39)+' and  d.name<>'+char(39)+'dtproperties'+char(39)+' '+
'left join syscomments e '+
'on a.cdefault=e.id '+
'left join sysproperties g '+
'on a.id=g.id AND a.colid = g.smallid '

exec(@Sql)