Check if table is a view

Dev Shed Forums Sponsor:

January 19th, 2010, 08:20 AM

SpiritedAway

Contributing User

Join Date: Dec 2008

Posts: 55

Time spent in forums: 20 h 33 m 24 sec
Reputation Power: 5

Check if table is a view

Hi people,

Is there a way for me to query the db to see if a table is a view..?

Thanks

January 19th, 2010, 10:06 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

have a look at the INFORMATION_SCHEMA

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

January 19th, 2010, 10:18 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,683

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 1 m 18 sec
Reputation Power: 284

Quote:

Originally Posted by SpiritedAway

Is there a way for me to query the db to see if a table is a view..?

A table can never be a view (because it's a table)

(Sorry, could not resist)

But if you want to know if a name refers to a table or a view then follow r937's advice.

January 20th, 2010, 12:19 AM

SpiritedAway

Contributing User

Join Date: Dec 2008

Posts: 55

Time spent in forums: 20 h 33 m 24 sec
Reputation Power: 5

Tx for your time chaps - looking into it now...

@shammat
1st time working with views so good to know the proper way of referring to them

January 20th, 2010, 02:34 AM

Devart Team

Contributing User

Join Date: Dec 2009

Posts: 225

Time spent in forums: 3 Days 3 h 17 m 27 sec
Reputation Power: 10

To get list of tables and view you can use select from information_schema.tables -

Code:

SELECT * FROM information_schema.tables;

This system table contans all tables and all views; this table has 'TABLE_TYPE' field, you could use it to define type of object -

For example -

Code:

SELECT * FROM information_schema.tables WHERE 'TABLE_TYPE' = 'BASE TABLE'; -- Gets all tables

SELECT * FROM information_schema.tables WHERE 'TABLE_TYPE' = 'VIEW'; -- Gets all views, without information_schema system views

And there is information_schema.views system table, it contains all views.

Last edited by r937 : January 20th, 2010 at 07:19 AM.

October 5th, 2012, 05:16 AM

shabarinath

Registered User

Join Date: Oct 2012

Posts: 2

Time spent in forums: 14 m 48 sec
Reputation Power: 0

This may help you

select table_name from information_schema.views;

October 5th, 2012, 05:48 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

For myself, I always prefix views with 'v_'. I never prefix tables (with, for instance, 'tbl_' or 't_') on the assumption that everything is a table unless otherwise stated.