How to list all tables with their schemas in a database?

  • It is for SQL 2008.

    Many thanks in advance.

  • it's all in sys.tables, but i prefer to use built in functions instead of joining sys.schemas:


    SCHEMA_NAME(schema_id) As SchemaName ,

    name As TableName

    from sys.tables

    ORDER BY name


    SELECT As SchemaName , As TableName

    from sys.tables tbl

    inner join sys.schemas sch on tbl.schema_id = sch.schema_id



    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I typically just go for sys.objects like the following.

    SELECT SCHEMA_NAME(schema_id) As SchemaName

    ,name As TableName

    FROM sys.objects

    WHERE type = 'U'

    An interesting sidebar on this is that if you look at the execution plan, the version going to sys.tables will get it's data from sysschobjs and sysidxstats and will cost a little bit more than just going direct to sys.objects (for which sysschobjs is the base object).

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • You can also use the following:

    SELECT Table_Schema, Table_Name

    FROM information_schema.tables

    WHERE table_type = 'BASE TABLE'

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

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