sysdepends缺失了一些stored procedure或表的问题

出自thomaslarock的一片文章，写的非常好，完美的解决了这个问题。有时间一定要翻译一下。

以下是原文：

WHY YOU CAN’T TRUST SYSDEPENDS

One thing I learned early on in my former life as a production DBA was the old motto “trust but verify”. Such is the case when it comes to relying on system information in order for your tools to work properly. An example that comes to mind immediately is the use of the sysdepends system view. I would rely on this view in order to tell me what objects were dependent upon other objects as it was handy when doing migrations and schema updates. The only problem I had with sysdepends is that IT LIES.

Well, OK, *it* doesn’t lie, but it also doesn’t always tell you the truth. Let me give you a quick example why.

Using AdventureWorks, run the following query and note the number of rows returned:

SELECT DISTINCT OBJECT_NAME(id) as [Parent_Name],
 OBJECT_NAME(depid) as [Child_Name] ,
 OBJECT_DEFINITION(id) as [Parent_Definition]
FROM sys.sysdepends

In my copy of AdventureWorks I got back 232 rows. Next, create a stored procedure with a simple SELECT statement on a table that does not exist yet.

CREATE PROCEDURE table_doesnt_exist_yet
AS

SELECT * FROM NoTableYet
GO

The command will complete successfully so we now have a procedure that is dependent upon an object that doesn’t exist. What do we expect to see in sysdepends? Should there be a row included for this scenario? Run the first statement again and you will see that the number of rows has not changed. You can even run the proc with the following:

EXEC [dbo].[table_doesnt_exist_yet]

And get back an error message. OK, now let’s create the table:

CREATE TABLE NoTableYet
 (foo nchar(10) NULL)
GO

Now we have the proc, the table and executing the proc returns a result (no rows) and not an error. But what about sysdepends? Go back and run that first statement and you will see that no row has been inserted yet. That means that every time you have a script that creates a proc BEFORE the dependent tables you will be missing rows from sysdepends. Now, think quick: how many times have you had developers hand you scripts with the object creations done in whatever order they please? I’ve seen a lot of scripts passed around where a developer will say “run it three times in order for all the warnings and errors to go away”. Just shoddy work and it has an effect on people and tools that rely on things being created in a specific order.

Now, let’s drop the procedure, then recreate:

DROP PROCEDURE table_doesnt_exist_yet
GO

CREATE PROCEDURE table_doesnt_exist_yet
AS

SELECT * FROM NoTableYet
GO

Now go back and run the first statement again and you will find that the extra row does exist in sysdepends. There are two ways I can think of to avoid missing out on dependencies being created. The first is to painstakingly review each and every script to make sure the objects are created in the proper order. The second is to scrub the syscomments table to find a particular search phrase. Neither way seems very pleasant I must admit.

Ready for the fun part? Check out the column named ‘selall’ in sysdepends. Let’s assume that you have all of your dependent objects listed in sysdepends. The ‘selall’ column will tell you if the object is dependent upon a ‘SELECT *’ being issued against the child object:

SELECT DISTINCT OBJECT_NAME(id) as [Parent_Name],
 OBJECT_NAME(depid) as [Child_Name] ,
 OBJECT_DEFINITION(id) as [Parent_Definition]
FROM sys.sysdepends
WHERE selall = 1

That’s the fun part. When done properly you can quickly review your databases to see what will break should you decide to add or remove a column from a table. You could even use PBM to build a policy around this idea.

If I was still a production DBA I would be thinking about doing something along those lines. That way I could help ensure that my environment stays stable as changes are deployed.

以下摘录了一个评论的内容，通过此评论内容和此问的介绍结合可以很好的解决问题。

John Sansom • 4 years ago

Oh that's sneaky that sysdepends does not record the procs dependence on the table in this case but totally makes sense given that well, the object does not exist yet :-)

It would be nice if there was a way for Administrators to invoke the revalidation/re-qualification of dependencies, without the need to drop and re-create the stored procedure.

******************************************************************************************************

Thomas LaRock  John Sansom  • 4 years ago

John,

I think there might be a way to do this, I seem to recall a way to 'refresh' without rebuilding. I'll see if I can dig up some old scripts.

******************************************************************************************************

@John Sansom and @ThomasLaRock, you can use sp_refreshsqlmodule to update the dependencies without having to 

alter the SP.

Here's a script to help run refreshsqlmodule on your database:

https://gist.github.com/KyleMi...

	DECLARE @str NVARCHAR(MAX) SELECT @str = N''

	SELECT @str = @str + 'exec sp_refreshsqlmodule '''
        + SCHEMA_NAME([schema_id]) + '.' + OBJECT_NAME([object_id])
        + '''' + CHAR(13) + CHAR(10)
	FROM  sys.objects o
	WHERE [type] IN ('FN', 'IF', 'P', 'TF', 'V') 
	AND is_ms_shipped = 0 
	AND NOT EXISTS (SELECT 1 FROM sys.sql_modules m
            WHERE m.[object_id] = o.[object_id] 
            AND m.[definition] like '%WITH SCHEMABINDING%')
	ORDER BY OBJECT_NAME([object_id])

	PRINT @Str
	EXEC  sp_executesql @str

原文链接：http://thomaslarock.com/2010/11/why-you-cant-trust-sysdepends/