获取SQL SERVER2012的数据库表结构



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 sys.extended_properties  g
-- on a.id=g.id AND a.colid = g.smallid 
 on a.id=g.major_id AND a.colid = g.major_id
 order by object_name(a.id),a.colorder

 declare @db varchar(30),@sql varchar(3000)
 set @db=db_name()
 set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -c -Usa -P '''
 --print @sql
 exec(@sql)

 select * from ##tx