Journey to SQL Authority with Pinal Dave

USE YourDBName
GO
SELECT *
    FROM sys.Tables
GO

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 74 Comments

74 Responses to “SQL SERVER - 2005 List All Tables of Database”

1. on June 27, 2007 at 4:24 pm Sqlist

   How do I list all tables on a server?

   
   
2. on July 3, 2007 at 4:51 am saravanan

   How do I list all databases on a server?

   
   
3. on July 11, 2007 at 4:14 pm Shawn

   Hey guys, I know that Pinal is pretty busy, so I thought I would help you out with your questions.

   To get all the databases on a server, you can use the same technique, but just a bit different query.

   USE Master
   GO
   SELECT *
   FROM sys.Databases
   GO

   For a list of all tables on a server, I would use the sp_msforeachdb procedure like this.

   sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’

   You can see some more examples of sp_msforeachdb on my own blog, here is a link to the article.

   http://www.sqlstop.com/index.php/2007/07/11/what-bol-doesnt-know-wont-hurt-you/

   
   
4. on July 11, 2007 at 6:03 pm pinaldave

   Thanks Shawn,

   Due to amount of emails and comment I receive I am not able to keep up with all of them. However, eventually I am able to answer them.

   Regards,
   Pinal Dave (SQLAuthority.com)

   
   
5. on July 20, 2007 at 12:07 am Radha Devi Gudipati

   SELECT *
   FROM information_schema.Tables

   This will display the list of table in the database

   
   
6. on July 30, 2007 at 12:37 am Bhuwanesh

   How could i get all the data from all the table of the specific database..?

   plz reply on my mail..if possible.

   thanks in advance
   Bhuwanesh..

   
   
7. on August 1, 2007 at 2:56 am Kumar Biswas

   I want to view all the table in a database

   
   
8. on August 3, 2007 at 2:34 pm ahmed

   hi,

   i want to see all the table names from a sigle database in sql server 2000. please anyone can help me!!!!!!!!!!!!!!!!!!!

   
   
9. on September 3, 2007 at 8:04 pm Lakshmi

   Thank you

   
   
10. on September 18, 2007 at 5:42 am pravin

    how can i know that any changes is occur in my table structure. is there any field in any system table that value is change if any changes is done in table structure???????

    please give me reply……..

    thanks…………..

    
    
11. on December 9, 2007 at 12:00 pm ELHAM

    i can not see systables in my database please tell me how i can see and show systables

    
    
12. on December 12, 2007 at 12:38 pm rama

    I want to view all the table in a database

    
    
13. on December 14, 2007 at 1:50 am sivakeshavulu

    hi,
    how to take the data to all back up in .sql with data also,
    example my sql data base so use export command like it show the sql format also in the same i need the mssqlserver 2005 database back up to all data with quires

    
    
14. on December 19, 2007 at 11:00 pm Srinivas

    How do i list all database triggers only in an instance?

    
    
15. on December 29, 2007 at 5:55 pm Mani

    Hi,

    Which query i used to list the tables in Particular Database?

    
    
16. on December 29, 2007 at 6:32 pm pinaldave

    Mani,

    Please read the post on top.

    Regards,
    Pinal

    
    
17. on December 31, 2007 at 3:45 pm Mani

    Thank U……..

    Regards,
    Mani……..

    
    
18. on January 4, 2008 at 5:04 pm sunny

    i want to see all tables in sqlserver2000;

    
    
19. on January 4, 2008 at 5:05 pm sunny

    i want to see all tables in sqlserver2000; given queries are not working im my system;

    
    
20. on January 9, 2008 at 12:52 pm Princes

    Thank you…very much…its very helpful …

    Thankyou & Regards,
    Princes

    
    
21. on January 14, 2008 at 7:46 pm Heinrich

    Works like a dream!! Thank You

    
    
22. on January 21, 2008 at 3:01 pm Shobha

    Thanks for ur help in viewing all tables from a database. Can u tell me how to view the table structure ?..

    
    
23. on January 31, 2008 at 1:50 pm Dharmendra

    Thanks for your help but i am looking something like where i can display the list of database . Please anybody help me to listing of database in sql 2005.

    
    
24. on February 1, 2008 at 10:31 am Abha Mishra

    Hi,
    Thanks this helped me.
    can anyone tell me how to see only the user defined tables or
    stored procedures or views or functions in a given database ?

    
    
25. on February 4, 2008 at 10:13 am pRATEEK

    Thanks
    Thanks a lot!!!!!!!!!!!!!!

    
    
26. on February 7, 2008 at 6:39 pm suresh

    dear sir,
    i want to know how to see the structure of table……..
    In oracle desc tablename means it will show the structure of that particular table but the sql server it is not show ………
    i need solution for this question as early as possible …..
    advance thanks ………. please consider as a urgent…..

    
    
27. on February 14, 2008 at 2:27 am Venkatesh

    Suresh,
    to view the structure of the table in MS SQL Server
    try this

    Solution: sp_help tableName

    Thanks,
    Venky

    
    
28. on March 18, 2008 at 12:24 am dawn

    I want to see all the data in all the tables in a database using the least code possible !!! Please let me know ASAP…

    
    
29. on April 14, 2008 at 1:52 pm raja

    how many user using my db
    (same user) ?

    
    
30. on April 15, 2008 at 3:46 am anteneh

    thaink you
    what about
    only user tables?

    
    
31. on April 15, 2008 at 3:53 pm Harinath_hyd

    I got much information regarding the database list of objects

    Thank you
    Harinath

    
    
32. on April 17, 2008 at 9:06 pm AbhishekGupta

    I am not getting abt master table.

    
    
33. on April 19, 2008 at 3:34 pm Richie

    Thank you. Helps a lot :)

    
    
34. on May 28, 2008 at 3:35 pm NG

    How could i get all the data from all the table of the specific database?

    Thanks a lot in advance!

    
    
35. on June 5, 2008 at 9:21 pm ananth

    Querry to fetch all tables in a database:

    select * from sysobjects where type=’u’
    ————————————————–
    Querry to fetch all procedures in a database:

    select * from sysobjects where type=’p’

    Regards
    Rap…

    
    
36. on June 5, 2008 at 10:41 pm sharath

    to view all tables……

    SELECT * FROM TAB;

    this will work…….

    
    
37. on June 6, 2008 at 12:53 pm pushparaj

    Dear sir,

    How do i get all databases in sql server

    Regard’s

    pushparaj

    
    
38. on June 18, 2008 at 7:02 pm Koushal

    Dear Friends,

    I want to know how to get all table name from particular data base in sql server 2005.

    Thanks.

    
    
39. on June 21, 2008 at 2:33 am Gaurav

    Hi ,

    I am creating an application where I have to list all the servers and on selecting any server the combobox should dynamically have all the databases that are present on that server so that the user can select any one of them. Can anyone please help. its urgent!!!

    thanks,
    Gaurav

    
    
40. on June 23, 2008 at 2:38 pm Aditya

    @Kaushal
    I want to know how to get all table name from particular data base in sql server 2005.

    select * from sysobjects where xtype=’U’

    
    
41. on July 9, 2008 at 12:20 pm Rekha

    if i want to check the primary and foreign key relations of particular table is it possible….if there is any solution plz tell me……..

    thx in advance

    Regards,
    Rekha…

    
    
42. on July 10, 2008 at 10:11 am Tony Philip

    I need to display only user defined tables from the current database.

    
    
43. on July 10, 2008 at 6:12 pm Abhijit

    Hi Tonny,
    U will get the user defined tables using any query below.

    select * from sys.tables
    select * from INFORMATION_SCHEMA.TABLES
    select * from sys.objects WHERE type =’U’

    
    
44. on July 23, 2008 at 6:52 pm Bob

    Hi everyone, I am new here.

    I am trying to get a list of all of the tables in a DB and the size/# of rows in each table.

    Does anyone know how to get this data?

    Thanks in Advance for your help!

    
    
45. on July 26, 2008 at 6:47 pm ahila

    dear sir,
    i want to diplay all databases name in sql2000.

    
    
46. on July 26, 2008 at 10:11 pm Imran Mohammed

    @ahila

    use master
    Select name from sysdatabases

    hope this helps,
    Imran

    
    
47. on August 1, 2008 at 8:20 am dumdum

    how i can see all tables in sqlserver2000;

    thanks..

    
    
48. on August 1, 2008 at 3:05 pm martin joseph

    /*
    USE YourDBName
    GO
    SELECT *
    FROM sys.Tables

    */

    i used the above code but shows error stating
    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name ’sys.tables’.

    is it dude to any syntax error
    ???

    
    
49. on August 2, 2008 at 3:16 am Imran Mohammed

    @dumdum

    Script1 :This will list all the system tables and userdefined tables in the database. ( meaning all the tables in the database)

    use master
    create table #temp1 (id int identity , name)
    insert
    SELECT name FROM sysobjects where type = ‘S’ or type = ‘U’

    Script2: If you want to list all the system and user defined tables in all the database at a time, then you can use this script,

    I am sure there must be a another good way of doing this,

    use master
    create table #temp1 (id int identity , name varchar(128))
    go
    create table #temp2 ( table_name varchar(128), db_name varchar(128) )
    go
    insert into #temp1
    SELECT name FROM sysdatabases
    go
    declare @var int
    declare @db_name varchar(128)
    declare @cmd varchar(1280)
    set @var = 1
    while @var <= (select count (1) from #temp1)
    begin
    select @db_name = name from #temp1 where id = @var
    set @cmd = ‘SELECT name , db_name = ‘+””+@db_name+””+’ FROM ‘+ @db_name+’..sysobjects where type = ‘’s”or type = ”U”’
    insert into #temp2 exec (@cmd)
    set @var = @var+1
    end
    select table_name As ‘Table Name’ , db_name As ‘Database Name’ from #temp2 where table_name not like ‘#temp1%’ and table_name not like ‘#temp2%’
    drop table #temp1
    drop table #temp2
    go

    Hope this helps,
    Thanks,

    
    
50. on August 2, 2008 at 3:21 am Imran Mohammed

    @martin joseph

    You can execute that query in Sql Server2005, it will give you all userdefined tables. Your query is absolutely correct.

    In Sql Server 2000 I dont think we have an object by name sys.tables that is why we canno use that query and that is why it is returning errors.

    There is something similar if you might want to use it, its sp_tables, it will list all the tables and views along with some good information in the particular database.

    Hope this helps,
    Imran.

    
    
51. on August 4, 2008 at 1:06 pm Surya

    Can I retrieve all tables from all database in SQL Server?
    If yes please give me the query.

    
    
52. on August 4, 2008 at 5:09 pm Viny

    select * from sysobjects will give you all the tables in the database.

    
    
53. on August 6, 2008 at 5:07 pm Binu

    Can anyone please tell me how to get all the tables which has same column name in a database

    
    
54. on August 11, 2008 at 10:53 am martin joseph

    thanx imran for ur reply…

    it was very helpful … i might have wasted a lot of time
    using the code in ” sql server 2000″

    
    
55. on August 12, 2008 at 9:50 pm Venkat

    Hi friends,

    these are also some use full
    please check

    Querry to fetch all user tables in a database:
    use yourdbname
    select * from sysobjects where type=’s’ order by name

    Querry to fetch all system tables in a database:
    select * from sysobjects where type=’U’ order by name

    Querry to fetch all procedures in a database:
    select * from sysobjects where type=’p’

    Querry to fetch all functions(userdefined or system) in a database:
    select * from sysobjects where type=’fn’

    Cheers,
    Venkat

    
    
56. on August 12, 2008 at 10:57 pm Venkat

    Hi friends,

    This is very use full for finding the size, table, find …

    query:
    exec sp_spaceused [tablename]

    result:
    name rows reserved data index_size unused
    tablename 145 888 KB 248 KB 16 KB 624 KB

    Cheers,
    venkat919

    
    
57. on August 12, 2008 at 11:19 pm Venkat919

    Hi Friends,

    After i did the exercise

    to get the all user tables from database:
    — this will gives the one extra table it is “dtproperties”

    1. select * from sysobjects where type=’U’ order by name

    — this will gives the exact user tables from database

    2.
    SELECT * FROM sysobjects
    WHERE
    type=’U’
    and
    name != ‘dtproperties’
    ORDER BY [name]

    Cheers,
    Venkat

    
    
58. on September 1, 2008 at 5:00 pm Jayakumar

    Dear Friends

    How To Find Particular Table stay Which Database ?

    
    
59. on September 19, 2008 at 9:18 pm RAYMOND KNIGHT

    I want to build an if statement that will create a table with different information in it based on the database that is currently in USE (the database name in the USE statement). Different databases USE the same table but have different information in them.

    
    
60. on September 30, 2008 at 9:57 am santosh

    Hi Pinal,
    i want to fetch all databases and tables from sql server database, mysql database, oracle database and ms-access database. can u suggest me how to do this. if possible post an example for each. i want to bind all those details to a treeview in asp.net.

    Thanks in Advance,
    Santosh.

    
    
61. on October 2, 2008 at 7:25 am whalon

    Does anyone have any thoughts on how to accomplish the following? I would like to query a SQL2005 db and return the table and column that contains a particular value. Based on what I have read from this site - this seems to be a good start. SELECT * FROM information_schema.Tables

    I would like to add syntax that will find a varchar value and return the table, column, and value itself.

    Thanks for the help.

    
    
62. on October 5, 2008 at 10:05 am Ankur Patel

    Thanks !

    
    
63. on October 9, 2008 at 2:09 am Hosmerica

    I can get a list of all stored procedures that I’m using in my Db via this query:

    SELECT * FROM SYS.PROCEDURES WHERE [Name] LIKE ‘usp%’ ORDER BY [Name];

    (I’m looking for the name to be like ‘usp’ because that’s the prefix to all of the stored procedures I’ve created.)

    What I’m looking for is where the text of the stored procedure is located. For example, I need to drop the use of a table that is causing performance issues (they’re using labor intensive triggers) and want to search the text of all stored procedures I’ve created for the table name so I know which stored procedures need changing. There are over 130 stored procedures and looking through every one of them seems too time consuming.

    It would be nice to run a query that would do the search for me…like this

    SELECT * FROM SYS.SomeSystemTable s INNER JOIN SYS.PROCEDURES p ON s.object_id = p.object_id WHERE [Name] LIKE tblBadTableName%’ .

    Any Ideas?

    
    
64. on October 9, 2008 at 2:13 am Hosmerica

    I aplogize, Pinal, I found the answer within your web site.

    http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/

    
    
65. on October 16, 2008 at 1:59 pm Suba

    Hi,

    Could you please let me know how to retrieve the deleted table list in SQL.

    Thanks,
    Suba

    
    
66. on November 6, 2008 at 9:11 pm Masud

    Hi,
    How can i know that how many times my tables,views and/or functions have been executed or used by the users?Can anybody help me or can give me some idea ,plz?

    
    
67. on November 14, 2008 at 6:52 pm Farhan Ahmed

    Hi,
    Is it possible ???
    how to select data when you dont know the table name, only thing I know is database where the column resides

    like I have to find how many CID columns are in my database and in which table they are resides???

    Please do help me … because I have to work on Large number of database tables ….

    
    
68. on November 21, 2008 at 4:53 pm paresh13

    Hi,

    I want to list of my database tables’ column name with their Datatype.

    Can you tell me how to do that?

    Regards,

    Pooja

    
    
69. on December 2, 2008 at 11:58 pm aalmeida71

    I use this to get all Table and Sprocs on all Database on a server:

    sp_msforeachdb @command1=”USE ?;select name, type from sys.objects where type in (’U', ‘P’) and name not like ‘dt%’ and name not like ‘#%’ order by type”

    Note sp_msforeachdb has a limits the query to 128 digits.

    
    
70. on December 3, 2008 at 2:30 am aalmeida71

    This code shows all the tables and sprocs for each DB, the size of each table int he DB and the DB file sizes:

    sp_msforeachdb @command1=”USE ?;select ‘?’,name,type from sys.objects where type in(’U',’P') and name not like ‘dt%’ and name not like ‘#%’ order by 3″
    , @command2=”exec sp_MSforeachtable @command1 = ‘exec sp_spaceused’”
    , @command3=”exec sp_helpfile”
    GO

    
    
71. on December 6, 2008 at 12:32 am ramesh

    i want default databases in sql server and their tables like EMP table in oracle . plz help me

    
    
72. on December 14, 2008 at 3:54 pm george

    hye,
    i want to modifier a field in a database and this field is related to many database what is the code to do to modifier this field in all database at the same time
    best regards

    
    
73. on December 19, 2008 at 6:23 am JG

    Everyone that contributed to this site has helped me tremendously. Thanks for leaving your informational note. This blog Rocks who do i give the credit to?? thanks again.

    
    
74. on December 22, 2008 at 10:58 am anirudha deshpande

    Hi pinal,

    i need to write the Use statement inside the stored procedure.
    but it throws error.
    Is there any alternate way for this?

    let me make it more clear.

    i have created one stored proc in master database which takes database name as parameter.
    and i want to execute the code on the respective database.

    How can i achieve this?

    Thanks,
    Anirudha ( SQL -Developer)