Find referenced SP within SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find referenced SP within SP

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-01-11 : 01:20:24

Team,

I have one stored procedure named 'User_SP_Check_Validity' , and this SP is used / referenced in most of the SP's in my database. I want to know what are the SP is not using 'User_SP_Check_Validity'.

All your help are much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 01:36:23

use sys.sql_modules for that

something like


SELECT OBJECT_NAME(object_Id) AS [Name] FROM sys.sql_modules where definition NOT LIKE '%User_SP_Check_Validity %' AND definition like '%CREATE PROCEDURE%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-01-11 : 03:14:35

Thanks.

Kristen
Test

22859 Posts

Posted - 2012-01-11 : 04:36:06

"definition NOT LIKE '%User_SP_Check_Validity %'"

I think User_SP_Check_Validity might not be followed by a space? Might be followed by ";" or linebreak - maybe other things too (can't immediately think of any others)

Assuming it is never the very last thing in the Sproc code then this would probably work:


definition NOT LIKE '%User_SP_Check_Validity[^A-Za-z0-9_]%'

and if it could eb the very last thing then


definition + ' ' NOT LIKE '%User_SP_Check_Validity[^A-Za-z0-9_]%'

but that will probably effect performance - won't matter for a one-off report though.

"AND definition like '%CREATE PROCEDURE%'"

I think probably better is:


SELECT OBJECT_NAME(M.object_Id) AS [Name]
FROM	sys.sql_modules AS M
	JOIN sys.objects AS O
		 ON O.object_id = M.object_id
		AND O.type = 'P'
WHERE   definition NOT LIKE '%User_SP_Check_Validity[^A-Za-z0-9_]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources