June 1, 2006 at 1:32 pm
Is it possible to force the creation of a view even if it calls an unknow element (i.e. another view that doesn't exist) using T-SQL? I am trying to create a Script to recreate all my tables, views and stored procedures. I can use the Generate Script task to generate a script to recreate my views, but I have many that reference other views. Since they are created in alphabetical order, I am often trying to create a view that references views farther down in the list that haven't been created yet, so they fail.
June 2, 2006 at 8:35 am
i don't know if this will help you;
i create my scripts in hierarchy order, so that items that depend on other items are created AFTER they are added to the db.
i use the built in stored proc, which works fine unless you have circular dependancies (tableA references Table B, which references Table C, which References TableA)
i use something like this to get my DDL statements:
create table #hierarchy (ObjectType int,ObjectName varchar(50),Owner varchar(50),Seq int)
insert into #hierarchy(ObjectType,ObjectName,Owner,Seq)
exec sp_MSdependencies
-- functions:
SELECT ObjectName from #hierarchy where ObjectType = 1 order by seq
select 'exec sp_helptext ' + ObjectName from #hierarchy where ObjectType = 1 order by seq
--views
SELECT ObjectName from #hierarchy where ObjectType = 4 order by seq
select 'exec sp_helptext ' + ObjectName from #hierarchy where ObjectType = 4 order by seq
--tables -- this is my own function which generates the table DDL
SELECT ObjectName from #hierarchy where ObjectType = 8 order by seq
select 'select dbo.fn_tblsql ' + ObjectName from #hierarchy where ObjectType = 8 order by seq
--procedures
SELECT ObjectName from #hierarchy where ObjectType = 16 order by seq
select 'exec sp_helptext ' + ObjectName from #hierarchy where ObjectType = 16 order by seq
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply