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
This question is related to
sql
database
sql-server-2005
ssms
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.
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%';
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 :)
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).
Option 1: In SSMS go to View > 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
.
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
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).
Source: Stackoverflow.com