RefreshView for all dependcies

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 9, 2006 at 1:40 pm

  #670926

  yes; my original post about sp_MSdependencies is the solution;

  create table #Hierarchy (

  objectType    int,

  objectName    varchar(50),

  objectOwner   varchar(50),

  CreationOrder int,

  IsSchemaBound int)

  insert into #Hierarchy (objectType,objectName,objectOwner,CreationOrder)

  EXEC sp_msdependencies @intrans = 1

  select * from #Hierarchy

  update #Hierarchy set IsSchemaBound =  OBJECTPROPERTY(OBJECT_ID(objectName), 'IsSchemaBound') where objectType=4 --views!

  select * from #Hierarchy where objectType=4 and IsSchemaBound=0 order by CreationOrder

  so you use this for your cursor instead:

  select objectName from #Hierarchy where objectType=4 and IsSchemaBound=0 order by CreationOrder

  to generate the scripts in the correct order:

  SET NOCOUNT ON

  SELECT 'EXEC SP_HELPTEXT ' + objectName from #Hierarchy where objectType=4 and IsSchemaBound=0 order by CreationOrder

  run the results, and that creates the non-schema bound scripts in the right order for the views in question.

  If it's not obvious, you could do the same for tables, procedures or functions in the same way.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!