Tables names Subquery

Posted on 2006-05-10
Last Modified: 2011-09-20
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?


Question by:novice12
    LVL 142

    Accepted Solution

    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
    LVL 34

    Assisted Solution

    by:Brian Crowe
    SELECT name
    FROM sysobjects
    WHERE xtype = 'U'
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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

    Author Comment

    I did this:

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

    Thanks for the help!!

    Author Comment

    This will also work:

    SELECT name
    FROM sysobjects
    WHERE xtype = 'U'



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now