Sql Server Programming :: Finding column references in sprocs


	Finding column references in sprocs I had a couple of questions, but one was answered when I used the search function here...namely, trying to find all tables containing a certain columnname. So, my only question here is: how do I go about searching through at least 100 sprocs to find all references to either a particular columnname or a particular table? I usually resort to the following: select distinct object_name(id) from syscomments where text like ('%columnname%'); -- Cheers, Stefan Delmarco \| SQL Server MVP \| http://www.fotia.co.uk/ -----------------------------------------------Reply----------------------------------------------- Hi see my blog http://oneplace4sql.blogspot.com/ Regards VT Knowledge is power, share it.... http://oneplace4sql.blogspot.com/ "AlBruAn" <albr@hotmail.com.(donotspam)> wrote in message news:BB76C4ED-9BB0-4587-9DB5-EB1FD2823BB7@microsoft.com... - Hide quoted text - >I had a couple of questions, but one was answered when I used the search > function here...namely, trying to find all tables containing a certain > columnname. So, my only question here is: how do I go about searching > through at least 100 sprocs to find all references to either a particular > columnname or a particular table? >I had a couple of questions, but one was answered when I used the search > function here...namely, trying to find all tables containing a certain > columnname. So, my only question here is: how do I go about searching > through at least 100 sprocs to find all references to either a particular > columnname or a particular table? It's always useful to indicate what version of SQL Server you are using, since often there is a better option on one version that is not as good or not applicable in another. If you are on SQL Server 2005: SELECT name FROM sys.procedures WHERE OBJECT_DEFINITION([object_id]) LIKE '%column_or_table_name%'; If you are on SQL Server 2000: SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%column_or_table_name%' GROUP BY ROUTINE_NAME; Note that in SQL Server 2000 there is a limitation here if you have any stored procedures > 4000 characters that column_or_table_name may straddle the boundary between rows (since the text is stored in blocks and not all in one piece in this case). -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006