Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: select table from all databases

    i need to create a rowset containing database names where a particualr table name exists.
    ie. in Oracle i would select owner from sys.all_tables where table_name='MYTABLE'

    I know master.dbo.sysdatabases contains my owners, and in each database sysobjects would identify MYTABLE ..
    sp_tables only lists those in the current database, but i want to list all databases that contain MYTABLE

    a single select statement would be nice.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need a cursor that loops through all the databases in sysdatabases and executes a dynamic select from each one's sysobjects table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I think you will need to use dynamic SQL (bad, I know), because you can't use a variable in a database reference:

    MyDatabase.dbo.sysobjects cannot be written as @db_name.dbo.sysobjects

    So, you 'll probably need a stored procedure (or user function if you want to use the rowset inline with another sql statement) which will return your database list. Something like:

    CREATE PROCEDURE sp_find_databases_with_table

    @table_name VARCHAR(128)

    AS

    CREATE TABLE #db_name_table ([db_name] VARCHAR(128))

    DECLARE @sql_text NVARCHAR(255), @db_name VARCHAR(128)

    DECLARE db_cursor CURSOR FOR
    SELECT name FROM master.dbo.sysdatabases

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @db_name

    WHILE @@fetch_status = 0
    BEGIN

    SET @sql_text = 'SELECT ''' + @db_name + ''' AS db_name WHERE ''' + @table_name + ''' IN (SELECT name FROM ' + @db_name + '.dbo.sysobjects WHERE xtype=''U'')'

    INSERT INTO #db_name_table
    EXEC sp_executesql @sql_text

    FETCH NEXT FROM db_cursor INTO @db_name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    SELECT [db_name] FROM #db_name_table
    GO


    (if you cut and paste it, it should format better)
    -bpd

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I'm writing this big thing and blindman slips in under the wire... hope the rest of the day isn't like this...
    -bpd

  5. #5
    Join Date
    Jan 2004
    Posts
    9
    I was afraid cursors would be mentioned. Not a problem, just seems overkill compared to Oracle views. Wanted to be sure i hadnt missed a 'special' sqlserver view/table

    have a good weekend.
    Cheers,
    Carl.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that blindman, he's a guru

    no wait, now he's a janitor...


    as for using the sysobjects table, i dunno

    i was under the impression that you would go further in life using the INFORMATION_SCHEMA
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Well, if you let SQL generate a table script for you, it uses sysobjects. Seems like it would be the best way to go, especially since INFORMATION_SCHEMA relies on it.
    -bpd

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what i meant was, if you write anything based on what you think is in any of the system catalog tables, you stand a chance to have it break if the structure of the system catalog tables changes, which the database vendor can do from time to time

    INFORMATION_SCHEMA references, however, are standard sql, and yes, they are views of the system catalog tables, but they will always work, as they will be redefined by the vendor should a change to the underlying system catalg tables occur

    furthermore, INFORMATION_SCHEMA is supported by many databases, so you could actually pick up a script you wrote in one and use it in another without change

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Touche
    -bpd

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, but I was too lazy to actually do the work for carlcjs like bpd!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    But now I think I'm burnt out and need to go home. It's quarter of 11am here. Long day...

    -b
    -bpd

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It maybe viewed as cheating, but if you create a union-based view on sysobjects from all of your databases first, then you CAN do it in 1 SELECT, just like in oracle (BTW, what is it? )

  13. #13
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    The only drawback to that approach would be the need to update the view with the addition fo a new database. Otherwise it would work.

    PS: Nothing is cheating.
    -bpd

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, but this drawback can be overcome by a script that would generate the view on a daily basis, or on demand.

  15. #15
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Or by a single procedure that would never need updating.
    -bpd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •