how to use multiple database at same time

Question

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.

Jaimin Shethiya · Answer

Hello Archit, you have to first execute GL script for GL database and after once again execute GR8 script for GR8 database. Thanks

Archit Shrivastava · Answer

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 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

Dinesh Gabhane · Answer

You can use in .Net for SQL Server and any other Databases like ORacle, AS400 and so on

Dinesh Gabhane · Answer

You can use ORM like Entity Framework

Ramachandran M · Answer

Hi Archit, Can we use [DBserver].dbo.databaseName ? select * from [ReportServer].dbo.Event

Archit Shrivastava · Answer

yes i know...i get the solution.thanks for replying

Salman Beg · Answer

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 R · Answer

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 Shrivastava · Answer

ok ....but i dont understand how to use ...can u please share some code or syntax or link

Archit Shrivastava · Answer

can i use ORM directly in sql server