missing DataBase object dependencies
Hi,
I have a Function it's name is "Function1" syntax of the function is
CREATE FUNCTION Function1()
RETURNS @Temp TABLE(
data varchar(100)
)
AS
BEGIN
insert into @Temp select view1.data from view1 left join view2 on view1.data = view2.data
RETURN
END
when i created this function the view2 was not there and i'm executed 'sp_depends' system stored procedure for finding object dependency, i'm getting only view1 as dependency.
so how can i find these missing dependencies.?
# 1 Re: missing DataBase object dependencies
Q1: Is it for MySQL, Oracle, Sybase, Postgres, Access, ...?
A1: Maybe, it is for SQL Server.
Q2: What are the missing dependencies you are thinking about?
A2: Maybe, it is view2.
Q3: How do you use sp_depends?
A3: Maybe, you use sp_depends Function1 instead of sp_depends view2.
at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_9qlv.asp it is explains that:
An object that references another object is considered dependent on that object. sp_depends determines the dependencies by looking at the sysdepends tableI suppose that if view2 is not created, it does not appear in the sysdepends table, and cannot be displayed by sp_depends, and therefore in your case some dependencies are not shown by sp_depends. But I'm sorry, I don't know how the absence of view2 can be detected with a system procedure.
EDIT:
I have found an answer to your question at http://www.transactsql.com/html/home.html in the FAQ section:
How can I return a list of objects that depend on a stored procedure?
Executing the system stored procedure sp_depends will return the objects that are dependent on the specified stored procedure or you can query the sysdepends system table.
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so2.name = 'STOREDPROCNAME'
Both of the methods above may not return all the objects that depend upon the specified stored procedure due to the fact that the specified stored procedure may not have existed when the object was created or the stored procedure is called as part of a dynamic statement. To find these missing dependencies you can use the syscomments system table and search through the text of a all objects for the specified stored procedure name.
SELECT so.name FROM sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE '%STOREDPROCNAME%'
