Tables used in view(s) - 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 2000 Forums

SQL Server Development (2000)

Tables used in view(s)

Author

Topic

sand-lakes1
Starting Member

29 Posts

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

Hi,

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

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-12-30 : 08:06:12

You can use the view information_schema.view_table_usage

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_338l.asp

e.g.

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

sand-lakes1
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources