Find stored procedures that reference a table column

We recently moved away from SQL Server replication and the database still has all of the rowguid columns and their associated indexes and constraints in it. We wanted to gain all of that disk space back so we went ahead with scripting out the delete of the indexes, constraints, and columns.

One thing though is that there are many stored procedures in use and we needed to make sure none of these referenced the rowguid column.  The stored procedures really should not have used the rowguid column at all since it is solely created and used for SQL Replication, but you never know so we needed to confirm.

The below script is what I created to search all of the stored procedures in the database for the rowguid column.  It could easily be modified to find any information within the stored procedures of a database.  (Note: One thing to watch out for is encrypted stored procedures.  This wouldn’t find anything in those so you would need to handle it through another method.)

SELECT p.name, c.text FROM syscomments c
JOIN sys.procedures p ON p.object_id=c.id
WHERE c.text LIKE '%rowguid%'
ORDER BY p.name