Find a stored procedure by name

Question

Can SQL Server Management Studio be found by name or by name in a stored procedure? (in the context of an active database)

thanks for the help

Answer 1

You can use:

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

if you need a code that you can see 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%' 

Change update:

you can also use the standard version of ansi

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

Answer 2

Assuming you are in the Object Information section ( F7 ), which displays a list of stored procedures, click the Filters button and enter a name (or partial name).

Answer 3

This will work for tables and views (among other things), and not just for 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 a schema name that will be useful in any non-trivial database (for example, where you need a query to find a stored procedure by name).

Answer 4

A very neat trick I came across an attempt to inject some SQL, in the object explorer in the search box just use your percentage characters and it will search ALL stored procedures, functions, views, tables, schemas, indexes ... I'm tired of thinking more: )

search pattern

Answer 5

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 also search inside the body of a stored procedure.