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 (http://blog.SQLAuthority.com) , BOL

About these ads

445 thoughts on “SQL SERVER – 2005 List All Tables of Database

  1. 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/

  2. 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)

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

    • Hi.. Mr.Pinaldave,

      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 [email removed]

      Regards,

      Vinosh
      [email removed]

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

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

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

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

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

    • 1) select distinct type from sysobjects.
      2) using for user table use
      select distinct type from sysobjects type=’u’
      for procedures type=’p’ etc….

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

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

    • u can get all the tables by using the comman

      select * from sys.objects where type=’u’ (in sql server)
      and
      select * from tab (in oracle)

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

  11. @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’

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

  13. 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’

  14. 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!

  15. /*
    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
    ???

  16. @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,

  17. @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.

  18. thanx imran for ur reply…

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

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

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

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

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

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

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

  25. 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?

  26. 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?

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

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

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

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

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

  32. 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)

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

  34. @ 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.

  35. @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.

    • 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

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

  37. 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]

  38. 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?

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

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

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

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

  43. @ 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

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

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

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

  47. @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

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

  49. 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)

  50. @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

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

  52. 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?

  53. @Mark,

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

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

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

  56. 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?

    • if exists(select * from Database2..information_schema.tables
      where table_name='your_table')

      select ‘table exists on other database’

      else

      select ‘table doesn’t exist on other database’

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

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

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

  60. 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)

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

  62. 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’

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

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

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

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

  67. 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?

  68. Hi Chaya,

    you can use the following query.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  83. I am looking for a script that goes a step beyond this. I need a script that will read all of the columns in a table, and output % populated (%0s for numbers or nulls for char/varchar), min/max ranges and date ranges. I used to have one, but it has been a few years, and I can’t locate it. We used to use it as a great way of validating a client’s data. We would use the client’s data to perform audits, and we had to make sure we had the right date ranges, adequately populated key fields (things like Invoice Number or Check Date). It would allow us to throw out totally null columns, or alert us to errors or invalid exports/imports (ETL process) such as importing a field as the wrong type, causing valid values to be left out on import.

    I am in a situation again where I could really use such a script.

    I have been searching for something like this again for several days now, to no avail. Any help along these lines would be greatly appreciated!

  84. Hello Keerthi,

    If you mean to get data and log files of all databases then use the below statement:

    sp_msforeachdb ‘select * from ?.dbo.sysfiles’

    You can also insert this result into a temporary table for better readability.

    Regards,
    Pinal Dave

  85. Hi,

    I’m trying to create a table from a query, but want the
    Query Name to be entered as a parameter.
    Is there a way to do that?

    Looking forward to your reply.

    Thanks
    Yodit
    e.g
    SELECT Dept.cwid INTO LabelHold
    FROM [Please Enter Query Name]

  86. Hi Yodit,

    I think you need execute it with Execute command.

    1. You need to build a query with string manipulation
    2. Execute it with Execute(Query)

    Example:

    DECLARE @qry VARCHAR(MAX)
    SELECT @qry = ‘SELECT * FROM employees’

    DECLARE @Qry2 VARCHAR(MAX)

    SELECT @Qry2 = ‘SELECT * INTO tempdb..test FROM (‘ + @qry + ‘) x’

    EXEC(@Qry2)

    SELECT * FROM tempdb..test

    @qry, is your parameter which contains your query.
    @Qry2, is actual query which will be executed. It also contains your query from parameter.

    NOTE: @qry should be surrounded by parenthesis, so your query looks like:

    SELECT * INTO tempdb..test FROM (SELECT * FROM employees) x

    I hope this will solve your problem.

    Thanks,

    Tejas
    SQLYoga.com

    • Hi Tegas,

      Thanks for your response, but I need to automate this task in Access. The database is in Microsoft Access.
      I’m trying to create a query in access that will
      ask me the TableName or queryName as a parameter from which to get the data. ) I don’t know VBA.
      I would appreciate any help, hints or suggestions.

      Many thanks!

      Yodit

  87. Hi
    I need to fetch a common column from a number of tables in a database .I have the list of names of the tables I need to fetch data from by using the query:
    select table_name from information_schema.tables where table_name like ‘G%’ order by table_name

    Please help me.

  88. Hello Shailja,

    Do you want list of columns name that are in more than one table?
    If yes get the name of such columns by below query:

    select column_name from information_schema.columns
    group by column_name
    having count(*) > 1

    And then get the name of all tables for each column.

    Regards,
    Pinal Dave

  89. please can anybody send me the query for

    Displaying Database from SQL and then list of tables of DYNAMICALLY selected Database?
    please give attention over the word DYNAMICALLY selected……….
    thanx plz mail me as soon as possible….
    thanks………

  90. Hello Tapan,

    you can set hte database name in @db variable and execute the followinf batch:

    declare @str varchar(1000), @db varchar(100)
    set @db = ‘yourdbName’
    set @str = ‘select * from ‘ + @db + ‘.information_schema.tables’
    exec (@str)

    Regards,
    Pinal Dave

  91. Hi Dave,

    Thanks for sharing all the great info. You have saved me many hours of work.

    Sorry if this is repeated somewhere but I could not get this to work:

    USE
    SELECT *
    FROM information_schema.Tables
    where TABLE_TYPE=’base table’

    Then realized it is case sensitive for me because I have binary sort order. So this worked for me.

    SELECT * from INFORMATION_SCHEMA.TABLES
    WHERE table_type = ‘BASE TABLE’

    Thanks again

    Bob

  92. Hi,

    I am new in SQL Server 2005. I want to search a customer name from the any tables exists in a same database. Could any one help to write such query by which we can search any text in database irrespective to the tables name. (i.e. I am not aware that the required details existing in which table, only database name is know)

    Please help!

    Thanks in advance

    AKHILESH

  93. Hello Pinal ,

    I have an error on attach/detach
    I detached my database, right now I dont have the database , then I am going to attach the database , buy it’s giving some error , the error is as follows..,

    Error 823:I/O error 38(Reached the end of the file.) detected during read at offset 0*000002ac680000 in file ‘S:\MSSQL\Data\ActivateMBBCards_log.ldf’.

    Please Provide the answer
    Please give me the entair script.

    Thanks & Regards ,
    Sahasra

  94. Hi Pinal ,

    I am very thankful and I am appreciate your effort by helping us.
    I have this case; I need to modify certain data in a certain column for all tables contain that value, for example let’s say that we have DB named “DB_Name” with the following tables:
    Table “X” & Table “Y”, each one of these tables has a column name “id” where it’s a primary key in table “X” and foreign key in tables “Y”.
    I f I tried to change the data in the table “Y” I get this message [The update statement conflicted with the REFERENCE constrains “FK_Y_ REFERENCE_X” the conflict occurred in database “DB_Name”, table “dbo.X”]
    So I go to the table “X” and trying to change the data but I get the same message, what I need is to break the relation in between these tables to make the changes then restore the relation again.
    Note: I have almost 350 tables in the DB that I work with and they are all related to each other.
    Many thanks in advance :)

  95. Hi guys,
    I created a view in sql2008 and when I try to select data using the view I got an error saying “Invalid object name” in my select statement for the fields. I used dbo as the schema name in defining the view.
    Any help will be highly appreciated.

    Thanks,
    Tony

  96. Hi evryone,
    I am trying to create an advance search query from multiple optional parameters. Could anyone tell me the best way to do this. I have 8 parameters to work with.

    Thanks,

  97. Thanks very much Madhivanan!!
    I started out with this approach but I find myself doing a lot if else statement that end up not given me the correct results. I have to come up with every possible combination for the 8 parameters. Is there a more efficient way to come up with every possible combination that the user can search by,.

    Thanks!
    Tony

  98. HI
    I need to check the my SQL server version ( 2000 or 2005)how to check the SQL server version and I need the document SQL server upgarde 2008 64 bit.can you send me document .

  99. How I can truncate all tables except the master tables..
    Do You have any script where I just pass the master tables tuncate all tables except passed (master tables) ?

    Thanks & Regards
    Raman verma

  100. Hi, I need to retrieve my database tables so that I can copy them into word document for my project submission. Im using SQL SERVER 2005.

    • Do you want copy the table names or structure of the tables?

      If tables names

      Select table_name from information_schema.tables

      It structures, make use of [b]Generate Script option[/b] from Management studio

  101. Good evening sir,

    i have one table Users and the fields are

    UserID Bigint,
    UserName nvarchar(50),
    SponsorID Bigint

    this question is related to Level tree in which i have to find out the parent of any user recursively.

    in this scenario when a new member is registered, he must have to enter his SponsorID (who is sponsoring the User). after that i have to find out the sponsors of all parent till the root node comes.

    the UserID may be a SponsorID for other user.

    so how can i recursively find the the userID and SponsorID of all the users with the help of Cursor.

    please sir help me out from this.

    thanking you,

  102. Hey Pinal,
    I am trying to get a list of all the database names along with their tables for SQL 2000, SQL 2005 and SQl 2008.
    sysobjects only gives the table names
    while information_schema.tables does not work in SQL 2000 Enterprise Edition.
    Please help.

    Lisa

  103. Hi,

    how can i delete the logs of the database. because my database logs size is 54 GB so not being srink.

    now what should i do??

  104. Hi Pinal Dev,

    I m using sql server2005. i run a query like…
    —sp_helpdb —-which is showing all databses name with details. but i want’s to view only the –database_Name–plz help with this

    Thanx
    Rakesh Fhaujdar

  105. Select the database from the database list,type select * from sysobjects;

    it will show all of the tables that belongs to that database.

  106. Hi Pinal,

    How to display the tables in a particular database?

    when i execute the below query it showing all the tables in the all the databases

    select * from sys.tables

    is there any direct query to list the tables in a particular database?

    thanks
    Koteswar rao

  107. How to import data from oracle database to sql database.Not by genarating any text file.

    It’s need to be imported through DB Link .

  108. Hi Pinal,
    I have a question about show/hide table columns in sql server based user permissions.

    How can we write a query for above thing.

    Please mail me or or answer my question in the post.

    Thanks,
    Kiran Kumar

  109. How to detect and count the scanning table. I need to count query of select, insert,update, delete the table. I need to check how much has been used a table.

  110. Hi Pinal
    Want to know how do I do database versioning? Actually I want to achieve something like application versioning, so when the application starts it should detect database version and compatibility with the application version. If the database version is compatible with application version then application wil continue, but if not then it should ask for the compatible database version.

    Thanks
    Shailesh S Bhute

  111. i tried so many queries to list all the tables i have created,and at the end this simple query did the magic!!

    select * from Tab;

    lists all the tables under the logged in user…

  112. Hi Pinal, Madhivanan

    I have a database with 1000 tables (for each table _id is the primary key).

    Would it be possible to select all tables which are having _id > 1500 (i.e Primary Key value > 1500).

    Thanks in advance,
    Sri.

      • Thank you Madivanan.

        I did the similar thing in the fist place, but I did have a thought to check if is there any elegant way to do this without need of running those queries.

        SELECT * FROM SYS.TABLES

        then loop through all the tables to check if the table meets the criteria.

        SELECT COUNT(*) FROM [] WHERE Table_id >1500 HAVING COUNT(*) > 0

        then appended all the table names to a list (tables which return a value) .

        Coming to your query:
        select ‘ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables

        only composes the SQL like :
        select * from employee where employee_id>1500

        but it not tell us if the table has meet our criteria?
        again I need to check if the query returned any rows etc., in program.

        Please let me know if is there any better approach. Many Thanks for your reply.

        Sri.

        • Ok. Execute this code

          declare @sql varchar(max)
          set @sql=”
          set @sql=@sql+’ select * from ”’+table_name+”’ where _id>1500”’ from INFORMATION_SCHEMA.tables
          exec(@sql)

          It will list out required data from all the tables

          • Thanks for your reply and patience.

            I am having trouble in executing the above query.

            I have replaced ’ with single quote, but it is erroring with a messgae : Incorrect syntax near the keyword ‘from’.

            (tablename_id is the primary key rather than just the _id)

            Could you please advise?

            Thanks,
            Sri.

          • There is an extra single quote. Try this

            declare @sql varchar(max)
            set @sql=”
            select @sql=@sql+’ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
            exec(@sql)

          • Thank you Madhivanan, this is working fine now.

            declare @sql varchar(max)
            set @sql=”
            select @sql=@sql+’ select * from ['+table_name+'] where ['+table_name+'_id]>1500′ from INFORMATION_SCHEMA.tables where table_type=’BASE TABLE’
            exec(@sql)

            But I wonder if is there any way to list only the table names not the tables data (tables which are having rows with id> 1500)?

            Thanks,
            Sathish.

          • Try this code

            declare @sql varchar(max)
            set @sql=”
            select @sql=@sql+’ if exists(select * from ['+table_name+'] where ['+table_name+'_id]>1500) select top 1 ”’+[table_name]+”” from INFORMATION_SCHEMA.tables where table_type=’BASE TABLE’
            exec(@sql)

  113. i have a problem with a step in my sql databse which is as the following:

    – Create a Stored Procedure that copies the record(s) in tblDAT_MyStar into tblDAT_MyStartHistory every time the stored procedure is called. The example is given here to help you out. In your code, make sure that the value returned by GetDate() is assigned to a local variable first. That is, you need to declare a local variable of type that is suitable of storing a date together with time. Populate the header section with your info.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    — =============================================
    — Author: ****
    — Create date: 1/18/2011
    — Description: Show an example of SP and copying records
    — =============================================

    create procedure [sp_CopyingClass]
    as
    BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @Status as int
    — Insert statements for procedure here

    Begin transaction
    INSERT INTO [dbo].[tblDAT_ClassHistory]
    ([ClassCode]
    ,[InsertedAt])
    Select Classcode, GETDATE() from tblDAT_Class

    select @Status = @@ERROR
    if @Status = 0
    commit
    else
    rollback

    return @Status
    END
    GO

    the tables are:
    – tblDAT_MyStar

    column name
    FirstName nvarchar(128)
    LastName nvarchar(128)
    john nchar(10)

    – tblDAT_MyStartHistory

    column name
    ID_AA21080 int
    FirstName nchar(10)
    LastName nchar(10)
    InsertedAt nchar(10)

    please help

  114. hello sir

    i knew sql server 2005 i knew create tables and backup and maintain column and row . but i have no exp in database field. i don’t know about use of sql server in mnc companies. i dont know use full text search and stored proceudre and triggers

    what should i do in database file
    plz help me this query
    becoz its very important for my life

    happy

  115. Hi every one!
    how can i view all the tables in my database on my admin page. i tried
    SELECT * FROM INFORMATION_SCHEMA.TABLES
    it gives me “Resource id #3″ and doesn’t show any thing on the page.

    pliz help.

  116. Hi All,

    I want the list of all tables and views of the database which has “emailaddress” as a field.

    How can i achieve this?? Please throw some light..

    Regards,
    Sneha

  117. Hi ,
    Query:

    1) Any giving user id give all their business that have been in active state by admin1

    ============
    2)List all the posts under top category Science
    =========
    3) All the address under country Singapore..

    please reply to my mail ASAP thanks

    Raj
    [email removed]

  118. select table_name,COUNT(COLUMN_NAME) from information_schema.columns
    where NUMERIC_PRECISION is not null
    group by table_name

  119. Can anyone suggest me any tool or agent through that
    i can pull out a system hardware information means asset scanning & insert the data into a sql database.

  120. Dear,

    i am using ” Toad for Oracle” and i went to fetch all tabels is the database and i can’t use the below quary due to the below error.

    “table or view does not exist”

    please advice

  121. I need to create history tables based on their orginating tables. The only exception being I need a new primary key field and the primary key field from the originating table needs to become a regular numeric field. I know comething can be created using sql Server 2005 sys.tables, sys.columns, etc. Has anyone done this? Can you offer up some sql to help me along my way?

  122. Hello Pinal,
    Can you help me how to find out the usage of the tables on the databases, and its growth on the tables from period to period…
    to collect the tables that are heavily used…

    Regards,
    Rohit

      • Thanks for the prompt response Madhivanan….

        But it gives only row count, used and unused information only, that too it happening me to do on each and evry table as there are lot many tables in each of my databases.
        So is there any process to look at a time on whole database that too from time to time period, so that I can figure out the growth in the tables and which are heavily used…

        For example: suppose there is 100kb of used data on a table and after two days it may be something more like 150kb… so I want that growth from time to time so that I can aware of getting the information which are heavily used…

        I hope u got understood…. if not let me know, I’ll try to explain little more abt my issue…

        Regards,
        Rohit

  123. Hello,
    Can anyone help me quickly how to format this datepart(week,date) to two characters length… I mean if the work week is one then it has to mention as 01 not as ‘1’… in the same way if the work week is 2 then it has to mention as 02 instead of 2…. and need to go on till 09….

    Regards,
    Rohit

  124. Hi Pinal,

    can you let me know where am doing wrong in this stored procedure….
    Actually if I run this query its working fine in 4 seconds of time…
    BEGIN

    SET @startdate = (SELECT CONVERT(VARCHAR(10),date_begin,101) FROM ww_calendar
    WHERE ww = SUBSTRING(@wwstart,5,LEN(@wwstart)) AND year = SUBSTRING(@wwstart,1,4))

    SET @enddate = (SELECT CONVERT(VARCHAR(10),date_end,101) FROM ww_calendar
    WHERE ww = SUBSTRING(@wwend,5,len(@wwend)) AND year = SUBSTRING(@wwend,1,4))

    INSERT INTO @temp_sn
    SELECT * FROM sntrax_current..SplitString(@snlist, ‘,’)

    DECLARE c_sn CURSOR FOR
    SELECT * FROM @temp_sn
    OPEN c_sn
    FETCH next FROM c_sn INTO @sn

    WHILE (@@fetch_status -1)
    BEGIN
    SET @sn = ltrim(rtrim(@sn))

    SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
    RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
    Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
    CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
    LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
    ON snm.sn_identity = shplnk.sn_identity
    LEFT OUTER JOIN sntrax_current..ship_master shpm
    ON shplnk.ship_identity = shpm.ship_identity
    WHERE shpm.shipdate >=”’+ @startdate + ”’AND
    shpm.shipdate = @startdate AND
    shpm.shipdate <= @enddate AND
    Substring(snm.sn, 1, 4) = @sn order by shpm.shipdate desc

    Kindly help me in this query pls….

    Regards,
    Venkat

  125. oops sorry….

    the above sent query was working fine in 4 to 6 seconds, but if I remove the query out of @sqlquery parameter it taking much time (approximately 3 to 5mnts of time)

    INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
    RIGHT(‘0′ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
    Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
    CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
    LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
    ON snm.sn_identity = shplnk.sn_identity
    LEFT OUTER JOIN sntrax_current..ship_master shpm
    ON shplnk.ship_identity = shpm.ship_identity
    WHERE shpm.shipdate >= @startdate AND
    shpm.shipdate <= @enddate AND
    Substring(snm.sn, 1, 4) = @sn order by shpm.shipdate desc

    can you help me where am doing wrong pls….

    Regards,
    Venkat

  126. To list all tables from existing database use sql command “select * from tab” without double quotes.

  127. Hello Pinal,
    Expecting an answer from you about my query….
    Actually why its showing such an impact on that parameter… when I run the query by initializing the query into parameter “Set @sqlquery = …” its working fine giving output in seconds… but when I take out of the parameters and run directly its taking much time… why there is such a difference in run time….???

    Looking for reason from you… and help me with a correction in my query….

    Regards,
    Venkat

  128. SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
    FROM
    sysobjects so,
    sysindexes si
    WHERE
    so.xtype = ‘U’
    AND
    si.id = OBJECT_ID(so.name)
    GROUP BY
    so.name
    ORDER BY
    2 ASC

    By Thameem

  129. Hi Pinal Dave,
    Greetings. I have a problem in creating a query. Can you please see and provide some help.
    Scenerio is:
    id column_name parentid
    1 A 0
    2 B 1
    3 C 1
    4 D 3
    5 E 2
    6 F 4
    7 G 0

    The columns which has parentid as 0 are the top most items and rest all have the parent-child relation.
    I want it somelike
    A
    B
    E
    C
    D
    F
    G

    Where B,C are child of A, E is child of B, D is child of C an so on.

    Thanks in advance….:)

  130. Column ‘a’
    —————————————————–
    Select e.empno,d.deptname from emp e
    inner join dept d on e.deptno=d.deptno

    Select e.empname,ed.salary from emp e
    inner join empdet ed on e.empno=ed.empno

    The above are two rows in column named as ‘a’ in a table ‘t1′

    Now i need the result to be as

    column ‘Obj_names’
    ——————————————-
    emp
    dept
    emp
    empdet

    (ie), I need a query that will exactly get the table names from those SQL statements and display me..

    IS THERE ANY OPTIONS FOR THIS..

    Help me PINALDAVE or ANYONE who can ??????????

    Regards,
    Arunkumar
    MCTS – SQL Server 2005

  131. Hi Pinal Dave,

    Is it possible to execute a query in two database together?
    For example :
    select database1.Name from sys.procedures where [name] not in (select database2.Name from sys.procedures)

  132. Okay I found the solution

    Here we go:
    Select t1.Name from
    db1.sys.procedures t1 where t1.name not in (Select t2.Name from
    db2.sys.procedures t2)

    Hope it helps someone….

    Pinal Dave, you are doing fabulous job man. keep going..do well….

    Regards

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

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

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

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

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

  137. 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 ??

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

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

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

  141. 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 – http://www.sqlservercentral.com/Forums/Topic671366-359-2.aspx?Update=1

    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

  142. 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”

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

    BEGIN
    SELECT filed1,filed2,filed3 FROM table1

    SELECT field1,filed2,field4 FROM another_different_table_1

    SELECT field1,filed2,field4 FROM another_different_table_2

    RETURN………………..

    END

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

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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s