mssql表名列名对应语句

if exists (select * from tempdb..sysobjects where name like '#magic%')
drop table #magic
go
select a.name name,b.name col,c.name type 
into #magic 
from sysobjects a,syscolumns b,systypes c
where a.xtype = 'U' 
and a.id = b.id 
and b.xtype = c.xtype 
and b.xusertype = c.xusertype

--将结果加工
select (case when tmp3.no=1 then tmp3.name else ' ' end) name,col,type 
from 
(select top 100000000 name
 ,(select count(1) from #magic tmp1 
   where tmp1.name=tmp2.name 
   and tmp1.col<=tmp2.col) as no,col,type
   from #magic tmp2 
   order by name,col,type) 
tmp3

做个笔记