tables in a view - SQL Server Forums

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

Transact-SQL (2005)

tables in a view

Author

Topic

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-07 : 03:59:02

Hello there!

Is it possible to select all tables and views used in a view?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 04:00:26

Almost. I think there is a limit of 256 base tables in a view.
But why?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 04:08:34

I guess Interview Question??

Chirag

http://chirikworld.blogspot.com/

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-07 : 04:19:21

We are experiencing some performance problems. Therefor we want to delete some history in some tables. Before deleting old records we want to know which views will be affected by this action.

So, is it possible to select all tables and views used in a view?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 04:20:31

See my first reply.

Oh??? Are you asking HOW to do it?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 04:25:50

Somthing like this??

Select * from Information_Schema.VIEW_TABLE_USAGE Where Table_Name = 'YourTable'

Chirag

http://chirikworld.blogspot.com/

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-07 : 04:27:27

Yes, I want to know how to do it. If that wasn't clear I apologize...

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 05:09:08

select * from information_schema.view_table_usage where table_name = <your table>

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-07 : 05:46:48

Okay, that's it!

Thanks!!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 05:53:24

quote:
Originally posted by Q

Okay, that's it!

Thanks!!

Did you read my previous post, that was the same query..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 05:59:35

quote:
Originally posted by chiragkhabaria

quote:
Originally posted by Q

Okay, that's it!

Thanks!!

Did you read my previous post, that was the same query..

Chirag

http://chirikworld.blogspot.com/

Chirag,

Next time try to post your query using different font and may be different color (BLUE may be) so that people can see it clearly.

And one more thing, edit your post again if OP doesn't seem to notice your reply.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:05:07

LOLZ Harsh!!

Next Time surely try out those tricks...;)!!!

Chirag

http://chirikworld.blogspot.com/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 06:07:41

Sorry Chirag..i also did not notice that...

SO the Full Credit goes fully to Chirag.....:-)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:11:42

quote:
Originally posted by pbguy

Sorry Chirag..i also did not notice that...

SO the Full Credit goes fully to Chirag.....:-)

Hey No Worries dude !!!

I gotta learn a new trick..

Chirag

http://chirikworld.blogspot.com/

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-07 : 07:37:53

VIEW ADN_aantalfaktuurregelsperfaktuur:
SELECT ORDERACCOUNT,
INVOICEDATE,
SUM(Aantal) AS AantalFR,
COUNT(ORDERACCOUNT) AS AantalF
FROM dbo.ADN_AantalFaktuurregelsPerFaktuur
GROUP BY ORDERACCOUNT, INVOICEDATE

VIEW ADN_AantalFaktuurregelsPerFaktuur:
SELECT dbo.CUSTINVOICEJOUR.ORDERACCOUNT,
dbo.CUSTINVOICETRANS.INVOICEID,
dbo.CUSTINVOICETRANS.INVOICEDATE,
COUNT(dbo.CUSTINVOICETRANS.INVOICEID) AS Aantal
FROM dbo.CUSTINVOICETRANS INNER JOIN dbo.CUSTINVOICEJOUR
ON dbo.CUSTINVOICETRANS.INVOICEID = dbo.CUSTINVOICEJOUR.INVOICEID AND
dbo.CUSTINVOICETRANS.DATAAREAID = dbo.CUSTINVOICEJOUR.DATAAREAID
GROUP BY dbo.CUSTINVOICEJOUR.ORDERACCOUNT,
dbo.CUSTINVOICETRANS.INVOICEID,
dbo.CUSTINVOICETRANS.INVOICEDATE

FROM-part of ADN_AantalFaktuurregelsPerFaktuur are all tables.

When I want to show which tables are used by the view 'ADN_AantalFaktuurRegels' I don't get any records as result.

Used:
Select *
from Information_Schema.VIEW_TABLE_USAGE
Where view_name = 'ADN_AantalFaktuurRegels'

What is going wrong??? Hopefully it still is all understandable since I used some Dutch words as the name of a view or column. If not I will try to change the names to View1, View2 etc.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 07:43:40

None of the view which you mentioned earlier are with name 'ADN_AantalFaktuurRegels'?

and do you have permission on the View ADN_AantalFaktuurRegels??? if you dont have the permission then you will not be able to view the tables associated to it.

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 07:51:42

Also note that INFORMATION_SCHEMA.VIEW_TABLE_USAGE is based on sysobjects and syscomments. It is possible that there is missing dependency information.

Try running sp_refreshview and then use INFORMATION_SCHEMA.VIEW_TABLE_USAGE.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-07 : 08:40:11

quote:
None of the view which you mentioned earlier are with name 'ADN_AantalFaktuurRegels'?

Stupid me!!! The first view should be named 'ADN_AantalFaktuurRegels'

I don't think permissions is causing this problem... But 'sp_refreshview' seems to solve te problem!

Thanks a lot!

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-09 : 04:55:51

I've been doing some tests on 'sp_refreshview' and it seems to make a difference in which order I refresh different views.

Is this logical?

USE Ax30adnprod

DECLARE viewUpdate CURSOR FOR 
	Select 	table_name
	from 	Information_Schema.VIEWS
	where	table_name not in ('Orbis_Salestable_View','ordersBev','SBA_DagStaat_KP',
			'ADN_aanvulopdrachten_oud','Orbis_Salesline_View','startOrderOverziht_view')
	order by table_name

OPEN viewUpdate
declare @view varchar(50)

FETCH NEXT FROM viewUpdate into @view
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	exec sp_refreshview @view
	print @view
	FETCH NEXT FROM viewUpdate into @view
END

CLOSE viewUpdate
DEALLOCATE viewUpdate

Select 	distinct View_name, table_name 
from 	Information_Schema.VIEW_TABLE_USAGE 
Where	table_Name in (
	Select	distinct view_name
	from 	Information_Schema.VIEW_TABLE_USAGE 
	Where 	Table_Name in ('salesline','salestable')) OR
	table_name in ('salesline','salestable')
order by view_NAME

In this code the select part after 'declare cursor' contains an order by. The final select results in 66 records.
When I do not use the order by part, the result is 67 records...

Any clues anyone?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-09 : 05:39:15

Order by will not affect the records fetched, it just orders the records fetched.

Q
Yak Posting Veteran

76 Posts

Posted - 2007-05-09 : 06:08:01

quote:
Order by will not affect the records fetched, it just orders the records fetched.

If you mean in what order 'exec sp_refreshview' executes the records, I agree.

But that isn't what I was trying to say.

Example:
2 views; viewA and viewB


/*first code*/
EXEC sp_refreshview 'viewA'
EXEC sp_refreshview 'viewB'

Select 	distinct View_name, table_name 
from 	Information_Schema.VIEW_TABLE_USAGE 
Where	table_Name in (
	Select	distinct view_name
	from 	Information_Schema.VIEW_TABLE_USAGE 
	Where 	Table_Name in ('salesline','salestable')) OR
	table_name in ('salesline','salestable')
order by view_NAME

/*second code*/
EXEC sp_refreshview 'viewA'
EXEC sp_refreshview 'viewB'

Select 	distinct View_name, table_name 
from 	Information_Schema.VIEW_TABLE_USAGE 
Where	table_Name in (
	Select	distinct view_name
	from 	Information_Schema.VIEW_TABLE_USAGE 
	Where 	Table_Name in ('salesline','salestable')) OR
	table_name in ('salesline','salestable')
order by view_NAME

The first code creates different output then the second code.

How is this possible???

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources