SQL Server 2008 query to search all stored procedure for a specific parameter name

I have a column name varchar(8)....I want to change the si开发者_C百科ze to 20 ...and have almost about 100 stored procedures .

I need to change the size of @name in all stored procedures that I have passed name as a parameter.

You can right click on the table and select View Dependancies – this will give you a list of all dependent objects you need to consider for updating.

If you want to search through all columns another tool you can try apart from SQL Search is ApexSQL Search which is also free.

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

To look for code in the database you can search sys.sql_modules (see example) or use something like Red Gate SQL Search which is free.

You can also use SQL:

SELECT
   OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id)
FROM
   sys.sql_modules
WHERE
   definition like '%whatever%'

Note: syscomments is legacy and splits the definition into nvarchar 4000 chunks thus risking not finding what you want. The same applies to INFORMATION_SCHEMA.ROUTINES

Edit: I misunderstood

To find parameters, query sys.parameters

To see both the Param and SP name:

SELECT
   o.name as NAME, p.name as parameter
FROM
   sys.parameters p (NOLOCK)
   INNER JOIN sys.objects o (NOLOCK) ON p.object_id = o.object_id 
WHERE
   p.name like '%afe%'

I'd would use Mangement Studio to generate scripts for all your stored procedures, and then use the text editor to do a simple find or find & verify & replace to make changes, and then just run the script (the whole script) again.