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.
Code that breaks by altering a table due to the code running “select *” is incredibly bad code, and it is actually just as well to have it break sooner than later IMO.
All code should reference columns by name, not index.
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.
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.
@johnsansom:disqus and @ThomasLaRock:disqus, you can use sp_refreshsqlmodule to update the dependencies with having to alter the SP.
Here’s a script to help run refreshsqlmodule on your database:
https://gist.github.com/KyleMit/a94288a339ade6529a65
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
Thanks Kyle! Much appreciated!
This is the most common thing that happens when an object is recreated. You must be getting the error similar to “Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object” when you compile a store proc which depends on another. Best way to avoid this is to make sure that all the procs are compiled twice on PROD during release.