Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 816
  • Last Modified:

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


0
novice12
Asked:
novice12
2 Solutions
 
Guy Hengel [angelIII / a3]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 CroweCommented:
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now