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
