May 9, 2018 at 11:10 am
Is there a way to list all of the stored procedures that use a particular database?
May 9, 2018 at 11:24 am
It might not be an exact science (depending on what your database is called), but how about:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2018 at 11:29 am
Works fine. Thanx
May 9, 2018 at 11:32 am
You might also, however, want to consider Redgate's SQL Search: https://www.red-gate.com/dynamic/products/sql-development/sql-search/download
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2018 at 11:39 am
K. Thanx.
May 9, 2018 at 12:21 pm
Here is some SQL I stole that will search across all databases on the server.
DECLARE @sql VARCHAR(8000);
DECLARE @SearchText NVARCHAR(255);
SET @SearchText = 'DwEsIbnrAdtvFact';
DECLARE @Results TABLE
(
[SERVERNAME] VARCHAR(255),
[DBName] VARCHAR(255),
[NAME] VARCHAR(255),
XTYPE VARCHAR(255)
)
;
SELECT @sql =
'SELECT DISTINCT @@SERVERNAME, ''?'' AS [DBName], so.[name], so.xtype
FROM [?].dbo.sysobjects so WITH(NOLOCK)
inner join [?].dbo.syscomments sc on
so.id = sc.id
INNER JOIN [?].sys.databases d ON
d.Name=''?''
AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')
WHERE sc.text like ''%'+ @SearchText + '%'''
INSERT INTO @Results
EXEC sp_MSforeachdb @sql
SELECT *
FROM @Results
ORDER BY DBName, XType, [Name]
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
May 9, 2018 at 2:25 pm
One more place to check is in the base_object_name of sys.synonyms. That might hide the fact that a query is really using a different database.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply