THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

CREATE PROC SomeTestProc

AS

SELECT dbo.somefuction(1)

GO


now create this function

CREATE FUNCTION somefuction(@id int)

RETURNS int

AS

BEGIN

SELECT @id = 1

RETURN @id

END

Go


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 06, 2008 1:50 PM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

pete said:

What about MSSQL 2000? What would you recommend?

July 28, 2009 10:50 AM

Leave a Comment

(required) 
(required) 
Submit

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 http://sqlservercode.blogspot.com/ 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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement