Explain Codes LogoExplain Codes Logo

Find stored procedure by name

sql
database-search
sql-server-management-studio
stored-procedures
Nikita BarsukovbyNikita Barsukov·Nov 13, 2024
TLDR

To retrieve the details of a stored procedure named 'YourProcedureName', use:

SELECT * FROM sys.procedures WHERE name = 'YourProcedureName';

Just swap 'YourProcedureName' with the exact name of your target procedure. This quickly fetches the essential information from the sys.procedures catalog within the current database context.

Hunting procedures by partial name

Want to perform a more generalized search? No problem, you can use the LIKE operator along with % wildcard:

SELECT * FROM sys.procedures WHERE name LIKE '%PartialName%';

Just replace %PartialName% with a part of the procedure's name. "SQL-secret: % can pretend to be any set of characters."

Seeking and filtering in SSMS Object Explorer

If you're using SQL Server Management Studio (SSMS), you can make use of the Object Explorer Details by pressing <kbd>F7</kbd>. After the window appears, click on "Filters", and type your search term into the filter textbox to locate stored procedures. "Guess who also needs filtering besides your coffee? Your stored procedures."

Digging through code of stored procedures

At times, it might be necessary to find a procedure by keywords within its code. You'd want to peek into the syscomments table for this:

SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%Keyword%';

This returns the names of those procedures which have the 'Keyword' in their code. "This is how SQL disk jockey sifts through rap lyrics."

Compliant with ANSI standards? We've got you covered!

In case you're working in an environment that prefers ANSI-standard practices, then INFORMATION_SCHEMA.ROUTINES is a great view to use:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE '%PartialName%';

This approach is like a trusty old pickup truck. Not the fastest ride, but gets the job done reliably on different SQL roads.

dbForge Add-On: Because we like to spoil you!

For an even more powerful search experience, consider installing the dbForge Search Add-On in SSMS. It's like having an intelligent personal assistant, one that integrates seamlessly into your environment, guiding you through database objects.

Sync up Object Explorer for optimized searching

In SSMS, you can synchronize the Object Explorer tree with the active database context. This aligns the search scope with a selected database, making your search more competent and your results cleaner. "Remember to sync, or search in a blink."

Returning procedure name and type

A query that returns both the procedure name and its type might be just what you need:

SELECT SPECIFIC_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE '%PartialName%';

These results help you differentiate your functions from procedures and even categorize your result set more efficiently.

Filtering - A trick up the sleeve for navigation

Dread scrolling through a sea of procedures? Use the Filters button in Object Explorer Details. This efficient tool allows you to quickly apply search criteria and wade your way easily through the vastness.