list tables in tempdb

Posted on 2006-05-17
Last Modified: 2008-03-06
How can I list the tables in the tempdb?

This doesn't work:

   select * from tempdb.systables

and this doesn't work:


Question by:brokeMyLegBiking
    LVL 20

    Expert Comment

    exec sp_tables
    LVL 20

    Expert Comment

    Use Tempdb
    exec sp_tables
    LVL 20

    Accepted Solution

    To just get user tabels you can also do

    select name, user_name(uid) from sysobjects where type='U'
    LVL 68

    Expert Comment

    Do you want to list *all* tables or just your own?

    If you want to list all, you can do this:

    SELECT name
    FROM tempdb.dbo.sysobjects WITH (NOLOCK)
    WHERE xtype = 'U'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how the fundamental information of how to create a table.

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now