THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Do you depend on sp_depends (no pun intended)

I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

First create this proc



SELECT dbo.somefuction(1)


now create this function

CREATE FUNCTION somefuction(@id int)




SELECT @id = 1




now run this

sp_depends 'somefuction'

result: Object does not reference any object, and no objects reference it.


Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT specific_name,*

FROM information_schema.routines

WHERE object_definition(object_id(specific_name)) LIKE '%somefuction%'

AND routine_type = 'procedure'



BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character

Published Tuesday, May 6, 2008 1:50 PM by Denis Gobo
Filed under: ,



pete said:

What about MSSQL 2000? What would you recommend?

July 28, 2009 10:50 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement