Find all objects that use this view?

Question

Post reply

Find all objects that use this view?

Jason Marshall

SSCommitted

Points: 1791
More actions
March 10, 2010 at 9:17 am

#216472

Morning-
Anyone know of a way to find all sprocs or views in a database that use a specific view or table in the FROM clause?
Thanks...
Jason

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Try this

SQL Server 2008 Books Online (November 2009)

http://msdn.microsoft.com/en-us/library/ms190464.aspx

Think it has all that your need.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

FabricioLimaDBA Mr or Mrs. 500 Points: 507 More actions · Answer 2

I use this simple query to find this.

select distinct B.name ,

case type when 'P' then 'Stored procedure'

when 'FN' then 'Function'

when 'TF' then 'Function'

when 'TR' then 'Trigger'

when 'V' then 'View'

end

from syscomments A (nolock)

join sysobjects B (nolock) on A.Id = B.Id

where A.text like '%Objetc_Name%'

I hope help you.

Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

Fabricio is right, his suggestion works for me also.

With one difference that I was only using the syscomments table not joining to sysobjects in order to get a better output.

Unfortunately there is not a built-in function that gives such a list except selecting from the syscomments.

Eralper

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

nigel. SSChampion Points: 11636 More actions · Answer 4

You might find these articles by Phil Factor useful:

Finding stuff part1 & part2

Nigel
How to post questions

Paul White SSC Guru Points: 150467 More actions · Answer 5

Nigel,

Good point and nice links, but they don't cover the new system views in 2008.

See Understanding SQL Dependencies in Books Online.

The new system views:

sys.sql_expression_dependencies

sys.dm_sql_referencing_entities

sys.dm_sql_referenced_entities

...represent the best solution from Microsoft so far, rivalling third-party solutions to this long-standing problem.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nigel. SSChampion Points: 11636 More actions · Answer 6

Thanks Paul,

They could come in really handy. Oh, if only I had 2008, 😀 and the time to play.

I'd not even noticed this was in a 2008 forum :blush:

Nigel
How to post questions

Paul White SSC Guru Points: 150467 More actions · Answer 7

nigel. (3/11/2010)
They could come in really handy. Oh, if only I had 2008, 😀 and the time to play.

2008 Dev Edition $50ish.

Free time...priceless :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

FabricioLimaDBA Mr or Mrs. 500 Points: 507 More actions · Answer 8

This query is very useful for me.

Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

FabricioLimaDBA (3/10/2010)
I use this simple query to find this.
select distinct B.name ,
case type when 'P' then 'Stored procedure'
when 'FN' then 'Function'
when 'TF' then 'Function'
when 'TR' then 'Trigger'
when 'V' then 'View'
end
from syscomments A (nolock)
join sysobjects B (nolock) on A.Id = B.Id
where A.text like '%Objetc_Name%'
I hope help you.

This little script works fine.

One concern with this script is the use of objects that are only available for backwards compatability. These objects are not guaranteed to be present in future versions. I would recommend upgrading the script to use the views and DMVs for 2005 and 2008.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

FabricioLimaDBA Mr or Mrs. 500 Points: 507 More actions · Answer 10

That's OK. I work with Sql Server 2000 and 2005 yet. I will make the upgrade.

Thank you.

Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

FabricioLimaDBA (3/12/2010)
That's OK. I work with Sql Server 2000 and 2005 yet. I will make the upgrade.
Thank you.

Something that I do is make it conditional. I run in a multi-version environment as well. Throw in a condition that checks for server version, if 2000 run this script - else run this script. Works great.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 12

The 2005/2008 query that I use is:

select OBJECT_NAME(object_id), *

from sys.all_sql_modules

where definition like '%<your search phrase goes here>%'

If I want to limit the results to just views, proc, etc. I'll do a join to sys.views, sys.procedures, sys.triggers, etc.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

FabricioLimaDBA Mr or Mrs. 500 Points: 507 More actions · Answer 13

Thank you WayneS.

Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

WayneS (3/13/2010)
The 2005/2008 query that I use is:
select OBJECT_NAME(object_id), *
from sys.all_sql_modules
where definition like '%<your search phrase goes here>%'
If I want to limit the results to just views, proc, etc. I'll do a join to sys.views, sys.procedures, sys.triggers, etc.

Thanks Wayne.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events