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
Best Answer
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