Select*from emp ( I want to run this query in 20 databases?)

Question

select*from emp ( How to run this query in 20 databases in single instance ?)

Sagar Chavan · Answer

You can use below generic SQL script. DECLARE @database_name VARCHAR (50), @schema_name VARCHAR (50) = 'dbo' , @table_name VARCHAR (50) = 'employees' , @sql_query NVARCHAR( MAX ), @row_id INT = 1 If OBJECT_ID( 'tempdb..#databases' ) IS NOT NULL DROP TABLE #databases SELECT ROW_NUMBER() OVER( ORDER BY [ name ]) row_id, [ name ] INTO #databases FROM sys.databases WHILE EXISTS( SELECT 1 FROM #databases WHERE row_id = @row_id) BEGIN SELECT @database_name = [ name ] FROM #databases WHERE row_id = @row_id IF COALESCE (OBJECT_ID(@database_name + '.' + @schema_name + '.' + @table_name), 0) != 0 BEGIN SELECT @sql_query = 'SELECT * FROM ' + @database_name + '.' + @schema_name + '.' + @table_name PRINT(@sql_query) EXEC (@sql_query) END SELECT @row_id = @row_id + 1 END

Kapil Singh Kumawat · Answer

If you have all your 20 databases in a single server then you can write query in this way - Select * from [database 1].dbo.emp . . Select * from [database 3].dbo.emp . . Select * from [database n].dbo.emp

Nitin Sontakke · Answer

Assuming schema is dbo in all cases... select * from [db01].[dbo].[emp] select * from [db02].[dbo].[emp] ... select * from [db19].[dbo].[emp] select * from [db20].[dbo].[emp] If this is not an expected answer, then I haven't understood your question. Sounds more like an interview question to me, than a rare real life scenario.

Muhammad Imran Ansari · Answer

Write a cursor to run same query on multple databases in singl instance. Qury will be like: DECLARE @DBNameVar NVARCHAR(128),@Statement NVARCHAR(300) FETCH NEXT FROM AllDatabases INTO @DBNameVar WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = N'USE [' + @DBNameVar + N'] SELECT * FROM Emp' PRINT @Statement EXEC sp_executesql @Statement FETCH NEXT FROM AllDatabases INTO @DBNameVar END CLOSE AllDatabases DEALLOCATE AllDatabases You can also write a storeprocedure on any database and use the same script as query for storeprocedure.

Select*from emp ( I want to run this query in 20 databases?)

Answers (4)