Getting table list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting table list

Author

Topic

manwmchan
Starting Member

13 Posts

Posted - 2004-10-30 : 00:55:30

I want to get a table list of a database and then store it into a table variable. Do anyone know how to do so?

Following is my statement, but it doesn't work....
declare @table table(table_qualifier char(10), table_owner char(10), table_name char(20), table_type char(20), remarks char(20))
insert @table exec sp_tables

Please help.

Thanks,

Mandel ;)

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-30 : 01:04:16

declare @table table(
TABLE_CATALOG nvarchar(128),
TABLE_SCHEMA nvarchar(128) ,
TABLE_NAME sysname,
TABLE_TYPE varchar(10)
)

insert into @table select * from INFORMATION_SCHEMA.TABLES

select * from @table

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-30 : 04:38:16

The problem with your original statement was that it's not possible to EXEC into a table variable.
But use the INFORMATION_SCHEMA views as Hemanth showed, they are SQL Standard I think,
and also they are more handy than using the various metadata sprocs.

This is a 2 step solution in response to your original code:


create table #table(table_qualifier sysname, table_owner sysname, table_name sysname, table_type varchar(32), remarks varchar(254))
insert #table exec sp_tables

declare @table table(table_qualifier sysname, table_owner sysname, table_name sysname, table_type varchar(32), remarks varchar(254))

insert @table(table_qualifier,table_owner,table_name,table_type,remarks)
select table_qualifier,table_owner,table_name,table_type,remarks from #table

drop table #table

select * from @table

rockmoose

manwmchan
Starting Member

13 Posts

Posted - 2004-10-31 : 20:26:06

Thanks very much. I can retrieve the table list using Mr. Rockmoose's solution :-)

Mr. Hemanth, I don't know why I get Invaild object error while I run the query with INFORMATION_SCHEMA.TABLES... Anyway, I really appreciate your help.

Mandel ;)

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-01 : 02:07:57

what version of sql server are you using ?

Hemanth Gorijala
BI Architect / DBA (yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources