SQL SERVER – 2005 List All Tables of Database

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

USE YourDBName
GO

SELECT
*
FROM sys.Tables
GO

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 .

    Reply
  • 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.

    Regards,
    Jayaram.

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

    Reply
  • Hi All,

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

    Reply
  • 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.

    -Jennifer

    Reply
  • 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 ..)
    regards

    Reply
    • 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

      Reply
  • 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.

    Reply
  • 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 ?

    Regards,
    Aashish Vaghela.

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

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

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

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

    Reply
    • 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+””
      exec(@sql)

      Reply
  • can we see the data of offset table

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

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

    Reply
  • Better still –

    select object_name from user_objects where object_type = ‘TABLE’;

    Reply
  • 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 ??

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

    Reply
  • 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.

    Thanks

    Reply
  • This command may help to list all columns, all tables and for all databases in SQL server.
    use master
    go
    DECLARE @CMD VARCHAR(1000)
    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

    Reply

Leave a Reply