SQL Server Performance

Selecting all tables in a database

Discussion in 'Performance Tuning for DBAs' started by Sri316, May 19, 2005.

  1. Sri316 New Member

    Hi all

    What is the query to SELECT ALL THE TABLES from a database?
    Pl lemme know

    Sri
  2. sundeip New Member

    SELECT * FROM SYSOBJECTS WHERE XTYPE='U'

  3. dineshasanka Moderator

    What do you mean by SELECT ALL the tables, is it like select * from A,B .. to All tables
    or Do you want the list of all the tables
    if you want list of all the tables as sundeip said use
    SELECT name FROM SYSOBJECTS WHERE XTYPE='U'


    quote:
    What is the query to SELECT ALL THE TABLES from a database?
    Pl lemme know

  4. ranjitjain New Member

    better use this:

    select table_name from information_schema.tables
  5. dineshasanka Moderator

    Yes,
    Better use rantjit code.
    Always better to go without system files

    quote:Originally posted by ranjitjain

    better use this:

    select table_name from information_schema.tables
  6. Sri316 New Member

    Thanks a lot folks
  7. derrickleggett New Member

    You might want to consider a small revision:



    SELECT TABLE_NAME
    FROM information_schema.tables
    WHERE
    TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME <> 'dtproperties'


    This only shows tables, instead of tables and views. In addition, the dtproperties table (which is a system table used for diagrams, etc) will not show up.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  8. surendrakalekar New Member

  9. surendrakalekar New Member

    Dinesh & Derrick
    Please explain... Why using information_schema is better than system tables ?


    quote:Originally posted by dineshasanka

    Yes,
    Better use rantjit code.
    Always better to go without system files

    quote:Originally posted by ranjitjain

    better use this:

    select table_name from information_schema.tables
  10. derrickleggett New Member

    Microsoft does a good job explaining it.

    From Books Online:


    quote:
    Information Schema Views
    Microsoft® SQL Server™ 2000 provides two methods for obtaining meta data: system stored procedures or information schema views.



    Note To obtain meta data, use system stored procedures, system functions, or these system-supplied views only. Querying the system tables directly may not provide accurate information if system tables are changed in future releases.


    These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.

    SQL Server supports a three-part naming convention when referring to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database, but visible only in the master database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database. This table describes the relationships between the SQL Server names and the SQL-92-standard names.


    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  11. surendrakalekar New Member

    Thanks Derrick

Share This Page