收集指定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