Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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...

>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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc