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

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

  • I’m trying to geat a list of tables in all views in a DB including the views that have their source tables in another database…

    SELECT * FROM [INFORMATION_SCHEMA].VIEW_TABLE_USAGE only gives me a list of views that have their input tables sourced from the current database

  • Umamaheshwer yadav
    March 6, 2012 2:36 pm

    this is too good……………

  • Brett Stutzman
    March 15, 2012 11:30 pm

    Anyone find a solution to this one, trying to restrict sys, and information_schema objects from ODBC and ms sql managment console on sql server 2008 r2 dbase but having no luck.
    found –

    but they never had a full proof answer either from 3 yrs ago. Help please as I am trying to set up the connection to the dbase where the user will only be able to see a select set of tables, views and with either userid pass word via ms sql mang console or ODBC have just select access to tables for users, keeping things locked down from the user. any ideas?
    Brett Stutzman

  • I require all the databases whether it is attached to Server or not. Is it possible ? Help me

  • I have one great doubt , i need to search data in a multiple table . the table name’s are like TR42012,TR52012,TR62012 ETC… I give an Input “month” only, that input will check all the table like (TR42012,TR52012,TR62012 ETC…) and give the particular “month” value only….

    its possible or not… please give me a solution to send my mail Id ::: “vinosh.john@gmail.com”

  • gr8 blog …. dis discusion helped me alot …. nd cleared many doubts too…

  • Is it possible to show the child/children of a table and show the links?

  • 1)how to write query for comparing userid & password in sql 2005

  • Santosh Kumar Polavarapu
    September 18, 2012 6:37 am

    I have got solution from this site thank’s

  • Hello. I am a complete newbie and your articles are some of the best that I have come across.

  • Hi Pinal,

    I want to know one thing. it is possible that we can set the table name at SQL which can be use at front end side. I mean if I assign any name to ‘select query’ and it will return table to front end with a given name.

    1) excute tis (below) procedure …, i will get 3 tables ..ok

    2) and …i m using ExcuteDataSet() in frontend like DatabaseLayer.cs (.Net)

    3) so tat time ….(from the Dataset) …Table1,Table2,Table3,Table4…..etc( table index 0 ,1 ,2,3 …etc)

    4) I want to access Dataset.Table[‘tablename which is given in backend’]?
    not by index value


    in Store procedure (Sample)


    CREATE PROC spname(@parm1 int,

    @param varchar(10)

    ) AS

    SELECT filed1,filed2,filed3 FROM table1

    SELECT field1,filed2,field4 FROM another_different_table_1

    SELECT field1,filed2,field4 FROM another_different_table_2



  • Hi ..
    how to get all the table structure with select statement in a database?
    ex: Emp table having name, department and salary columns
    i want output like
    select name, department, salary from emp

  • How will i run parallel load in SSIS package, like i have 10 text file and my system configuration is 7cpu and 32gb ram, so i want to utilise all the cpu and 32gb ram, so how can i run all the text file in one short. not like dependence . as of now my job will take one by one instead of that i have load parelley load all the data into one table.

    Note: I have one table , source 10 text file,destination one table/

    Please help me to understand the logic to implement and utilize all the cpu in the server

  • Hi experts, i need your help. I need to create a store procedure which will receive an four arrays of parameters. Each one has parameters split with a comma. I need to retrieve data very fast and i cant just use the split function for security reasons. Can give some advice. Thanks

  • Hi, I am working into oracle sql developer.so i need to get into database all tables table names. how can write query?

  • select * from tab; thats all :-)

  • How to get list of table names of a database, whose names start with “student”?
    For example: I have many tables in database like (student_attendance,student_address, student_exam, employ_salary, employ_attendance).
    Here I only need the list table names, whose names start with “student”??

    Thank you..

  • Hello,

    I want to compare two tables on diffrent servers which are having same structure.

    Please assist.

    Thanks in Advance

  • Hello,

    How to compare two tables on diff server which are having same structure.

    PLease assist.

    Thanks in advance

  • Thanks you, this works !!


Leave a Reply