Post reply

How to see all columns of all tables in a database?

  • September 10, 2005 at 4:00 am

    #87631

    Hi,

    I want to know is there any way to see all tablenames and column names using a quiey on database or system catalog?I have seen some solutions such as "sp_columns " or some other regarding syscolumns table and join it with sysobjects.But none of them can be used to list the followin report:

    table_name column_name

    ------------------------

    jobs job_id

    jobs job_desc

    jobs min_lvl

    jobs max_lvl

    -Thanks in advance

  • September 10, 2005 at 6:06 am

    #588958

    Just as there are global stored procedures in the master database that are context sensitive to the current database, there are also global views in the master database that can query the current database. These views are owned by INFORMATION_SCHEMA.

    For all table columns, try

    select * from INFORMATION_SCHEMA.COLUMNS

    SQL = Scarcely Qualifies as a Language

  • September 10, 2005 at 8:32 am

    #588960

    Just modify Carl's query slightly to:

    select table_name, column_name from INFORMATION_SCHEMA.COLUMNS

    order by table_name, ordinal_position







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply