Table Structure Information

  • Hi, New to SQL Server 2000

    Need to know how to get a list of all the tables in a database along with the fields in each table

  • use sp-help and sp_columns

    sp_help will give u list of tables from database

    sp_columns <table name> will give u list of columns from table.

  • this will give you a list of all user created tables along with the column names for each table. to get a list of ALL tables, remove the code "where type = 'U'"

    declare @id int

    declare table_cursor cursor for

    select id from sysobjects where type = 'U'

    open table_cursor

    fetch next from table_cursor into @id

    while @@fetch_status = 0

    begin

    select a.name as 'table name', b.name as 'column name' from sysobjects a , syscolumns b

    where a.id = @id

    fetch next from table_cursor into @id

    end

    close table_cursor

    deallocate table_cursor

  • No reason to use a cursor, this can be done as a set based operation. Maybe something like this:

    select t.*, c.* from information_schema.tables t inner join information_schema.columns c on t.table_name=c.table_name

    Using the sp_helpxx procs is also good. Typically you want to avoid querying the system tables directly.

    Andy

Viewing 4 posts - 1 through 4 (of 4 total)

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