April 16, 2008 at 8:39 am
Does anyone have a slick way with a stored procedure to find if a view or SP is using a table name?
April 16, 2008 at 8:59 am
There isn't a fool-proof method without doing some complex parsing or using a third-party tool but you can try a couple of things:
Use the procedure sp_depends to get a list of objects that depend on a given stored procedure and then check if your table exists in that list. Problem is that sp_depends isn't necessarily accurate.
Use the dmv sys.sql_dependencies but, again, the same restrictions apply to this as to sp_depends.
Or, use a query like :
select object_name(m.object_id) object
from sys.sql_modules m
join sys.objects o on o.object_id = m.object_id
where definition like '%my_table%' --enter your table name here
and o.type in ('P','V')
The problem with this query is that you'll return an procedures or views that have the table name in any comments. And you'll also return procedures or views that contain the string "my_table" in it. So "this_is_my_table" will result in a match, when you didn't necessarily want it to.
Other than that,if you absolutely need 100% accuracy, you're stuck with having to parse the text (which I wouldn't advise you do in T-SQL) or you resort to a third-party tool.
April 16, 2008 at 9:51 am
Maybe it's worth to mention that sp_depends CAN work properly if there are two conditions met.
1. All dependent objects are created before a procedure is created
2. Dependent objects are not used in dynamic SQL.
Here's simple script showing first behavior:
create table tab1 (a int, b int)
go
create procedure p1
as
select * from tab1
go
--here's deferred name resolution - table name created after procedure.
create procedure p2
as
select * from tab2
go
create table tab2(x int, y int)
go
exec sp_depends 'p1'
--there are no dependencies discovered as procedure was created BEFORE table.
exec sp_depends 'p2'
go
drop table tab1
drop table tab2
drop procedure p1
drop procedure p2
Regards
Piotr
...and your only reply is slàinte mhath
April 16, 2008 at 11:09 am
Thank you all, Worked out great, thank you again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply