Post reply

List all databases that have the same table name

  • December 13, 2006 at 7:02 pm

    #368568

    Hi,

    I need to produce a stored procedure to populate a table with all database names under the same server that contain the name "Tracker". 

    I've searched the net and cannot find anything.  Looking for help!! 

    Thanks

  • December 13, 2006 at 7:21 pm

    #677972

    Use sp_MSforeachdb

    _____________
    Code for TallyGenerator

  • December 13, 2006 at 7:31 pm

    #677976

    Create Table #dbnames(db varchar(50))

    Declare @dbname varchar(50)

    declare dbases cursor for

    select name from master..sysdatabases

    Open dbases

    fetch next from dbases

    into @dbname

    while @@fetch_status = 0

    begin

     print 'select top 1 ''' + @dbname + ''' from ' + @dbname + '..sysobjects where name = ''Employees'''

     insert #dbnames

     exec('select top 1 ''' + @dbname + ''' from ' + @dbname + '..sysobjects where name = ''Employees''')

     fetch next from dbases

     into @dbname

    end

    Close dbases

    deallocate dbases

    select * from #dbnames

    drop table #dbnames

     

    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

  • December 13, 2006 at 8:40 pm

    #677981

    Thanks so much,

    The stored procedure worked great!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply