List stored procedure view depenedencies
-
Has anyone out there got a script I can run against a database to list all the views called BY a stored procedure.
I need a list like
sp1 - view85
sp1 - view96
sp2 - view786
....etc
------------------------------
The Users are always right - when I'm not wrong! -
the text for an sp , if not encrypted, can be searched in syscomments.
select sysobjects.name from sysobjects
inner join syscomments on sysobjects.id = syscomments.id
where sysobjects.xtype='P'
and text like '%view%'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
select ROUTINE_NAME, VName
from INFORMATION_SCHEMA.ROUTINES
cross join
( select TABLE_NAME as VName
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'VIEW'
and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsMSShipped') = 0 ) v where patindex ( '%'+Vname+'%', ROUTINE_DEFINITION) > 0
Enjoy
* Noel -
Thank you both. You have saved me a mammoth task. I will have to tell my manager that I won't need need the budgeted amount of time to complete the job. Or will I?
------------------------------
The Users are always right - when I'm not wrong!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply