View Depends on itself??

Question

View Depends on itself??

dva2007

SSCertifiable

Points: 7707
More actions
September 26, 2008 at 8:39 am

#194158

hi Friends,
I am looking at some views and when i see the dependencies of that view it shows name of view itself. So it is like view depends on view itself? what is that mean?
Some of the views cant shows actual dependencies. When i see some stored procedure uses some view but when i see that view it doesnt show name of that procedures in dependencies. How come?
thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I haven't found the dependencies data in SQL Server to be very reliable. If someone change's an object's name, it messes all of that up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

Hi,

In this case if i want to rewrite some views then how do i make sure that which stored procedure i need to change? Because i need to change all the stored procedure which depends on origianl view to new view.

Is there any other reliable way to make sure?

Thanks,

Vijay

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 3

For items like this I use SQL Toolbelt (specifically Dependency Tracker) - You can download it for a free 14 day trial, but other than that I would recommend keeping a DB Schema Document and keeping it updated!

dva2007 SSCertifiable Points: 7707 More actions · Answer 4

Hi,

I have just joined the organization and all are just writing code and code but no documentation at all. So it is tough to get even old dated documentation.

Thanks for your suggestion.

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 5

Then do try the SQL Toolbelt, even if you use this as a starting point only. It is so easy to use and is dynamic! So even if other developers keep updating and changing objects you can just refresh and get updated Dependency Tracking... (oh and I don't work for Redgate - But have just discovered the software after getting tired of keeping my documentation upto date...!)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

check out this forum topic

http://www.sqlservercentral.com/Forums/Topic571247-338-1.aspx

"Keep Trying"

GSquared SSC Guru Points: 260824 More actions · Answer 7

dva2007 (9/29/2008)
Hi,
In this case if i want to rewrite some views then how do i make sure that which stored procedure i need to change? Because i need to change all the stored procedure which depends on origianl view to new view.
Is there any other reliable way to make sure?
Thanks,
Vijay

I always query the actual code myself for that kind of thing.

If, for example, the view is "MyView", I'd do this:

select type, name, definition

from sys.all_objects

inner join sys.sql_modules

on all_objects.object_id = sql_modules.object_id

where definition like '%MyView%'

order by type, name

Plug in the object name you want to find, it will find all views, procs, and UDFs that reference that object. Of course, it will also find things that have comments with that word in it, and if you use composite object naming, it will find those things too.

For example, if you have a table "Customers", and a table "CustomersTypes", and you use that query with "like '%Customers%'", it will find code that references CustomersTypes.

I find it good enough for my use. And querying the data is free and doesn't have a trial expiration. It's also very, very certain. It will find everything that references that table or view.

Of course, actual documentation and a real database dictionary would be better, but I'm too lazy to keep those up-to-date.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Doug S. SSCrazy Points: 2801 More actions · Answer 8

Vijay, another way to check dependencies is to script the database and use the find in files feature of Visual Studio. This also works with code that creates dynamic SQL. Its a manual process, but it works.

Good Luck

Doug