How to display all user tables in a database

  • graceb

    SSChasing Mays

    Points: 624

    What built-in stored procedure would display all user tables in a database?

    Can someone give me advice on that?

    Regards-Grace copy me please gbarr@czn.com

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    There is a view and table you can use.

    For all tables (and views) you as a user has access to:

    SELECT * FROM INFORMATION_SCHEMA.Tables

    For all user table:

    SELECT [name] FROM sysobjects WHERE type = 'U'

    The last will pick up dtproperties (database diagrams) and MS replication tables, but you can filter those out with the appropriate WHERE clauses if need be.

    K. Brian Kelley

    bk@warpdrivedesign.org

    K. Brian Kelley
    @kbriankelley

  • Andy Warren

    SSC Guru

    Points: 119676

    Both are good solutions. Had never thought about it, but was surprised there was no sp_helptable type sp.

    Another way to get the list from an existing stored procedure would be to run this:

    sp_msforeachtable 'print ''?'''

    Andy

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply