How to find which stored procs and UDFs reference a column

How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.

Thanks.

Which version of sql server are you using ?|||SQL Server 2000.|||You can try the sql server specific system table "sysdepends". Look it up in Books Online. I am not aware of an INFORMATION SCHEMA view that will help you in determining such references.|||

The sysdepends table seems helpful, but it only gives the table, not the columns. Here's the query I used:

SELECT sp.name as StoredProc, dep.name AS DependentObject
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id AND sp.xtype = 'P'
INNER JOIN sysobjects dep ON dep.id = sd.depid

Any further clues to linking the columns? (I suppose I could query for occurrences of the column name within the stored procedures depending on the column's table, but that's an approximation, since column names in the procedure text could be from a different table.)

|||

The depnumber column in sysdepends should give you the column id of the table that the procedure/function references. You can modify your query above to also join the depnumber column in sysdepends with the id column in syscolumns to get the name of the column.

Let me know if that works for you

|||

Seems I'm close, but the following query doesn't always yield complete results:

SELECT sp.name as StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

I'm trying to verify it as follows:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

Thank you for your consideration.

|||

If you are using dynamic sql to create the procedures/functions then sql server may not be able to track the references. Also, if you are using deferred name resolution [i.e create the procedure first and then create the table that is being referenced by the procedure] then sql server will not be able to track the references.

By any chance, is this the case?

|||

There are many cases under which the dependency tracking in SQL Server will not work. Here are the common cases:

1. Creation of dependent stored procedures in out-of-order fashion for example

2. Use of temporary tables or table variables in SELECT/DML statements will defer compilation of the statement so there will be no dependency information saved

3. Use of dynamic SQL

4. In case of permanent tables, if the object doesn't exist then deferred name resolution / compilation will kick-in at run-time and there will be no dependency information for this case also

Please take a look at the blog entry below for some queries on how to do this in SQL Server 2005 assuming that the dependency information is present.

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

So given the various restrictions it is probably unlikely that you have dependency information for most objects other than references, schema bound objects etc. You will have to mostly maintain this information manually or use your source code control system to scan your scripts assuming you use some keywords mechanism to tag scripts for example.

|||

I take it back -- after carefully comparing results, it looks like it's working great. Thanks so much for all your help!!

SELECT sp.name AS StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

Confirming/cross-checking query:

SELECT obj.[name], cmt.[text]
FROM syscomments cmt
INNER JOIN sysobjects obj ON obj.id = cmt.id
WHERE text like '%MyTable%'
AND [text] LIKE '%MyColumn%'

Reverse query:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

|||I've discovered that the sysdepends table is not reliable/complete unless the stored procedure is created (or recreated) after the table(s) that it references. [Or at least, I can say that querying for columns referenced by a proc may not show up any, but then if the proc is dropped and recreated, the same query can now show its column dependencies.]|||

As Umachandar pointed out, there are instances where sysdepends will not work correctly.

|||Recreating SPs or altering SPs are costly operations since they will block access to the SP metadata. So you have to probably schedule this during a maintenance window. And if you don't have a mechanism to know which ones to alter then you will have to do this for all the SPs and this can take considerable time depending on the number of SPs/UDFs in the database. So there are several issues if you rely completely on the server dependency information.