information_schema.view_table_usage

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 2000 Forums

SQL Server Development (2000)

information_schema.view_table_usage

Author

Topic

sand-lakes1
Starting Member

29 Posts

Posted - 2003-01-07 : 11:12:05

When I use this view not all tables used are displayed ...... for a specific view containing joins......

Have been searching for a possible explanation all day; did not find anything. Therefore made a new database and created the tables and views as a copy from the first database.

Does anyone have an explanation for this ? There is a difference in the sysobject/sysdepends tables between the two database.

I really don't understand. Help is welcome....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-07 : 11:21:05

quote:
I really don't understand. Help is welcome....

Well, we could use a little of that ourselves. What exactly is your question?

quote:
Therefore made a new database and created the tables and views as a copy from the first database.

OK. Did that solve anything? If it did, WHAT did it solve?

quote:
When I use this view not all tables used are displayed ...... for a specific view containing joins

Could you post some table and view DDL and point out exactly what's missing?

quote:
There is a difference in the sysobject/sysdepends tables between the two database

OK. So? It's not particularly helpful to say "it's messed up in one database but works fine in another, DIFFERENT database" That's like asking why one person is sick and another is healthy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-07 : 11:38:55

This might give a clue as to what may have happenned.
In general don't rely on sysdepends.

create table a (id int)
go
create table b (id int)
go
create view c
as
select a.id from a,b where a.id = b.id
go
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
drop table a
go
create table a (id int)
go
select * from c
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sand-lakes1
Starting Member

29 Posts

Posted - 2003-01-08 : 05:27:32

Sorry Rob, You are right, my question was incomplete and formatted in a wrong way.

Poster after you -Nigel- put my question on this formum in a right way.

Indeed some tables are missing after running: "select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where view_name ='c'"

When I did a drop and create of the view I ran the command again and all the tables used in the view were displayed correctly.

Only question which is still open: "What is causing the incomplete list after a drop/recreate of the table only ? Mistake is in the sysobjects/sysdepends table ? I am trying to explain why all this is happening "

All people who are trying to help thanks for your assistance.

Edited by - sand-lakes1 on 01/08/2003 05:28:53

sand-lakes1
Starting Member

29 Posts

Posted - 2003-01-08 : 05:32:18

quote:

This might give a clue as to what may have happenned.
In general don't rely on sysdepends.

create table a (id int)
go
create table b (id int)
go
create view c
as
select a.id from a,b where a.id = b.id
go
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
drop table a
go
create table a (id int)
go
select * from c
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE

This is the situation where it all goes wrong. Thanks Nigel. When I drop/recreate the view the problem is solved. Are the sysobjects and sysdepend tables only then updated when I do a drop and recreate of the view ?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-08 : 06:03:29

sysdepends holds the IDs of the tables.
If you drop a table then that ID is no longer valid so is removed from sysdepends.

When the table is dropped it's reference is removed from sysdepends.
Creation of the table understandably does not update the other objects.
The next time the view (or SP) runs the server could update sysdepends but it doesn't.

It used to be a lot worse.
If you dropped a table the server would access anything with that ID so could be updating/reading incorrect tables and corrupt the database if the structure was wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sand-lakes1
Starting Member

29 Posts

Posted - 2003-01-08 : 06:24:07

Thanks Nigel,

So, only solution will be a drop/recreate of the view ?
Correct ?

Thanks

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-01-08 : 08:24:18

You can use sp_refreshview. See BOL for details e.g.

 
create table a (id int) 
go 
create table b (id int) 
go 
create view c 
as 
select a.id from a,b where a.id = b.id 
go 
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE 
drop table a 
go 
create table a (id int) 
go 
select * from c 
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE 

exec sp_refreshview c

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE 

drop view c
drop table a
drop table b

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources