none
how to get all table names from a database?

    Question

  • I have two questions:

     

    1. Which system table (data dictionary) I can select to get all table names in one database?

    2. What sql command can I use to get a definition about a table?

     

    Thanks

    Friday, April 13, 2007 3:23 PM

Answers

  • For both SQL 2000 and SQL 2005:

     

    Code Snippet

     

    SELECT TABLE_NAME

    FROM   INFORMATION_SCHEMA.TABLE

     

    EXECUTE sp_help MyTableName

     

     

     

    Friday, April 13, 2007 5:12 PM
  • EXECUTE sp_help INFORMATION_SCHEMA.TABLES;

     

    That wouldn't work -this would...

    Code Snippet

     

    EXECUTE sp_help MyView

     

    Perhaps I'm not understanding you correctly. You want to do the same thing 'as' INFORMATION_SCHEMA.TABLES WITHOUT using INFORMATION_SCHEMA.TABLES?

    Friday, April 13, 2007 5:54 PM
  • My mistake, I misunderstood you.

    Code Snippet

     

    SELECT *

    FROM   INFORMATION_SCHEMA.TABLES

     

    Then you can see all of the columns, and select the ones you need.

     

    There is also INFORMATION_SCHEMA.COLUMNS.

     

    You might want to check out INFORMATION_SCHEMA in Books Online.

    Friday, April 13, 2007 6:32 PM

All replies

  • For both SQL 2000 and SQL 2005:

     

    Code Snippet

     

    SELECT TABLE_NAME

    FROM   INFORMATION_SCHEMA.TABLE

     

    EXECUTE sp_help MyTableName

     

     

     

    Friday, April 13, 2007 5:12 PM
  • EXECUTE sp_help INFORMATION_SCHEMA.TABLES;

     

    I got syntax error by excuting the above command. If the table name is a table in the database, that will be ok but I need to know the the system views definition like information_schema.tables.

     

    Thanks for your help!

    Friday, April 13, 2007 5:37 PM
  • EXECUTE sp_help INFORMATION_SCHEMA.TABLES;

     

    That wouldn't work -this would...

    Code Snippet

     

    EXECUTE sp_help MyView

     

    Perhaps I'm not understanding you correctly. You want to do the same thing 'as' INFORMATION_SCHEMA.TABLES WITHOUT using INFORMATION_SCHEMA.TABLES?

    Friday, April 13, 2007 5:54 PM
  • I want to know what columns are in information_shcema.tables first and then select some data from information_schema.tables like table_names and others.

     

    Thanks

    Friday, April 13, 2007 6:06 PM
  • My mistake, I misunderstood you.

    Code Snippet

     

    SELECT *

    FROM   INFORMATION_SCHEMA.TABLES

     

    Then you can see all of the columns, and select the ones you need.

     

    There is also INFORMATION_SCHEMA.COLUMNS.

     

    You might want to check out INFORMATION_SCHEMA in Books Online.

    Friday, April 13, 2007 6:32 PM
  • Thanks.
    Friday, April 13, 2007 7:47 PM