Find stored procedure by name

Question

Is there any way I can find in SQL Server Management Studio stored procedure by name or by part of the name? (on active database context)

Thanks for help

Solution 1

You can use:

select * 
from 
   sys.procedures 
where 
   name like '%name_of_proc%'

if you need the code you can look in the syscomments table

select text 
from 
    syscomments c
    inner join sys.procedures p on p.object_id = c.object_id
where 
    p.name like '%name_of_proc%'

Edit Update:

you can can also use the ansi standard version

SELECT * 
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_NAME LIKE '%name_of_proc%'

Solution 2

Assuming you're in the Object Explorer Details (F7) showing the list of Stored Procedures, click the Filters button and enter the name (or partial name).

Solution 3

This will work for tables and views (among other things) as well, not just sprocs:

SELECT
    '[' + s.name + '].[' + o.Name + ']',
    o.type_desc
FROM
    sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE
    o.name = 'CreateAllTheThings' -- if you are certain of the exact name
    OR o.name LIKE '%CreateAllThe%' -- if you are not so certain

It also gives you the schema name which will be useful in any non-trivial database (e.g. one where you need a query to find a stored procedure by name).

Solution 4

You can use this query:

SELECT 
    ROUTINE_CATALOG AS DatabaseName ,
    ROUTINE_SCHEMA AS SchemaName,
    SPECIFIC_NAME AS SPName ,
    ROUTINE_DEFINITION AS SPBody ,
    CREATED AS CreatedDate,
    LAST_ALTERED AS LastModificationDate
FROM INFORMATION_SCHEMA.ROUTINES
WHERE 
    (ROUTINE_DEFINITION LIKE '%%')
    AND 
    (ROUTINE_TYPE='PROCEDURE')
    AND
    (SPECIFIC_NAME LIKE '%AssessmentToolDegreeDel')

As you can see, you can do search inside the body of Stored Procedure also.

Solution 5

For SQL Server version 9.0 (2005), you can use the code below:

select * 
from 
syscomments c
inner join sys.procedures p on p.object_id = c.id
where 
p.name like '%usp_ConnectionsCount%';

View more solutions