How to find a 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

SQL Server 2012 Forums

Transact-SQL (2012)

How to find a SP

Author

Topic

gaby_58
Starting Member

33 Posts

Posted - 2015-03-30 : 09:41:11

Hi all,

Just wondering how to find a particular Stored Proc if it is imbeeded in other objects as well?
Can we write a SQL or is there a other way

Thank You

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-30 : 09:53:43

You can use sys.dm_sql_referencing_entities, but I usually just run something l like the following

SELECT DISTINCT Object_Name(ID),* FROM syscomments
WHERE TEXT like '%YourProcedureName%'

SELECT * FROM sys.dm_sql_referencing_entities('Schema.YourProcedureName','Object')

gaby_58
Starting Member

33 Posts

Posted - 2015-03-30 : 15:34:26

Thank you for the info, when I run this
SELECT * FROM sys.dm_sql_referencing_entities('Schema.YourProcedureName','Object') on the database.
Not getting any any info, does this mean they do not exist in any other places

I

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-30 : 15:38:42

So long as you swapped out 'Schema.YourProcedureName' with the name of your schema and procedure and then ran , then they do not exist unless someone is using dynamic sql. You could do a like search on syscomments and that will pick up dynamic sql as well so long as it is not a built string where the procedure name is split in parts

gaby_58
Starting Member

33 Posts

Posted - 2015-03-30 : 16:05:34

For schema, I am using database name, is that correct. Also how to search on syscomments

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 05:30:10

quote:
Originally posted by gaby_58

For schema, I am using database name, is that correct

most likely SCHEMA will be 'dbo'

syscomments isn't foolproof - it returns the SProc in 8,000 character chunks, so the phrase you are searching for could be split across an 8,000 character boundary.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-01 : 06:03:43

How about using sp_depends?

EXEC sp_depends 'procedure name'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 06:34:57

quote:
Originally posted by madhivanan

How about using sp_depends?

Good point Madhi.

Is it reliable these days? It was so flaky when i used it (probably back in the days of Version 6.1

) that I have avoided it since. If it is reliable, nowadays, I'll definitely use it in future on the odd occasions when I need a quick Heads Up of where something is used.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-01 : 08:29:00

quote:
Originally posted by Kristen

quote:
Originally posted by madhivanan

How about using sp_depends?

Good point Madhi.

Is it reliable these days? It was so flaky when i used it (probably back in the days of Version 6.1 ) that I have avoided it since. If it is reliable, nowadays, I'll definitely use it in future on the odd occasions when I need a quick Heads Up of where something is used.

Not reliable only when you create a stored procedure first then the table that it references.

Madhivanan

Failing to plan is Planning to fail

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-04-01 : 10:41:58

RedGate has a tool for searching text in code. It's called "SQL Search" and a free version is available for download. I use it frequently. As has been pointed out, the system tables and views won't pick up references embedded in dynamic SQL.

Also, syscomments has been replaced with sys.sql_modules which, happily, contains the entire definition so there isn't the need to search across 8000 byte boundaries.

I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 13:03:02

quote:
Originally posted by Bustaz Kool

Also, syscomments has been replaced with sys.sql_modules which, happily, contains the entire definition so there isn't the need to search across 8000 byte boundaries

That's the one. I did a little TEST

earlier, and tried

sys.procedures

but that had no text column so I just sat quietly in the corner

but you've now reminded me that I should have tried sys.sql_modules

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources