Find which objects are using my table column in the given schema

Question

Lets say I have a table 'MyTable' having a column 'MyColumn'. I want to find out which procedures, triggers, views, functions are using that column. Currently I have this script which gets most objects with the given name. Please let me know if this script returns all the objects using the given column. Is there in-built function for this? Thanks

CREATE procedure [dbo].[Find] (@q varchar(255))  
as  

BEGIN  
(SELECT DISTINCT  
    o.name AS Object_Name,o.type_desc AS 'Type', m.definition AS 'Complete Name'  
    FROM sys.sql_modules        m   
        INNER JOIN sys.objects  o ON m.object_id=o.object_id  
    WHERE m.definition Like '%'+@q+'%')  

UNION ALL  

(SELECT t.name AS Object_Name,'Table' AS 'Type', c.name AS 'Complete Name'  
FROM sys.tables AS t  
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID  
WHERE c.name LIKE '%'+@q+'%')  

UNION ALL  

SELECT OBJECT_NAME(id) AS Object_Name, 'Trigger' AS 'Type',text AS 'Complete Name'  
FROM syscomments  
WHERE [text] LIKE '%'+@q+'%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1  

ORDER BY 2,1,3;  
EnD  

Answer 1

Best is to use FREE tool from Red-Gate SQL Search - quickly find SQL in SSMS. You can even profile it while it is running to get the sql code and use that.

Also, refer to Aaron's post on : Keeping sysdepends up to date in SQL Server 2008.

Phil Factor has a great writeup on finding object dependency at Exploring your database schema with SQL

Answer 2

Take a look at this, replacing 'TableName' with the table your column is in, and 'ColumnNumber' with whatever the column number is that is returned in the first query:

SELECT
    *
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID('TableName')

SELECT
    OBJECT_NAME(object_id),
    *
FROM
    sys.sql_dependencies AS sd
WHERE
    sd.referenced_major_id = OBJECT_ID('TableName')
    AND sd.referenced_minor_id = ColumnNumber

Please tell me if this doesn't make sense. :)