Journey to SQL Authority with Pinal Dave

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 182 Comments

182 Responses

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

   How do I list all tables on a server?

   
   
   • on December 26, 2009 at 2:07 pm | Reply venkatesh

     select * from sys.tables
     (or)
     –list of all user defined tables
     select * from sysobjects where xtype=’u’

     
     
     • on December 26, 2009 at 2:08 pm | Reply venkatesh

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

       
       
     • on January 5, 2010 at 10:37 am | Reply kalidass

       select * from sys.tables

       error: Invalid object name ’sys.tables’.
       i use 2000 sql serv.

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

   How do I list all databases on a server?

   
   
   • on December 26, 2009 at 2:05 pm | Reply venkatesh

     use master
     select * from sysdatabases

     
     
3. on July 11, 2007 at 4:14 pm | Reply 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 | Reply 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)

   
   
   • on January 5, 2010 at 10:49 am | Reply kalidass

     i can’t do it;
     ====================================
     USE Master
     GO
     SELECT *
     FROM sys.Databases
     GO

     error: Invalid object name ’sys.Databases’.
     ====================================
     select * from sys.tables

     error: Invalid object name ’sys.tables’.
     =====================================

     i use 2000 sql serv.

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

   SELECT *
   FROM information_schema.Tables

   This will display the list of table in the database

   
   
   • on December 18, 2009 at 3:56 pm | Reply ravi kore

     thnks for query on sqlauthority

     
     
   • on December 29, 2009 at 8:23 am | Reply Ram Kumar

     I Think it is not working in SQL SERVER 2005.

     
     
     • on December 29, 2009 at 3:58 pm | Reply Pinal Dave

       Hello Ram,

       Its working. INFORMATION_SCHEMA views are SQL-92 ANSI standard views and will work in future versions also. This view returns only the user tables on which you have SELECT permission. Let us know the issue you are facing.

       Regards,
       Pinal Dave

       
       
   • on January 5, 2010 at 10:51 am | Reply kalidass

     thanks
     i got it
     but

     select * from sys.tables

     error: Invalid object name ’sys.tables’.
     i use 2000 sql serv.

     
     
   • on January 13, 2010 at 12:11 pm | Reply ram

     Hi

     Thank’ sir

     
     
6. on July 30, 2007 at 12:37 am | Reply 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 | Reply Kumar Biswas

   I want to view all the table in a database

   
   
   • on January 5, 2010 at 10:56 am | Reply kalidass

     USE
     SELECT *
     FROM information_schema.Tables where TABLE_TYPE=’BASE TABLE’

     
     
8. on August 3, 2007 at 2:34 pm | Reply 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 | Reply Lakshmi

   Thank you

   
   
10. on September 18, 2007 at 5:42 am | Reply 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 | Reply 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 | Reply rama

    I want to view all the table in a database

    
    
13. on December 14, 2007 at 1:50 am | Reply 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 | Reply Srinivas

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

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

    Hi,

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

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

    Mani,

    Please read the post on top.

    Regards,
    Pinal

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

    Thank U……..

    Regards,
    Mani……..

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

    i want to see all tables in sqlserver2000;

    
    
19. on January 4, 2008 at 5:05 pm | Reply 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 | Reply Princes

    Thank you…very much…its very helpful …

    Thankyou & Regards,
    Princes

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

    Works like a dream!! Thank You

    
    
22. on January 21, 2008 at 3:01 pm | Reply 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 | Reply 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 | Reply 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 | Reply pRATEEK

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

    
    
26. on February 7, 2008 at 6:39 pm | Reply 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 | Reply 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 | Reply 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 | Reply raja

    how many user using my db
    (same user) ?

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

    thaink you
    what about
    only user tables?

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

    I got much information regarding the database list of objects

    Thank you
    Harinath

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

    I am not getting abt master table.

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

    Thank you. Helps a lot :)

    
    
34. on May 28, 2008 at 3:35 pm | Reply 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 | Reply 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 | Reply sharath

    to view all tables……

    SELECT * FROM TAB;

    this will work…….

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

    Dear sir,

    How do i get all databases in sql server

    Regard’s

    pushparaj

    
    
38. on June 18, 2008 at 7:02 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Tony Philip

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

    
    
43. on July 10, 2008 at 6:12 pm | Reply 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 | Reply 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 | Reply ahila

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

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

    @ahila

    use master
    Select name from sysdatabases

    hope this helps,
    Imran

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

    how i can see all tables in sqlserver2000;

    thanks..

    
    
48. on August 1, 2008 at 3:05 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Viny

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

    
    
53. on August 6, 2008 at 5:07 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Jayakumar

    Dear Friends

    How To Find Particular Table stay Which Database ?

    
    
59. on September 19, 2008 at 9:18 pm | Reply 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 | Reply 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 | Reply 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 | Reply Ankur Patel

    Thanks !

    
    
63. on October 9, 2008 at 2:09 am | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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)

    
    
75. on January 21, 2009 at 3:31 am | Reply Marcel

    Grat tips, tanks a lot !!

    
    
76. on January 29, 2009 at 6:39 pm | Reply pinaldave

    Good Participation Friends.
    I will reply all the questions asked.

    Please keep it going.

    Regards,
    Pinal Dave

    
    
77. on February 3, 2009 at 6:31 pm | Reply Krishna

    Hi Mr.Pinal,

    In SQL server 2000 i got the error message.

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

    Please help on it.

    Regards,
    Krishna

    
    
78. on February 3, 2009 at 6:54 pm | Reply Swetha

    What should i do to become the future C.E.O of S.Q.L

    
    
79. on February 4, 2009 at 10:57 am | Reply Imran Mohammed

    @ Krishna,

    In SQL Server 2000, there is no view called ’sys.tables’. This view is only in SQL Server 2005.

    You can use below quiries to get user defined tables names in SQL Server 2000.

    select * from sysobjects where table = ‘u’

    or

    select * from information_schema.tables

    Regards,
    IM.

    
    
80. on February 5, 2009 at 7:43 pm | Reply Krishna

    HI Imran Mohammed,

    Thank you for your information…..

    Regards,
    Vidhya

    
    
81. on February 6, 2009 at 1:38 am | Reply Will Steele

    In SQL Server 2000 this works well to return all User Tables:

    SELECT Name
    FROM FOTNAudit.dbo.sysobjects
    WHERE xtype = ‘U’
    ORDER BY Name

    
    
82. on February 6, 2009 at 1:38 am | Reply Will Steele

    FOTNAudit is a specific db name. Replace with whatever you need to use.

    
    
83. on February 9, 2009 at 12:46 pm | Reply Jignesh Patel

    Hello sir

    How can get all the table list from Sql Server 2005.

    Thanks & Regards
    Jignesh Patel

    
    
84. on February 10, 2009 at 7:38 am | Reply Imran Mohammed

    @Jignesh Patel

    This script will give you tables in all database ( meaning in whole SQL Server )

    DECLARE @CMD VARCHAR(1000)
    create table #TableNames ( DatabaseName sysname, TableName sysname)
    SET @CMD = ‘USE [?]; SELECT DB_NAME()DATABASE_NAME, NAME FROM SYS.Tables’
    insert into #TableNames EXEC SP_MSFOREACHDB @CMD
    SELECT * FROM #TableNames — Where DatabaseName not in ( ‘master’,'msdb’, ‘tempdb’, ‘model’ )
    drop table #TableNames

    If you dont want system databases table names, then you need to remove — ( remove comment symbol) from above script, otherwise it will include system database table names also.

    Regards,
    IM.

    
    
85. on February 10, 2009 at 11:43 am | Reply Jignesh Patel

    thanks Imran

    Its working…

    
    
86. on February 13, 2009 at 1:00 pm | Reply Girish Vikraman

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

    Advance thanks for the help…. :)

    
    
87. on February 18, 2009 at 6:07 pm | Reply rashmi

    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

    
    
88. on February 19, 2009 at 2:48 am | Reply Sandeep

    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]

    
    
89. on February 26, 2009 at 5:10 am | Reply Minendra

    Thanks. It was a great help.

    
    
90. on March 4, 2009 at 2:12 am | Reply pacolgate

    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?

    
    
91. on March 4, 2009 at 2:13 am | Reply pacolgate

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

    
    
92. on March 6, 2009 at 11:35 am | Reply hitesh

    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

    
    
93. on March 10, 2009 at 10:54 am | Reply shine M m

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

    Can any one help me

    
    
94. on March 12, 2009 at 5:20 pm | Reply Abbhas

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

    
    
95. on March 15, 2009 at 11:02 am | Reply lakshmi

    I want queries about sql server 2005

    
    
96. on March 19, 2009 at 7:55 pm | Reply Trilok

    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

    
    
97. on March 30, 2009 at 1:15 pm | Reply vipin katre

    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…..

    
    
98. on March 30, 2009 at 3:14 pm | Reply Tejas Shah

    Hi Vipin,

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

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

    Thanks,

    Tejas

    
    
99. on March 31, 2009 at 7:01 pm | Reply Ambanna

    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

    
    
100. on April 1, 2009 at 6:18 am | Reply Imran Mohammed

     @ 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 : http://www.devx.com/getHelpOn/10MinuteSolution/20551

     It has good explanation.

     Regards
     ~ IM

     
     
101. on April 1, 2009 at 12:56 pm | Reply vipin katre

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

     
     
102. on April 1, 2009 at 7:36 pm | Reply Ambanna

     Thank you very much Imran

     U’ve save a lot of my time

     thanx once again

     
     
103. on April 4, 2009 at 12:47 pm | Reply vipin katre

     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…….

     
     
104. on April 6, 2009 at 7:19 pm | Reply shivam

     I get this error:

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

     
     
105. on April 7, 2009 at 5:03 am | Reply Imran Mohammed

     @Shivam

     Script is to be executed in sql server 2005.

     cannot be executed SQL Server 2000

     ~ IM

     
     
106. on April 7, 2009 at 1:22 pm | Reply ace

     thanks a lot buddy

     
     
107. on April 10, 2009 at 11:24 am | Reply Sobia

     Very informative thread…
     Can anybody tell me how may I get the constranits if any applied on the table’s column?

     Thanks in anticipation for the help…

     
     
108. on April 30, 2009 at 8:00 pm | Reply Chip Allessi

     I am trying to select and id from sysobjects in another database in a stored procedure using SqlServer 2005.

     I can not use a use databaseB statement inside of a stored
     procedure and if I try to run this from databaseA

     Select id from databaseB..sysobjects where …

     I get a null id.

     Any help is appreciated.

     Thanks
     Chip

     
     
109. on May 1, 2009 at 11:23 am | Reply vbs

     hi all
     is there is any sql query which gives all triggers in a database?

     
     
110. on May 15, 2009 at 4:39 am | Reply Ruwan Pathiranage

     Try this

     select * from sys.triggers

     select * from sys.triggers WHERE name like ‘%dup%’

     Ruwan

     
     
111. on May 15, 2009 at 7:50 am | Reply Imran Mohammed

     @Ruwan,

     Just to add one more cent to your answer, That will give you list of DML Triggers. But you cannot get list of DDL triggers through that script.

     Because the question was asked to find triggers in database, your solution works perfectly fine, no doubt about it.

     But if we are talking about in general, DDL triggers are defined at server level but not database level, so sys.Triggers will not provide information about DDL triggers.

     IM

     
     
112. on May 20, 2009 at 7:47 pm | Reply Rohitas Gangwar

     Hi Pinal,

     Can you explain me what is sql server 2005 client? As it is free but what else I required. Do I need SQL Server 2005 enterprise?

     As I never worked with Sql Server, I always worked with oracle.
     Please help me out

     Thanks in Advance.

     Rohitas Gangwar

     
     
113. on June 4, 2009 at 8:33 pm | Reply chaya

     Hi I am Looking for a Query that will list out tables by a keyword

     I use to run following Query in Oracle for teh same purpose:–

     Select * from tab where tname like ‘%BANK%’

     So, this query will list out all the table names with ‘BANK’

     I tried to run a similar query in SQL serever 2005:–

     select * from sys.TABLES WHERE NAME like ‘%BANK%’

     It didn’t return any results. Also, I want my Query to search table names from all Database( I have tables with the keyword mentioned in the database)

     
     
114. on June 5, 2009 at 1:18 am | Reply Imran Mohammed

     @Chaya,

     You need to understand that, there is huge difference between oracle and SQL Server naming standards,

     In Oracle, we will have one database and many schemas, so if you run below script in oracle in a database, it will search this table in all schemas but only in that one database, since ORACLE has only one database per server, there will be no problem.
     Select * from tab where tname like ‘%BANK%’

     BUT, SQL Server has one more layer to it, In One SQL Server instance, you can have multiple databases, and in one database you can have multiple schemas.

     ORACLE :
     STAGE1 : SERVER
     STAGE2: ONLY 1 DATABASE
     STAGE3: MORE THAN ONE SCHEMA IN DATABASE

     If you execute any script at serve level, because there is only one database, it can give you results.

     SQL SERVER:
     STAGE1 : SERVER
     STAGE2: MORE THAN 1 DATABASE
     STAGE3: MORE THAN ONE SCHEMA IN EACH DATABASE

     If you execute any script at server level, it sees many databases, and if you do not specify the database name, by default it will search in masterdb, which is not the database you want to search in,

     So in SQL Server you first need to connect to the right database and then execute your sql script,

     use database_name
     select * from sys.TABLES WHERE NAME like ‘%BANK%’

     ~ IM

     
     
115. on June 8, 2009 at 7:58 pm | Reply chaya

     Hey, thanks Imrqan you resolved my doubts.. Henceforth I will make sure that I write querirs as per the database connected to.

     
     
116. on June 9, 2009 at 7:29 pm | Reply Eugene

     Hi Pinal,
     Sometime I need to find some text in the whole database to detect the table and the field which contains this kind of information.
     So I have an idea to create a SELECT from all tables, and then unload it in text file. I decided to do it in XML format:
     So I open cursor that passes all tables and does SELECT from them.

     declare @tableName varchar(100)

     DECLARE tables_cursor CURSOR FOR
     SELECT name
     FROM sys.Tables

     OPEN tables_cursor

     FETCH NEXT FROM tables_cursor
     INTO @tableName

     WHILE @@FETCH_STATUS = 0
     BEGIN

     EXEC (‘SELECT * FROM ‘ + @tableName + ‘ FOR XML AUTO’)

     FETCH NEXT FROM tables_cursor
     INTO @tableName
     END

     CLOSE tables_cursor
     DEALLOCATE tables_cursor

     It gets all-tables content as many selects, so I can’t mark it and copy-paste in txt file to search in.
     How can I get this output in file or in console ?

     Or, may be there is another solution of my problem?

     
     
117. on June 9, 2009 at 9:13 pm | Reply mark

     I want to know how to list all my user on my SQL 2005 database ? Can someone help me out ?

     Thanks in advance

     
     
118. on June 10, 2009 at 9:23 pm | Reply Eli

     @Mark,

     If you are using SQL Server 2000, you can use master..syslogins. For SQL Server 2005, you can use sys.syslogins.

     
     
119. on June 18, 2009 at 11:32 am | Reply Seonghun.choi

     Thank you~!

     I can make my Table list… so easy..

     
     
120. on June 18, 2009 at 8:25 pm | Reply angela

     How do I find out the server name. I have the database name with DB_Name(). There’s got to be something out there, I can’t find it.

     
     
121. on June 22, 2009 at 11:05 am | Reply srinivas

     Query or stored procedure to List of all the databases to which user has access in sql server 2005.

     
     
122. on July 22, 2009 at 3:09 pm | Reply Dhinesh

     Hi,
     one of the previous command has the statement like

     if we run this bellow query it displays the list of tables in the database

     SELECT * FROM TAB

     It does not works in SQL SERVER 2005.

     select * from sys.tables
     only used to display the list of tables in the database.

     With Regards,
     Dhinesh

     
     
123. on July 27, 2009 at 9:06 am | Reply www.thingsweloveothate.com

     Is there a way to list all tables in the SERVER (not just in the current database)?

     I am in @Database1 and need to know if a certain table/column exists in @Database2.

     While in a SPROC in @Database1, how do I return a bit to tell me if a certain table exists in the other database?

     
     
124. on July 28, 2009 at 1:53 pm | Reply zee

     hi ,

     please tell me how to genrate each IDs privileges

     regards
     Zee

     
     
125. on August 26, 2009 at 6:06 pm | Reply sambarta

     Hi Pinal,

     i have 400 tables in a database. I have a requirement which need exhaustive document showing all the dependencies and information about all tables in a database(user tables). Please let me know if there is anyway to do this.

     
     
126. on August 26, 2009 at 8:02 pm | Reply Sourabh Sachdeva

     you can see all table information by

     select * from sys.tables

     or

     select * from infomation_schema.tables

     or

     sp_tables

     you can see all procedures by:

     select * from sys.objects where type=’p’

     you can see all databases by:

     sp_databases

     you can see all indexes by:

     select * from sys.indexes

     
     
127. on September 7, 2009 at 12:43 pm | Reply Mani

     How to get tables from sqldatabase

     
     
128. on September 7, 2009 at 3:45 pm | Reply Suyog

     Dear sir,
     i want to know how to see the structure of all tables…..

     sp_help tablename gives the output only for 1 table..i have 200 tables in my db.. should i hit the query for each table.. or is there any other solutuion for it..

     plz reply…
     thanx in advance.

     
     
129. on September 7, 2009 at 6:41 pm | Reply Sourabh Sachdeva

     Hi Suyog,

     You can see the structure of all tables. by using….

     select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)

     
     
130. on September 8, 2009 at 10:12 am | Reply suyog

     Hi Sourabh,

     UR answer is ohkk.. but i need INDEX constaints also in it.. so wat should i do??
     plz reply..

     
     
131. on September 8, 2009 at 5:17 pm | Reply Manshi t

     Great man!

     
     
132. on September 8, 2009 at 6:32 pm | Reply pinaldave

     Hi Mani,

     Please read original post as it contains answer to your question.

     USE YourDBName
     GO
     SELECT *
     FROM sys.Tables
     GO

     Kind Regards,
     Pinal

     
     
133. on September 9, 2009 at 2:04 pm | Reply Suyog

     Dear Sir,
     that query is not showing me index ,constraints,columns details…
     is there any other way..except sp_help tablename..

     can we go for sp_msforeachtable???
     can u plz provide exact query for same..

     
     
134. on September 9, 2009 at 3:40 pm | Reply Sourabh Sachdeva

     hi suyog,

     If you want to see structure of all tables you can use:

     select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)

     If you want to see index details then you can use:

     select name,type_desc from sys.objects where type=’PK’

     
     
135. on September 10, 2009 at 2:59 pm | Reply Sourabh Sachdeva

     Hi suyog,

     Not a proper way but i think u can use

     declare @tableName as varchar(100)
     declare @countValues as int
     select @tableName=name from sys.tables
     select @countValues=COUNT(*) from sys.tables
     declare @startvalue as int
     set @startvalue=1
     while @startvalue<@countValues
     begin
     declare @exe as varchar(100)
     set @exe='sp_help '+@tableName+''
     print @exe
     exec(@exe)
     set @startvalue=@startvalue+1
     end

     after executing these statements you can see all tables structure info. If you want to combine all data into single resultset you can use temporary tables etc..

     
     
136. on September 11, 2009 at 12:02 am | Reply Ambanna

     you can use this,
     sp_msforeachtable ’sp_help [?]‘

     
     
137. on September 23, 2009 at 6:10 pm | Reply NebWright

     Appreciate your blog. Does anyone have any ideas on how to lookup the source table/column associated with a column in a given view? I’m looking for a way to map a column from a view (that shows up in syscolumns) back to the originating table and column that the view column is based on.

     
     
138. on October 1, 2009 at 9:58 am | Reply anurag

     how do i know the structure of table like in mysql or oracle viz desc.i uses sp_privileges but it return the result that i dont want. i want to know the data type and its size

     
     
139. on October 4, 2009 at 12:56 am | Reply zee

     zee

     Select * from table name
     where Fname like ‘%a%’ and Fname like ‘_li’

     i hope you like it

     
     
140. on October 4, 2009 at 12:59 am | Reply zee

     zee

     Select RNo+2 As RNo,FName From DBtab
     Where FName=’lia’

     
     
141. on October 4, 2009 at 5:12 pm | Reply mohanty

     Thanks Sir…
     Big help

     
     
142. on October 9, 2009 at 6:46 pm | Reply kapil dhariwal

     select * from sysobjects where xtype=’U’

     
     
143. on October 30, 2009 at 10:18 am | Reply Lakshmi

     How to find the number of records from all the tables in sql 2005?

     
     
144. on October 31, 2009 at 6:39 pm | Reply Sourabh Sachdeva

     Hi Lakshmi,

     I think there is no any build in table or any built in function in sql server through you can count number of records in all the tables.

     But i have created a logic through you can get number of records from all tables….

     You can use this:

     declare @dynamicqry as varchar(1000)
     create table temp(tid int)
     declare @startvalue as int
     declare @endvalue as int
     set @startvalue=1
     select @endvalue=count(*) from INFORMATION_SCHEMA.TABLES
     while @startvalue<@endvalue
     begin
     declare @tablename as varchar(100)
     select @tablename=Table_name from (select Table_name,ROW_NUMBER() over(order by table_name asc) 'RowNum' from information_schema.tables) tt where RowNum=@startvalue
     set @dynamicqry='insert temp select count(*) from '+@tablename
     print @dynamicqry
     exec(@dynamicqry)
     set @startvalue+=1
     end
     select SUM(tid) from temp

     
     
145. on November 4, 2009 at 1:57 am | Reply Chaya

     Hello,

     I tried to run the following query for display columnames in Ascending order from table Item:-

     exec sp_columns Item order by Column_name Asc

     I get the following error:-
     Msg 156, Level 15, State 1, Line 1
     Incorrect syntax near the keyword ‘order’.

     How shhould I construct the query in order to get the desired results?

     
     
146. on November 4, 2009 at 5:59 pm | Reply Ambanna

     Hi Chaya,

     you can use the following query.

     select *from information_schema.columns where table_name=’ITEM’ order by column_name

     
     
147. on November 5, 2009 at 9:49 am | Reply vidhyasri

     Hi

     I am new to this SQL.. I want to know how can get the tables from data base..

     Can you tell me in a easy way please… I need it as soon as possible to resolve an issue :(

     Thanks
     Vidhyasri M

     
     
148. on November 5, 2009 at 5:55 pm | Reply Ambanna

     Hi Vidhyasri,

     you can use any of this query to get all the tables from a particular database

     select * from INFORMATION_SCHEMA.TABLES
     or
     select * from sysobjects where xtype=’U’
     or
     select * from sys.tables

     Regards,
     Ambanna Yatnal

     
     
149. on November 6, 2009 at 12:56 pm | Reply Sourabh Sachdeva

     Hi Vidhyasri,

     you can use:

     sp_tables

     OR

     you can use:

     select * from information_schema.tables

     OR

     You can use:

     select * from sys.tables

     OR

     you can use:

     select * from sys.objects where type=’U’

     Note: type=’U’ not use xtype

     So all these are different way of getting all tables from sql server.

     
     
150. on November 8, 2009 at 8:15 am | Reply praveen

     what is Invalid object name ’sys.tables’. in sql server 2000???

     can u plss help me!!!!!!!!!

     
     
     • on January 20, 2010 at 4:26 pm | Reply ivan.peter

       use this code:
       SELECT * FROM INFORMATION_SCHEMA.TABLES

       
       
151. on November 18, 2009 at 4:26 pm | Reply arul

     reply by example

     
     
152. on November 19, 2009 at 1:29 pm | Reply dharma

     how can i display the no of tables present in the database and also displays the table name present in the database

     
     
153. on November 25, 2009 at 3:59 am | Reply Chaya

     I have Query which requires me to display ‘Active’ or ‘Inactive’ in ‘Status’ Column based on the following Condition:-

     IF Todate IS Null or Todate>=Getdate() then status = ‘ACTIVE’
     If Todate<Getdate() then status = 'INACTIVE'

     Whereas 'Status' is not a column by itself in Agentownership table. Above condition needs to be added in the following query:-

     Select AO.OwnerfirstName, AO.OwnerLastName, P.PhoneNumber, AO.FromDate
     From AGENTOWNERSHIP AO
     Left Join Agent A on AO.AgentID = A.AgentID
     Left Join Phone P on P.PhoneiD = AO.PhoneiD

     I have the following Columns in my AgentOwnership table:-
     AgentOwnershipID
     AgentID
     OwnershipRoleID
     OwnerFirstName
     OwnerLastName
     PhoneID
     AddressID
     FromDate
     ToDate
     OwnerDateofBirth
     OwnerDriversLicense
     OwnerDriversLicenseStateId
     OwnerEmailAddress

     
     
     • on November 25, 2009 at 3:59 am | Reply Chaya

       Help please! I am still trying to complete the query

       
       
154. on November 25, 2009 at 7:15 pm | Reply Brian Tkatch

     @dharma

     Look at INFORMATION_SCHEMA.TABLES or sys.tables.

     
     
155. on November 25, 2009 at 7:18 pm | Reply Brian Tkatch

     @Chaya

     Just add a CASE statement to the query:

     CASE
     WHEN Todate < Getdate() THEN 'INACTIVE'
     ELSE 'ACTIVE'
     END Status

     
     
156. on December 4, 2009 at 12:51 pm | Reply raje

     hi
     In all data bases have a common table ah?

     
     
157. on December 7, 2009 at 5:51 pm | Reply Tinku

     Hi.
     i want a SQL querry to list the tables in a schema in DB2. can anyone help me!! Thanks friends!!

     
     
158. on December 10, 2009 at 10:25 am | Reply chong

     Hi,

     I m converting my .net application from Mysql to Ms sql

     server 2005 but the queries just don’t go well. eg.

     MS sql server gives me error (SELECT * FROM Employee).

     Thanks

     
     
159. on December 22, 2009 at 5:25 pm | Reply Bertus

     Hi

     I’m using stored procedures to create temp tables with info and use it in SQL reporting services.
     Now I have major collation issues making my life difficult.

     How can I avoid collation issues in my stored procedures, or how can I change the collation of all tables and all columns in a database.

     I get the following error

     Msg 468, Level 16, State 9, Procedure BISSystemsOverdue, Line 103
     Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

     
     
160. on December 23, 2009 at 10:04 am | Reply Pinal Dave

     Hello Bertus,

     Different collation of working database and tempdb could cause the collation conflit when using temp tables. To resolve this either specify the collation while creating the temp table or change the default collation of tempdb.

     Regards,
     Pinal Dave

     
     
161. on December 30, 2009 at 10:01 am | Reply hitesh

     its very useful info.

     Thanks

     
     
162. on December 30, 2009 at 7:55 pm | Reply Mike

     Hi,

     How can I get all the databases on the sever and thier location(file name). i tried too many thing but I couldnt get it.

     Thanks

     
     
163. on January 4, 2010 at 6:30 pm | Reply LOKESH

     Hi all,

     I wanted where condition with two paramters which works in oracle but i wanted to execute in sql server 2000 which gives me error.
     Kindly check the query

     select * from t1 where (column1,column2) in (select column1,column2 from t2)

     i require same query in sql server.If any have ideas on this plz forward to my mail id

     thanks and request,
     lokesh

     
     
164. on January 5, 2010 at 1:03 am | Reply Pinal Dave

     Hi Lokesh,

     Write as below:

     select t1.* from t1 INNER JOIN t2
     ON t1.column1 = t2.column1
     AND t1.column2 = t2.column2

     or another way:

     select * from t1
     where column1 in (select column1 from t2)
     AND column2 in (select column2 from t2)

     Regards,
     Pinal Dave

     
     
165. on January 6, 2010 at 6:08 pm | Reply Mike

     How can I get all the databases on the sever and thier location(file name). i tried too many thing but I couldnt get it.

     
     
166. on January 6, 2010 at 11:32 pm | Reply Tom

     Looks like some good information here. I’m trying to create two combo boxes. The first one would show all the SQL servers (so I wouldn’t have to program to a specific server) – so I could easily compile the program for our clients. Once a server is selected, then show only those databases that contain a specific table name (to show only those databases that apply to my program) – even the first combo box could do this as well – only showing those servers that have any database that contains a specific table name.

     I’m using Visual Studio 2008 and have a dataset in the program. Is there an easy way to update that with the selections from these dropdown boxes?

     Thanks in advance for any help.

     
     
167. on January 7, 2010 at 12:47 am | Reply Pinal Dave

     Hi Mike,

     Use the below script:

     Use tempdb
     GO
     SELECT TOP 0 * INTO DatabaseFiles
     FROM sys.database_files

     ALTER TABLE DatabaseFiles
     ADD DatabaseName varchar(255)

     EXECUTE sp_msforeachdb ‘INSERT INTO DatabaseFiles SELECT *, ”[?]” FROM [?].sys.database_files’

     SELECT DatabaseName,[name] as [FileName],physical_name as FilePath, * from DatabaseFiles

     Regards,
     Pinal Dave

     
     
168. on January 15, 2010 at 9:10 pm | Reply Shree

     Hi Pinal,

     Am working on scripts to help me in daily monitoring tasks..

     I want to get the database names, its file names, total size and available space..till now am able to get all the bits except for the last one
     —————————————————————————
     SELECT a.name as [DBname], b.name AS ‘File Name’ , case when b.type=0 then (select ‘Data’) else ‘Log’ end as [File_type] ,b.physical_name AS ‘Physical Name’, b.size/128 AS ‘Total Size in MB’,

     b.size/128.0 – CAST(FILEPROPERTY(b.name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’

     FROM sys.databases a left outer join sys.master_files b

     on a.database_id = b.database_id
     —————————————————————————
     This gives me list of everything what i want on that server but regarding “available space” it can only retrieve for the current database files..I want that for all the database files on that server..

     Looking forward to your reply..

     Thanks
     Shree

     
     
169. on February 4, 2010 at 7:09 pm | Reply silambarasan

     how do i select all the records in a particular database using a particular in where condition..i had a commom column Eldate in all tables……..