收集指定database 下的所有table的列名
use reportservertempdb --创建名称临时表，按名称顺序查询列信息 begin declare @count int declare @colid int declare @i_id int declare @i_rw int declare @sql varchar(8000) --删除表 begin try drop table ZP_DatabasesColumnName end try begin catch end catch --创建临时表 select a.rw,a.name as table_name,b.name as column_name,b.colid as column_id into #temp_columns from (select ROW_NUMBER() over(order by name) as rw ,name,id from sys.sysobjects where xtype='u' ) as a left join sys.syscolumns as b on a.id=b.id order by a.name,b.colid --select * from #temp_columns select @count=max(rw),@colid=max(column_id) from #temp_columns --创建表 begin set @sql= 'create table ZP_DatabasesColumnName(table_name varchar(50) primary key not null,' set @i_id=1 while @i_id<=@colid begin set @sql=@sql+'c'+convert(varchar(2),@i_id)+' varchar(50),' set @i_id=@i_id+1 end set @sql=@sql +')' exec (@sql) end --插入表名 begin insert into ZP_DatabasesColumnName(table_name) select distinct table_name from #temp_columns end --更新数据 set @i_id=1 while @i_id<=@colid begin set @sql='update ZP_DatabasesColumnName set ZP_DatabasesColumnName.c'+convert(varchar(3),@i_id)+ '=#temp_columns.column_name from #temp_columns '+ 'where ZP_DatabasesColumnName.table_name=#temp_columns.table_name and #temp_columns.column_id='+convert(varchar(3),@i_id) exec (@sql) set @i_id=@i_id+1 end --删除临时表 drop table #temp_columns select * from ZP_DatabasesColumnName end