SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.17 seconds. Powered By: Snitz Forums 2000