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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Option 1  In SSMS go to View  gt  Object Explorer Details or press F7  Use the Search box  Finally in the displayed list right click and select Synchronize to find the object in the Object Explorer tree     Option 2  Install an Add-On like dbForge Search  Right click on the displayed list and select Find in Object Explorer

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Find stored procedure by name

Examples related to sql

Examples related to database

Examples related to sql-server-2005

Examples related to ssms