CREATE procedure sp_0200FinalView
(
@database varchar(100),			-- name of database to create table in
@tablename varchar(100)			-- name of table to be created
)
as

set nocount on

-- =============================================
-- Variable declarations 
-- =============================================

declare @sql  nvarchar(4000)		
declare @fieldlist nvarchar(4000)		
declare @tablelist nvarchar(4000)		
declare @maxfield int, @maxtable int, @i int
set @i=0
set @sql=''
set @fieldlist=''
set @tablelist=''

-- =============================================
-- Store databasename and tablename in tables
-- =============================================

if exists (select name from sysobjects where name='databasename' and type ='U')
	drop table databasename
if exists (select name from sysobjects where name='tablename' and type ='U')
	drop table tablename
select @database as databasename into databasename
select @tablename as tablename into tablename

-- drop view if exists

set @sql = 'if exists (select name from '+@database+'.dbo.sysobjects '
set @sql = @sql+'where name='''+@tablename+'_DB_VIEW_ALL'' and type =''V'') '
set @sql = @sql+'begin use '+@database+' drop view '+@tablename+'_DB_VIEW_ALL end '

exec (@sql)

-- create table of variable names and tables 

set @sql='create table cview (fieldname varchar(100),tablename varchar(100),id_num int identity(1,1)) '
set @sql=@sql+'insert into cview (fieldname,tablename) '
set @sql=@sql+'select fieldname=a.name, tablename=b.name '
set @sql=@sql+'from '+@database+'.dbo.syscolumns a '
set @sql=@sql+'inner join '+@database+'.dbo.sysobjects b on a.id=b.id '
set @sql=@sql+'left outer join '+@database+'.dbo.FileFormat c on c.fieldname=a.name '
set @sql=@sql+'where b.type=''U'' and substring(b.name,len('''+@tablename+''')+2,2)=''DB'' and '
set @sql=@sql+'	not (transformation<>''C'' and b.name='''+@tablename+'_DB'') and '
set @sql=@sql+'	a.name<>''cis_id'' '
set @sql=@sql+'order by a.name '
exec (@sql)

-- create table of table names and asign table id
create table tablenames (tablename varchar(100),table_id int identity(1,1))
insert into tablenames (tablename,table_id)
	select tablename
	from cview group by tablename

-- merge table and variable id information
select a.tablename,fieldname,table_id=cast(table_id as varchar(10)),id_num
	into newcview
	from cview a left join tablenames b on a.tablename=b.tablename

-- build sql code
set @maxfield=(select max(id_num) from cview)
set @maxtable=(select max(table_id) from tablenames)

while (@i<@maxfield) begin
	set @i=@i+1
	if (@i<@maxfield) set @fieldlist=@fieldlist+'a'+(select table_id+'.'+fieldname
			from newcview where id_num=@i)+','
	else set @fieldlist=@fieldlist+'a'+(select table_id+'.'+fieldname
			from newcview where id_num=@i)
	end

set @i=0
while (@i<@maxtable) begin
	set @i=@i+1
	if @i=1 set @tablelist=@tablelist+(select tablename+' a'+cast(table_id as varchar(10)) from tablenames where table_id=@i)
	else set @tablelist=@tablelist+' left join '+
		(select tablename+' a'+cast(table_id as varchar(10))+
		' on a1.cis_id=a'+cast(table_id as varchar(10))+'.cis_id'
		from tablenames where table_id=@i)
	end

print @tablelist

-- store sql statement in a table

if exists (select name from sysobjects where name='fieldlist' and type ='U')
	drop table fieldlist
select @fieldlist as fieldlist into fieldlist

if exists (select name from sysobjects where name='tablelist' and type ='U')
	drop table tablelist
select @tablelist as tablelist into tablelist

-- wrap sql in dynamic sql so that sp_executesql can be used to run the sql in the selected database
select @sql = 'declare @test nvarchar(4000) '
select @sql = @sql + 'set @test='''' '
select @sql = @sql + 'select @test = ''create view ''+tablename+ ''_DB_VIEW_ALL as select a1.cis_id,'' from tablename '
select @sql = @sql + 'select @test = @test+fieldlist from fieldlist '
select @sql = @sql + 'select @test = @test+'' from '' + tablelist from tablelist '
select @sql = @sql + 'exec ' + databasename + '.dbo.sp_executesql @test print @test' from databasename 

print @sql
exec sp_executesql @sql

drop table cview
drop table tablenames
drop table newcview
drop table databasename
drop table tablename
drop table fieldlist
drop table tablelist

go