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')
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -