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

kiran kumar

5y909

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
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.
Sagar Chavan4y ago
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 Kumawat5y ago
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 Sontakke5y ago
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.