I have a list of over 200 databases objects. All of these are either tables, stored procedures and views.
All of these objects exist on a specific SQL Server, but I was not given a field specifying which database each object belong in.
Given a list of DB objects that exists somewhere on a specific server, how can I query the server to provide the containing database name for each object?
I had a similar issue, this is what worked for me:
-- List of objects .. store in a table somewhere with
-- a db column set to an empty string
create table tempdb.dbo._mylist ( name nvarchar(500), db nvarchar(500) )
insert tempdb.dbo._mylist values ('obj 1', '')
insert tempdb.dbo._mylist values ('obj 2', '')
-- Setup cursor for databases
DECLARE db_cursor CURSOR FOR
SELECT name from sys.databases WHERE [state] != 6 -- OFFLINE
-- Loop through cursor
OPEN db_cursor;
DECLARE @dbname sysname;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
-- added dbname to object list if found (joined using common collation)
EXECUTE ('use ' + @dbname + '; update l set db = db + '';' + @dbname + ''' from tempdb.dbo._mylist l join sysobjects o on o.name = l.name COLLATE SQL_Latin1_General_CP1_CI_AS;');
FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
-- Select results
select name, db = isnull(stuff(db,1,1,''), '') from tempdb.dbo._mylist
-- Cleanup
drop table tempdb.dbo._mylist
You can write a script using the SP_MSFOREACHDB stored procedure to do this. You can find examples of this here This basically allows you to run a script against all the databases.
For Example, the statement below will allow you to search for a table name, and it will also return the associated databasename.
EXEC sp_Msforeachdb "USE [?]; SELECT '[?]' databaseName, * FROM sys.tables WHERE name = 'table_name'"
