if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P') drop proc proc_sky_blue go create proc proc_sky_blue (@tablename varchar(200)) as begin set nocount on declare @col nvarchar(20) declare @makesql nvarchar(800) declare @insertsql nvarchar(800) declare @caculatesql nvarchar(800) declare @count int declare @i int create table #tmp (colname nvarchar(20)) select @caculatesql = 'select @count=count(1) from ' + @tablename exec sp_executesql @caculatesql, N'@count int output',@count output select @i=0 while @count >0 begin select @i=@i+1 select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int' exec(@makesql) select @count=@count-1 end declare my_cursor cursor for select name from syscolumns where id=object_id(@tablename) order by colid open my_cursor fetch next from my_cursor into @col while @@fetch_status = 0 begin select @makesql ='select @insertsql= @insertsql + convert(varchar(12),'+@col+') +'', '' from ' +@tablename select @insertsql =N'insert #tmp values ( '''+@col+ ''' ,' execute sp_executesql @makesql,N'@insertsql nvarchar(800) output' ,@insertsql output select @insertsql = left(@insertsql,len(@insertsql)-1) +')' exec(@insertsql) fetch next from my_cursor into @col end close my_cursor deallocate my_cursor select * from #tmp set nocount off endgo
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(20)
declare @makesql nvarchar(800)
declare @insertsql nvarchar(800)
declare @caculatesql nvarchar(800)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(12),'+@col+') +'', '' from ' +@tablename
select @insertsql =N'insert #tmp values ( '''+@col+ ''' ,'
execute sp_executesql @makesql,N'@insertsql nvarchar(800) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
endgo