March Madness – SQL Server System Tables – sysdepends

Whenever someone asks a seasoned DBA a question their answer is almost always “it depends”. Today we are going to talk about sysdepends, one of the system tables which may or may not have the data you want. And why would that be true? Well, because “it depends” on how you deploy your code!

Before we get started please note that this system table will be removed from a future version of SQL Server.

What is this table for?

This system table is scoped at the database level (not the instance level), so be mindful of that when you are using sysdepends. Run the following code:

SELECT *
FROM sysdepends

Now, the BOL defines this table as:

“Contains dependency information between objects (views, procedures, and triggers) in the database, and the objects (tables, views, and procedures) that are contained in their definition.”

Well now, that sounds like we have a place to go should we ever want to know what objects are dependent on others. Say we wanted to know which tables are referenced by stored procedures, or by views, or even functions. This might be a good thing to know about should you ever think about making a change to a table, perhaps by altering a column. With sysdepends you should be able to find out which objects would be affected by such a change, right?

It depends!

Why should you care?

Because sysdepends lies, that’s why. Here’s the problem: I can create objects out of order. Have a look at this code:

CREATE PROCEDURE table_doesnt_exist_yet
AS

SELECT * FROM NoTableYet
GO

No problems there, except that the table doesn’t exist yet! As I outline in my blog post above, because I can create objects out of order in this manner then I cannot rely on sysdepends to give me 100% accurate information.

That’s the bad news. The good news is that tomorrow I will show you how you can get the details you need from a different system table.

And here is more good news for you: the selall column. This column tells you if the object is using a ‘SELECT *’ in its definition. So, for the objects that do get loaded properly into sysdepends you have a way of finding those that are using a ‘SELECT *’, which is a good thing.

SELECT *
FROM sysdepends
WHERE selall = 1

Where else is this information?

Remember how I said this system table will be removed from a future version of SQL Server? Good, then start using the sys.sql_expression_dependencies object catalog view. Here’s one good reason why: linked servers.

See, if I create a stored procedure that relies on a table across a linked server then I will not see any addition rows in sysdepends. Go ahead and see this for yourself. Go to AdventureWorks and run the following:

SELECT COUNT(*)
FROM sysdepends

Also note the number of rows from:

SELECT COUNT(*)
FROM sys.sql_expression_dependencies

Now (assuming you have a linked server already created), create a stored procedure that goes across a linked server. In my case I simply went from one instance to another on my laptop (from the named instance BACON to the SQLEXPRESS instance) as follows:

CREATE PROCEDURE tstProc
AS
SELECT DepartmentID
FROM SQLEXPRESS.AdventureWorks.HumanResources.Department
GO

Now, go back and get a count of the rows from sysdepends and sys.sql_expression_dependencies. You should see that sysdepends has nto changed, but sys.sql_expression_dependencies has changed, increased by exactly one row. And in my case I can see this easily by running:

SELECT *
FROM sys.sql_expression_dependencies
WHERE referenced_server_name IS NOT NULL

But the very simple fact remains that you cannot trust on sysdepends to give you back 100% accuracy on the object dependencies. Please be mindful of this when you are trying to piece together information about what changes will impact objects in your environment.