RefreshView for all dependcies
- I have this answer in 2005 but can't seem to get it to work in 2000.th basic refresh view is OK - I need to get the dependency list also? The 2000 help pages don't seem to list anything like above about dependencies.
-
There are no reliable (100%) way of getting that information in sql server.
The best you can do is scan the syscomments table and try to find them by yourself. I guess you could recompile all hits you find... even if you recompile more objects than needed, at least you get 'em all.
-
So its psooible in 2005 but not 2000?
-
Check out the sysdepends table in 2000.
As I said it is not reliable (you could miss quite a few objects). I have no idea if it is more reiable on 2005. That's why I suggested the syscomments scan on the text column (still not 100% but very close).
-
Thanks - How does SQL server return the dependencies list if you try and delete the object?
-
I guess it deletes the corresponding rows in the dependancies table when you delete an object. But the problem comes when you recreate objects, all dependancies are deleted, and only some are recreated. So after a while you start to miss a lot of 'em. I never got a full proof workaround for this one... and I don't think anyone exists
! -
OK - Ta
-
in SQL 2000 you can run sp_MSdependencies with no parameters to get the list of objects in a order of hierarchy, but it's not perfect if you have a circular fk structure, like TABLE A referenced TABLE B, but TABLE B also references Table A (typical when you are realating users or entities to each item
Of course the results are based on foreign keys and sysdepends, so if a dependancy is missing, it might be incorrect.
so most of the time, the list of items in that resultset is the order in which to create the objects.
I created this because of those rare circular references, this might help as well, as far as getting the hierarchy of tables for foreign keys; This skips around Functions and procedures:
SET NOCOUNT ON
DECLARE
@Level INT,
@MovedToNewLevel INT,
@sql varchar(1024),
@err varchar(125),
@LastBatch int
CREATE TABLE #Hierarchy
(FKLevel INT,
TblName VARCHAR(100),
id Numeric
)
-- Populate the table
INSERT INTO #Hierarchy
select 0 AS 'FKLevel', name as 'TblName',id
FROM sysobjects where xtype='U' and
id not in (select rkeyid from sysforeignkeys) and
id not in (select fkeyid from sysforeignkeys)
INSERT INTO #Hierarchy select 1 AS 'FKLevel', name as 'TblName',id
FROM sysobjects where xtype='U' and
id not in (select id from #Hierarchy) and
id in (select rkeyid from sysforeignkeys) and
id not in (select fkeyid from sysforeignkeys)
INSERT INTO #Hierarchy select 2 AS 'FKLevel', name as 'TblName',id
FROM sysobjects where xtype='U' and
id not in (select id from #Hierarchy) and
id in (select fkeyid from sysforeignkeys)
-- Set the variables
set @Level=2
set @MovedtoNewLevel=1
WHILE @MovedtoNewLevel <> 0
BEGIN
set @LastBatch=@MovedtoNewLevel
set @sql='update #Hierarchy set FKLevel = FKLevel + 1 where FKLevel=' + CAST(@Level as varchar) + ' and'
set @sql=@sql + ' id in (select fkeyid from sysforeignkeys where fkeyid<>rkeyid and'
set @sql=@sql + ' rkeyid in (select id from #Hierarchy where FKLevel=' + CAST(@Level as varchar) + ' ))'
exec(@sql)
SET @MovedtoNewLevel = @@Rowcount
set @err='#ID''s yet to be processed :' + convert(varchar,@MovedtoNewLevel)
--'BANDAID TO WORK AROUND CIRCULAR FK REFERENCES, WHERE EVENTUALLY,
--TABLEA REFERS TO TABLEQ AND TABLEQ REFERS TO TABLEA
if @LastBatch=@MovedtoNewLevel
BEGIN
select top 1 @err= TblName from #Hierarchy WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )
set @err = 'possible Circular FK''s found, arbitrarily changing ' + @err + ' to break circular reference.'
--RAISERROR (@err,1,1)
set rowcount 1
UPDATE #Hierarchy SET FKLevel = FKLevel - 1
WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )
set rowcount 0
END
--RAISERROR(@ERR,1,1)
SELECT @Level = @Level + 1
End
select * from #Hierarchy order by FKLEVEL
HTH
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! -
Thanks Lowell - I'm not to sure I need this solution - and since a beginner at this I don't reall understand what is going on there!
Having had a little think about the problem - Lets say I have views A, B, C , D , E
Where B is a view of A, C is a view of B etc etc
Lets say i changed A - I would want to refresh B - E if I looped thru B-E How would I ensure they were refreshed in the correct order - ie if E was freshed before B - then I am assuming the change in A wouldn't be reflcted in E?
My solution would be to loop thru B- E refreshing each 4 times - thereby ensuring all objects coorectly refreshed. ie if I was unlucky and did them purely in the wrong order n objects would take n loops to refresh them all correctly.
At the moment all I have is a view containing the view names that I want to refresh - how do I execute a loop n times thru the n objects doing a refeshview on each one each time?
Or is this just a crazy solution - at the moment I can't see another which wouldn't involve much manual intervention!
Cheers
-
i was originally going to post about the wonders of sp_recompile, but it didn't work as expected. I thought it recompiled dependant objects as well, causing a chain reaction, but it does not.
there's two options i can think of;
if you change your views to use schemabinding, that might do it automatically;
i would personally just be lazy and recompile all objects after i changed a table or view; it depends on your database as to whether that would slow things down when an object gets recompiled
Here's how I'd create a cursor and recompile all objects:
declare
@isql varchar(2000),
@objname varchar(64)
declare c1 cursor for select name from sysobjects where xtype in('FN','TF','V','P') order by xtype
open c1
fetch next from c1 into @objname
While @@fetch_status <> -1
begin
select @isql = 'sp_recompile @objname
select @isql = replace(@isql,'@objname',@objname)
print @isql
exec(@isql)
fetch next from c1 into @objname
end
close c1
deallocate c1
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! -
Thanks Lowell have got that cursor thru the view names to work - only 1 problem - views which have schemabinding come back as invalid object names - I can safely ignore these views howver - how would I exclude these form the SELECT of viewnames?
Ta
-
yould want to use something like this:OBJECTPROPERTY(OBJECT_ID(OBJNAME), 'IsSchemaBound')=0 means false, and OBJECTPROPERTY(OBJECT_ID(OBJNAME), 'IsSchemaBound')=1 means true
so i'd use something like this for the cursor:
select name from sysobjects where xtype='V' and OBJECTPROPERTY(OBJECT_ID(NAME), 'IsSchemaBound')=0
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! -
Thanks that works perfectly.
-
Sorry spoke to soon - errors out if the views are tried to refresh in the wrong order. - ie if view D is refreshed before B then get an error.
-
Have you figured out a methodology to determine the order in which the views should be refreshed?
I have a similar problem, but it relates to both refreshing and creating new views. I have year specific views (related to a tax year); therefore, I would have A_2005 is built upon B_2005 which is built upon C_2005. Next year, I'll have to create a script for each view, but change the years from 2005 to 2006 (A_2006 is built upon B_2006...). Since I have hundreds and hundreds of these dependent, year specific views, I was trying to automatically generate the create SQL scripts in the correct order. I could create the text from the 2005 tables and then with any text editor change the 2005 values to 2006.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply