October 10, 2001 at 6:55 pm
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
October 10, 2001 at 10:11 pm
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.
October 18, 2001 at 9:51 am
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
October 18, 2001 at 10:32 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply