CODEWITHSUNDEEP
sql-server
Sandeep
Sandeep

Reputation: 105

Get all stored procedures where table is being used in SQL Server

I need the query or code in SQL Server to get all the stored procedures in which table is being used in the server (need all stored procedures in all databases on that server).

SELECT * 
FROM sys.procedures 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%[dbo].[Batch]%.

Above query will give in the current database but I need the query to get from all the databases in that server.

Thanks. Sandeep

Upvotes: 4

Views: 13801

Answers (3)

Sandeep
Sandeep

Reputation: 105

Thanks for the help. I got that below query will give all Stored Procedures that are dependent on any table or view for all the databases in the server.

 DECLARE @SQL NVARCHAR(max)

 SELECT @SQL = STUFF((SELECT ' UNION ALL SELECT ' + quotename(NAME, '''') + ' AS Db_Name, Routine_Name collate SQL_Latin1_General_CP1_CI_AS as SP_Name FROM ' + quotename(NAME) + '.INFORMATION_SCHEMA.Routines WHERE ROUTINE_Definition like ''%Replace_Table_Name_Here%'' AND ROUTINE_TYPE = ''PROCEDURE''' FROM sys.databases ORDER BY NAME FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 11, '')

 EXECUTE sp_executeSQL @SQL

Upvotes: 0

bitch_cakes
bitch_cakes

Reputation: 126

EXECUTE master.sys.sp_MSforeachdb 
"USE [?]; SELECT * FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%[dbo].[Batch]%'"

Just consider your permutations of the code that references the Batch object (dbo.batch, [dbo].[batch], dbo.[batch], etc)

Upvotes: 1

Melanie
Melanie

Reputation: 3111

Try this:

select * from sysobjects where id in 
(select id from syscomments where text like '%myquery%')
order by [name]

where "myquery" is the name of the table. You need to run this on each database on the server individually.

Upvotes: 5

Related Questions