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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query on Multiple Database tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chava_sree
Yak Posting Veteran

USA
56 Posts

Posted - 08/21/2008 :  22:25:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/21/2008 :  22:37:36  Show Profile  Reply with Quote
Yes with sp_Msforeachdb.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 08/22/2008 :  00:04:37  Show Profile  Reply with Quote
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
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 08/22/2008 :  00:08:57  Show Profile  Reply with Quote
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

Edited by - harlingtonthewizard on 08/22/2008 06:19:00
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000