asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

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

novice12

ASKER

I did this:

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

Thanks for the help!!

novice12

ASKER

This will also work:

SELECT name
FROM sysobjects
WHERE xtype = 'U'

Thanks