CODEWITHSUNDEEP
sqlsql-server
RLH
RLH

Reputation: 15698

If given a list of SQL Server objects, how can I determine which databases each object resides in?

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?

Upvotes: 0

Views: 322

Answers (2)

Malk
Malk

Reputation: 11983

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

Upvotes: 1

Arun
Arun

Reputation: 951

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'"

Upvotes: 0

Related Questions