Identify column-level dependencies between databases

Question

Is there a way to identify column-level dependencies within and between databases? I'd like to generate a report of all columns in a database that are unused by anything (views, procs, UDFs).

e.g.

In database 'DB1', there is a table with a column called 'col1'. How do I determine if 'col1' is being used by procs, views or UDFs in either database 'DB1' or a second database 'DB2'?

Thank you

Answer 1

If it's only col1, db1, and db2, you can script out the objects in db1 and db2 and search for references to col1.

Answer 2

You can try using:

SELECT OBJECT_NAME(m.object_id), m.*
  FROM SYS.SQL_MODULES m
 WHERE m.definition LIKE N'%my_column_name%'

SYSCOMMENTS and INFORMATION_SCHEMA.routines have nvarchar(4000) columns. So if "my_column_name" is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines, but ROUTINES truncates.

But that won't be any help for SELECT * situations, because the column name won't be in text.