Pages

Friday, September 27, 2013

Find an object in a SQL Server Instance (across databases)

Following block generates a concatenated string for a set of selected databases defined in the sub query.
DECLARE @command VARCHAR(max) ;
SELECT @command = coalesce (@command + 'union' , ' ') +
'    SELECT ''' + [DBName] + '''AS DBName,
    [type_desc] AS ObjectType
    FROM ['+ [DBName] + '].sys.objects
    WHERE [name] = ''ATC4''
'
FROM (SELECT TOP 1000 name AS DBName FROM master.sys.sysdatabases) d
EXEC (@command)


sp_MSforeachdb is an undocumented stored procedure, which can be used to run sql statements across databases in a SQL Server instance.
DECLARE @command varchar(1000)
SELECT @command =
'    USE [?] ; SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM sys.objects
    WHERE name = ''ATC4''
'
EXEC sp_MSforeachdb @command ;


Following cursor can also be used to execute SQL Statements across databases in a SQL Server instance.
DECLARE @DB_name varchar(1000)
DECLARE cur CURSOR LOCAL for
    SELECT name FROM master.sys.databases
OPEN cur
FETCH NEXT FROM cur into @DB_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM [' + @DB_name + '].sys.objects
    where name = ''ATC4''
    ')
    FETCH NEXT FROM cur INTO @DB_name
END
CLOSE cur
DEALLOCATE cur

No comments:

Post a Comment