how to use multiple database at same time

Archit Shrivastava

6y1.1k

Hi all

i want to know how to use multiple database at same time

actually i have 2 sql script name like GL and GR8 now my need is i want to apply GL script to all GL databases at same time and same process applied on GR8...

how can i do this.

10 Replies

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

Ramachandran M6y ago
Hi Archit,

Can we use [DBserver].dbo.databaseName ?

select * from [ReportServer].dbo.Event
Archit Shrivastava6y ago
yes i know...i get the solution.thanks for replying
Jaimin Shethiya6y ago
Hello Archit,

you have to first execute GL script for GL database and after once again execute GR8 script for GR8 database.

Thanks
Archit Shrivastava6y ago
i found a solution

BEGIN TRAN
CREATE TABLE #TempGETDBLIST
(
[NAME] NVARCHAR(255) NULL,
[ROWID] INT IDENTITY NOT NULL
)
INSERT INTO #TempGETDBLIST (NAME)
--SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%EnterpriseGL%' -- get all database name like GL
--SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%GR8%' -- get all database name like GR8
SELECT '#TempGETDBLIST',* FROM #TempGETDBLIST
DECLARE @Flag INT = 1
WHILE (@Flag <= (SELECT COUNT(1) FROM #TempGETDBLIST))

BEGIN
DECLARE @ABC NVARCHAR(50), @query NVARCHAR(max)
SET @ABC =(SELECT Name FROM #TempGETDBLIST WHERE ROWID = @Flag)
SET @query = 'USE '+ @ABC +' select top 1 * from SystemConfiguration' -- USE[DATABASE NAME] + QUERY
EXECUTE( @query )

SET @Flag = @Flag + 1

END

DROP TABLE #TempGETDBLIST

ROLLBACK TRAN
Salman Beg6y ago
Hi.
You can use sp_MSforeachdb to run the same query in multiple databases at same time.
Example:-
DECLARE @command varchar(1000)
SELECT @command =
    'USE [?] UPDATE Table1 SET Field1 = ''ninjas'' WHERE Field2 = ''pirates'''
EXEC sp_MSforeachdb @command
For more example you can follow this link,
https://www.c-sharpcorner.com/UploadFile/63f5c2/sp_msforeachtable-stored-procedure-in-sql-server-2012/

Thanks.
Mageshwaran R6y ago
Hi,
please refer this link:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/747b36ab-5fec-49c4-a0e6-45eb37037cc4/queries-from-multiple-databases-on-the-same-server?forum=transactsql
Archit Shrivastava6y ago
ok ....but i dont understand how to use ...can u please share some code or syntax or link
Dinesh Gabhane6y ago
You can use in .Net for SQL Server and any other Databases like ORacle, AS400 and so on
Archit Shrivastava6y ago
can i use ORM directly in sql server
Dinesh Gabhane6y ago
You can use ORM like Entity Framework