March 31, 2009 at 5:43 am
Hi folks
I am trying to find which stored procedures reference a table in my database.
However, I run sp_depends and get one record back, whereas a search within sys.syscomments on "WHERE text like '%MySchema.MyTable%'" returns 12 rows.
I'm a little confused - is there something fundamental I'm missing here?
Many thanks
March 31, 2009 at 5:55 am
Update:
I have now found this:
http://www.sqlservercentral.com/scripts/Administration/62801/
sp_depends will fail to display correct information if there have been changes in views / tables /stored procs (dropping / recreating etc).
Seems a bit of a failing though? This would mean that sys.sysdepends does not get updated when a stored proc/view etc gets updated?
April 1, 2009 at 8:56 am
When a new database is created using a script and the script objects are not in dependency order, objects will be created, but the sysdepends table is not inserted/updated. This scenario results in the sysdepends functionality rendered useless.
For example, create new stored procedure results in the following message because a dependent has not been created yet:
Cannot add row to sysdepends for the current stored procedure because it depends on the missing object 'usp????'
I suppose that there are other scenarios where objects are updated even when the sysdepends table was at one time up-to-date but somehow gets out of synchronization with the sql objects.
Resolutions vary, including not using MS SQL in favor of Red-Gate's SQL Dependency Tracker. Red-Gate's tool is a nice diagram UI with drill-down, search and filter to enable you to complete your research.
Dropping and re-Creating each view and programmability object has worked recently, do not attempt this on tables.
[font="Arial"]Clifton G. Collins III[/font]
April 2, 2009 at 5:58 am
Clifton Collins (4/1/2009)
When a new database is created using a script and the script objects are not in dependency order, objects will be created, but the sysdepends table is not inserted/updated. This scenario results in the sysdepends functionality rendered useless.For example, create new stored procedure results in the following message because a dependent has not been created yet:
Cannot add row to sysdepends for the current stored procedure because it depends on the missing object 'usp????'
I suppose that there are other scenarios where objects are updated even when the sysdepends table was at one time up-to-date but somehow gets out of synchronization with the sql objects.
Resolutions vary, including not using MS SQL in favor of Red-Gate's SQL Dependency Tracker. Red-Gate's tool is a nice diagram UI with drill-down, search and filter to enable you to complete your research.
Thanks for the explanation, Clifton
I guess it makes sense - and of course now you've mentioned it I remember seeing the "cannot add row to sysdepends" message on numerous occasions!
Dropping and re-Creating each view and programmability object has worked recently,
Thanks, I might give that a go
do not attempt this on tables.
I hear you!
April 2, 2009 at 6:53 am
I wouldn’t recreate the views. If you recreate the views in the wrong order, you’ll have the same problem again (e.g. if view B is based on view A and you recreate view B and then recreate view A, sysdepends will not show the dependency). Also when you recreate the views, you have to take care about the view’s permissions. Instead of recreating the views, you can use sp_refreshview stored procedure. The code bellow shows you a scenario where recreating the views doesn’t solve the problem and it also shows you how to use sp_refreshview.
--Creating the son view
create view CurrentDate
as
select getdate() as CurrentTimeDate
go
--creating the calling view
create view MyView
as
select CurrentTimeDate from CurrentDate
go
--get correct dependencies
exec sp_depends MyView
--Now I recreate the views
--but since I don't do it
--in the correct order,
--sp_depends will show
--the wrong results
drop view MyView
go
create view MyView
as
select CurrentTimeDate from CurrentDate
go
drop view CurrentDate
go
create view CurrentDate
as
select getdate() as CurrentTimeDate
go
exec sp_depends MyView
--Now I refresh the views.
--the order of the views is not
--important
exec sp_refreshview CurrentDate
exec sp_refreshview MyView
exec sp_depends MyView
go
--clean up
drop view MyView
go
drop view CurrentDate
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2009 at 8:48 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply