Tables names Subquery

When I run the following query, to obtain all table names in the database:
 sp_tables @table_type = "'table'"

I get many columns:
Tabel_Qualifier, ......, TableName, Table_Type, ....

I need to only get the "table names", so I thought I could use a subquery like this:
select table_name from
(
 sp_tables @table_type = "'table'"
)
 as t1

when I try to run it, I get the fllowing error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@table_type'.

Is it that the stored procedure is not allowed in the subquery? How can I fix this?

Thanks


novice12Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you cannot query directly the output of a stored procedure.

insert into #temptable exec sp_tables @table_type = 'table'
select table_name from #temptable


if you are only interested in the list of tables:

select * from information_schema.tables
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
SELECT name
FROM sysobjects
WHERE xtype = 'U'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Uou cant do like this
You need to create a temp table to hold the results of the inner sp call

create table #temp (TQ varchar(255),Owner varchar(32), TName varchar(32), Type varchar(32), remarks varchar(32) )

insert into #temp
sp_tables @table_type = "'table'"
select table_name from #temp
0
 
novice12Author Commented:
I did this:

select * from information_schema.tables
 where Table_type = 'base table'

Thanks for the help!!
0
 
novice12Author Commented:
This will also work:

SELECT name
FROM sysobjects
WHERE xtype = 'U'

Thanks

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.