SQL SERVER – 2005 List All Tables of Database

This is very simple and can be achieved using system table sys.tables.

USE YourDBName

FROM sys.Tables

This will return all the tables in the database which user have created.

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

Previous Post
SQL SERVER – Explanation and Example Four Part Name
Next Post
SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

Related Posts

448 Comments. Leave new

  • sir i want know about how to delete same name table from multiple database .

  • Dear All,

    I have a database with three users and I need to show only tables that related to a particular user instead of showing all tables. So how can I achieve this?

    Any help must be appreciated.


  • Exact select query in sql,
    select *from tab;
    sql server is
    select *from sys.tables

  • Hi All,

    I want know about how to db backup via command line.
    or i need DB DDL query.

  • Jen Schmidt (@DBJenSchmidt)
    October 18, 2011 5:36 am

    It was great meeting you at PASS; once again, I searched the web for a quick answer, and yours was the first that popped up. As always, you’re a wonderful resource.


  • Hichem Sharaeen
    October 23, 2011 9:07 pm

    Hi dave,
    i want to thank you very much for this useful blog. keep helping us !
    between brackets : are one of my references.

    I suggest if we add “where is_ms_shipped = 0” will return tables that user created, because i found that only select * from sys.tables returns all tables (even tables that was created by replication or sync process etc ..)

    • Alternatively, you can query information_Schema.tables view to access the user defined tables so that you dont need to query on system tables directly

  • Hi,
    I connected sql server through sql developer, in that i am not able see some table while querying like “SELECT *FROM TEST” ,its giving invalid object.but if select TEST table by clicking on TEST object its showing all data and structure.Please help in this regard thanks.

  • Hello Pinal / Madhivanan,

    First up, EXEC SP_MSFOREACHDB does not work on my DB runnin SQL 2k8 R2.
    Second, EXEC sp_help ” returns the schema & metadata pertaining to one table at a time. Is there a way to put this in some kind of LOOP, or iteration & execute it against all the Tables within the DB at the same time ?
    Lastly, why is it so that SELECT * FROM SYS.TABLES returns an Error like Object not found, whereas, SELECT * FROM sys.tables, executes successfully ? Why is the Query parser Case-Sensitive … ??
    Any suggestions ?

    Aashish Vaghela.

  • Hi, Can you post the query for SQL server to get the active objects?

  • how can we delete total tables which are not used from start or which doesnt have data ?

  • how can we delete total tables which are not used from start or which doesnt have data ?
    in a single server

  • How to Create a stored procedure which takes ‘database name’ and table name as input ‘parameters’ and return column name list of that table.

    • This is subject to sql injection and use it carefully

      declare @sql varchar(1000)
      set @sql=’select column_name from ‘+@dbname+’.information_schema.tables where table_name=”’+@table_name+””

  • can we see the data of offset table

  • i want all table names from the database. please let me know the syntax

  • select * from user_objects where object_type = ‘TABLE’;

  • Better still –

    select object_name from user_objects where object_type = ‘TABLE’;

  • Hello EveryOne,

    I want to get the Table Values of respective Project of QC from SQL Server 2005, which Contains n number of Projects.

    Can any one tell me the query for that ??

  • how can I get all table names with its record count in one query

  • Hi,

    I have one SP and i want to know the dependency in all databases i.e wher the sp is using.
    Sp_syscontents ‘store_proc_name’ is fine or any other sql statment?
    Please reply.


  • This command may help to list all columns, all tables and for all databases in SQL server.
    use master
    create table #TableNames ( DatabaseName sysname, TableName sysname, ColumnName sysname, DataType sysname, Length sysname)
    SET @CMD = ‘USE [?]; SELECT DB_NAME()DATABASE_NAME, SysObjects.[Name] as TableName, SysColumns.[Name] as ColumnName,
    SysTypes.[Name] As DataType,SysColumns.[Length] As Length FROM SysObjects INNER JOIN SysColumns
    ON SysObjects.[Id] = SysColumns.[Id] INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype]
    WHERE SysObjects.[type] = “U” ORDER BY SysObjects.[Name]’
    insert into #TableNames EXEC SP_MSFOREACHDB @CMD
    SELECT * FROM #TableNames Where DatabaseName not in ( ‘master’,’msdb’, ‘tempdb’, ‘model’ )
    drop table #TableNames


Leave a Reply