Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Tables used in view(s)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sand-lakes1
Starting Member

29 Posts

Posted - 12/30/2002 :  07:09:03  Show Profile  Reply with Quote
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

Sweden
284 Posts

Posted - 12/30/2002 :  08:06:12  Show Profile  Reply with Quote
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'

Go to Top of Page

sand-lakes1
Starting Member

29 Posts

Posted - 01/06/2003 :  07:59:56  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000