Please start any new threads on our new site at 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)
 Tables used in view(s)

Author  Topic 

Starting Member

29 Posts

Posted - 2002-12-30 : 07:09:03

I would like to know if it is possible to display which tables a specific view(s) is/are using...

If yes, what are the command(s) ?

Or is it possible to create an sp or view to get the results ?

thanks in advance

Constraint Violating Yak Guru

284 Posts

Posted - 2002-12-30 : 08:06:12
You can use the view information_schema.view_table_usage


select * from information_schema.view_table_usage
where view_name = 'V1'

Go to Top of Page

Starting Member

29 Posts

Posted - 2003-01-06 : 07:59:56
Thanks Lars,

Seems to work on most of the views, but when using a more complex one only some tables are shown. For example this one:

SELECT Vast_of_Tijdelijk, Mannummer, CASE WHEN tussenvoegsel_fam_naam IS NULL THEN fam_naam +' ' else fam_naam + ' ' + tussenvoegsel_fam_naam +' ' END as Achternaam, Voorletters, Kostenplaats, Directe_Leidinggevende, Superieur_van_Leidinggevende, CASE WHEN tussenvoegsel_partnernaam IS NULL THEN partner_naam +' ' else partner_naam + ' ' + tussenvoegsel_partnernaam +' ' END as Partnernaam, Indicator, Gewijzigd_persoon, Gewijzigd_leidinggevende, Gewijzigd_afdeling, Badge, Soort_contract,
network_id, building, phone_number, nonsqlcomponents.fax_number, country_code, country_initials, nonsqlcomponents.vpn_phone_number, mail_code, intern_pager, nonsqlcomponents.vpn_fax_number, mobile_number, audix, email_address, voornaam, kostenplaatsomschrijving, telbook
FROM nonsqlcomponents
JOIN Totaaloverzicht ON Nonsqlcomponents.Mannummer = Totaaloverzicht.amp_id
JOIN kostenplaatsen ON totaaloverzicht.kostenplaats = kostenplaatsen.kosten_plaats
where amp_id not in (select tyco_id from inactief)

Here are the results of the attached view through information_schema:
Personeel dbo Postkamer Personeel dbo Inactief
Personeel dbo Postkamer Personeel dbo Kostenplaatsen
Personeel dbo Postkamer Personeel dbo NonSqlComponents

Totaaloverzicht is missing .....

Edited by - sand-lakes1 on 01/06/2003 08:00:36
Go to Top of Page

- Advertisement -