Query on Multiple Database tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query on Multiple Database tables

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-08-21 : 22:25:51

Does anybody know how to run a single SQL query on Multiple Databases.
I've around 50 Databases which has same table structure and i got something which i need to find out in all these databases. so is there a way to do it.. or run my query on each database.

my databases looks like these..no consistent names but same table structure. pls.help
a12
a2w
a3w
a4s
a5s

and i want to run a query something like this

SELECT * FROM EMPLOYEE
WHERE ACTIVE = 1

Thanks and appreciate your help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-21 : 22:37:36

Yes with sp_Msforeachdb.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-22 : 00:04:37

I am trying to do the same thing and I have the following working if this helps. I am using Reporting Services 2005 and point to this type of stored procedure. I do have concerns about using dynamic sql due to security issues? I have tried with sp_Msforeachdb but do not know how to limit DB's.

I am a beginner so don't take this as a good solution but it may give you some ideas.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Users] Script Date: 08/22/2008 13:28:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[Users]
@CombinedName varchar (100),
@CombPriv varchar (100)

AS
declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT UserDetails.LastName + '' '' + UserDetails.FirstName AS CombinedName,
GroupDetails.GroupName + '' - '' + PermissionDetails.PermissionName AS CombPriv, UserDetails.FirstName, UserDetails.MiddleName,
UserDetails.LastName, UserDetails.Initials, UserDetails.LoginName, GroupDetails.GroupName, GroupDetails.GroupDescription,
FeatureDetails.Description, PermissionDetails.PermissionName, PermissionDetails.PermissionDescription, UserDetails.UserID
FROM '+name+'.dbo.UserDetails INNER JOIN
'+name+'.dbo.UserGroup ON '+name+'.dbo.UserDetails.UserID = '+name+'.dbo.UserGroup.UserID INNER JOIN
'+name+'.dbo.GroupDetails ON '+name+'.dbo.UserGroup.GroupID = '+name+'.dbo.GroupDetails.GroupID INNER JOIN
'+name+'.dbo.GroupFeatures ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupFeatures.GroupID INNER JOIN
'+name+'.dbo.FeatureDetails ON '+name+'.dbo.GroupFeatures.FeatureID = '+name+'.dbo.FeatureDetails.FeatureID INNER JOIN
'+name+'.dbo.GroupPermissions ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupPermissions.GroupID INNER JOIN
'+name+'.dbo.PermissionDetails ON '+name+'.dbo.GroupPermissions.PermissionID = '+name+'.dbo.PermissionDetails.PermissionID
WHERE ((UserDetails.LastName + '' '' + UserDetails.FirstName) IN (Select Param From fn_MVParam ('''+@CombinedName+''','','')) AND (GroupDetails.GroupName + '' - '' + PermissionDetails.PermissionName) IN (Select Param From fn_MVParam ('''+@CombPriv+''','',''))) AND (UserDetails.IsDeleted = ''False'')
ORDER BY CombinedName ' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([CombinedName] varchar(100), [CombPriv] varchar(100), [FirstName] varchar(50), [MiddleName] varchar(50), [LastName] varchar(50), [Initials] varchar(50), [LoginName] varchar(50), [GroupName] varchar(50), [GroupDescription] varchar(50), [Description] varchar(50), [PermissionName] varchar(50), [PermissionDescription] varchar(50), [UserID] int)
Insert into #t

exec(@sql)
---Print @sql

Select [CombinedName], [CombPriv], [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [GroupName], [GroupDescription], [Description], [PermissionName], [PermissionDescription], [UserID]
from #t

Drop table #t

Return

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-22 : 00:08:57

USE [your dbname]
GO
/****** Object: StoredProcedure [dbo].[MultipleDB] Script Date: 08/22/2008 13:28:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[MultipleDB]

AS
declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT * FROM '+name+'.dbo.EMPLOYEE
WHERE ACTIVE = 1 ' from sys.databases where name like 'a%'

Create table #t(<<<define your fields here in the same order as your select statement above>>>)
Insert into #t

exec(@sql)
---Print @sql

Select *
from #t

Drop table #t

Return

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources