Find stored procedure by name

问题:

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

回答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%'

回答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).

回答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).

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

回答5:

Very neat trick I stumble upon trying some SQL injection, in object explorer in the search box just use your percentage characters, and this will search EVERYTHING stored procedures, functions, views, tables, schema, indexes...I tired of thinking of more :)

Search Pattern

回答6:

When I have a Store Procedure name, and do not know which database it belongs to, I use the following -

Use [master]
GO

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
--Status 48 (mirrored db)
SELECT name FROM MASTER.dbo.sysdatabases WHERE STATUS NOT LIKE 48 AND name NOT IN ('master','model','msdb','tempdb','distribution')  

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'SELECT * FROM ['+@dbname+'].INFORMATION_SCHEMA.ROUTINES  WHERE [ROUTINE_NAME] LIKE ''%name_of_proc%'''+';'
print @statement

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor