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

SQL Scripts, SQL System Table
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

  • thanks Imran

    Its working…

    Reply
  • Girish Vikraman
    February 13, 2009 1:00 pm

    How could I find the structure of all tables in a DB in one query ?Is it possible?..

    Advance thanks for the help…. :)

    Reply
    • Hello,

      were you able to find answer for this?
      i am also looking at solution to find structure of all tables at one streach.

      Regards,
      Jayasheela

      Reply
  • hello sir

    i want retrieve table names of a particular selected database
    please help me
    first i will list the databases present in my server
    then i will select one of the databases
    now i want to retrieve table names of a selected database
    i hope my question is clear

    Advance in thanks

    Reply
  • Hi Rashmi,

    Check below query,

    SELECT 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]

    Reply
  • Thanks. It was a great help.

    Reply
  • I see the previous admin created additional database files on different drives and used this command to ge the name of each file and it’s location.

    How do I find out what tables are being stored in each of these files?

    Reply
  • Sorry, this is the command I used to see the file names and locations: select * from sys.database_files

    Reply
  • hi,

    i want to make a CSV of each tables separate.

    What is sql query for this? i need to run in query analyzer.

    i made a connection bet’n MS-SQL and php for this, and this php file runs every day.

    Thanks

    -Hitesh

    Reply
  • how to list of tables/ views in the databases that have a direct ODBC connection

    Can any one help me

    Reply
  • Well prepared and answered were very useful to all of our team members. Thanks for your timely work and helpful

    Reply
  • I want queries about sql server 2005

    Reply
    • What did you mean by queries?
      Do you want to know if the version is 2005?
      If yes,

      SELECT @@VERSION

      Reply
  • Hello Friends i need a query
    Table
    id , Name , Age , Salary, Add1,Add2,Add3,……

    This table is generated by a tool, so i dontknow exact coloume except few ,
    so i want to retrive all value for those coolume which is specified in query.

    Plz help or communicate if not getting the exact problem

    Reply
  • Hi, i m new in database. Actually i want to know how can i get tables from the database using querry.
    Is there any inbuilt querry or we have to make it..
    please reply soon…..

    Reply
  • Hi Vipin,

    if you are using SQL 2005, then you can use:

    select * from sys.objects
    WHERE Type=’U’

    Thanks,

    Tejas

    Reply
  • Hi,
    ——————————————
    DECLARE TABLE_NAMES_CURSOR CURSOR
    FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE LIKE ‘BASE TABLE’
    DECLARE @ONE_TABLE_NAME VARCHAR(250)
    OPEN TABLE_NAMES_CURSOR
    FETCH NEXT FROM TABLE_NAMES_CURSOR INTO @ONE_TABLE_NAME

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @ONE_TABLE_NAME
    DELETE FROM @ONE_TABLE_NAME
    FETCH NEXT FROM TABLE_NAMES_CURSOR INTO @ONE_TABLE_NAME
    END

    CLOSE TABLE_NAMES_CURSOR
    DEALLOCATE TABLE_NAMES_CURSOR
    ————————————————-

    the above code using in sql server2005

    in in this @ONE_TABLE_NAME i’ll be storing table name,
    when i print this i can able to print but the same thing when i use for deleting rows from table i’m getting error

    DELETE FROM @ONE_TABLE_NAME

    please help me

    thanx
    ambu

    Reply
  • Imran Mohammed
    April 1, 2009 6:18 am

    @ Ambanna,

    You cannot give table name as variable for delete statement, select statement …

    But you can store that whole statement into a variable and execute that variable which will execute whole statement.

    some thing like this,

    declare @sql varchar(1000)

    set @sql = ‘DELETE FROM ‘+@ONE_TABLE_NAME

    Exec (@Sql)

    Place above three lines in your cursor, it should work.

    Suggestion:

    Instead of going through all this pain of cursor, why don’t you take a look at sp_MSForEachTable. Very easy to handle and less complex. Just one line code.

    Link :

    It has good explanation.

    Regards
    ~ IM

    Reply
  • Thanks for replying, it will great help to me..
    Thanks Again.

    Reply
  • Thank you very much Imran

    U’ve save a lot of my time

    thanx once again

    Reply
  • Sir,
    Actually i m doing a program in which database operations are performed from user interface using sql dmo.
    when i m creating database it works,but when i want to delete database it gives some error…

    so plz provide me the code for deleting database from user interface.

    please dont give solution through sql querry….

    please reply soon…….

    Reply
  • I get this error:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.Tables’.

    Reply

Leave a Reply