29 Nov 2010 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:
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.