#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    55
    Rep Power
    6

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    have a look at the INFORMATION_SCHEMA
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    55
    Rep Power
    6
    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
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    225
    Rep Power
    11
    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.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    This may help you


    select table_name from information_schema.views;
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by shabarinath
    select table_name from information_schema.views;
    that will not help

    did you even read this (dormant) thread before making that reply?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    >did you even read this (dormant) thread before making that reply?

    Oh, darn it! I fell for it again!
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    that will not help

    did you even read this (dormant) thread before making that reply?
    Why man he needs all the indexes list naa

IMN logo majestic logo threadwatch logo seochat tools logo