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)
 Need help running this against all Databases

Author  Topic 

rternier
Starting Member

44 Posts
Posted - 2009-03-17 : 15:01:27
I have a query I need to run against 500+ databases. I'm stuck, any thoughts?

Here's the query:

CREATE PROCEDURE [dbo].[procSelectUsers] 
(
      @p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int
)
      
AS
BEGIN
DECLARE @TempTable TABLE(RowID INT IDENTITY, UserID int)

INSERT INTO @TempTable(UserID)
SELECT intUserID 
      FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W
            WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
                  AND W.intWorkgroupID = U.intWorkgroupID
                  AND vchLastName LIKE @p_vchLetterFilter + ''%''
                  AND vchLastName LIKE ''%'' + @p_vchQuickSearch + ''%''
      ORDER BY 
            CASE
                  WHEN @p_vchOrderBy = ''last'' AND @p_vchSortDirection = ''ASC'' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))
                  WHEN @p_vchOrderBy = ''last'' AND @p_vchSortDirection = ''DESC'' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))
                  WHEN @p_vchOrderBy = ''first'' AND @p_vchSortDirection = ''ASC'' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))
                  WHEN @p_vchOrderBy = ''first'' AND @p_vchSortDirection = ''DESC'' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))
                  WHEN @p_vchOrderBy = ''username'' AND @p_vchSortDirection = ''ASC'' THEN (RANK() OVER (ORDER BY vchUserName DESC))
                  WHEN @p_vchOrderBy = ''username'' AND @p_vchSortDirection = ''DESC'' THEN (RANK() OVER (ORDER BY vchUserName ASC))
                  WHEN @p_vchOrderBy = ''security'' AND @p_vchSortDirection = ''ASC'' THEN (RANK() OVER (ORDER BY vchRoleTitle DESC, vchLastName DESC, vchFirstName DESC))
                  WHEN @p_vchOrderBy = ''security'' AND @p_vchSortDirection = ''DESC'' THEN (RANK() OVER (ORDER BY vchRoleTitle ASC, vchLastName ASC, vchFirstName ASC))
                  WHEN @p_vchOrderBy = ''workgroup'' AND @p_vchSortDirection = ''ASC'' THEN (RANK() OVER (ORDER BY vchDescription DESC, vchLastName DESC, vchFirstName DESC))
                  WHEN @p_vchOrderBy = ''workgroup'' AND @p_vchSortDirection = ''DESC'' THEN (RANK() OVER (ORDER BY vchDescription ASC, vchLastName ASC, vchFirstName ASC))
            END
/*Get the total Users that can be viewed*/
SELECT COUNT(userID) FROM @TempTable

/* Get the Alphabet Characters from the Last Name */
SELECT DISTINCT CONVERT(VARCHAR(1), vchLastName) FROM tblUsers


SELECT      intUserID, vchFirstName, vchLastName,vchUserName, vchPassword, vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID, 
            IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber, SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc
                  
      FROM @TempTable T, tblUsers U, tblSecurityRoles SR, tblWorkgroups W
            WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
                  AND T.UserID = U.intUserID
                  AND W.intWorkgroupID = U.intWorkgroupID
                  AND vchLastName LIKE @p_vchLetterFilter + ''%''
                  AND vchLastName LIKE ''%'' + @p_vchQuickSearch + ''%''
                  AND t.RowID BETWEEN (@p_intPage * @p_intPageSize + 1) AND ((@p_intPage + 1) * @p_intPageSize)



----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-17 : 15:05:13
hope this helps.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90393
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-17 : 15:09:24
Or, you can code a cursor which would loop thru all databases from sysdatabases and use dynamic sql to change the db name in the query everytime.
You would want to avoid firing it on tempdb and master if you go this way.
Go to Top of Page

rternier
Starting Member

44 Posts
Posted - 2009-03-17 : 15:56:38
Hey Sakets,

I've been trying that method for the past 30 minutes with no avail. I get massive errors on random lines like "vc" isn't defined... and it points to vchUserName

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-17 : 16:32:13
Ryan.. I think you'll need to do it in 2 steps.

1)
--this is to compile the proc in all dbs except master,model,.. etc. Add to the list if there are exceptions.

DECLARE @command varchar(1000) 
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
EXEC(''
CREATE PROCEDURE YourSp
...

'') END' 
EXEC sp_MSforeachdb @command


2) Call the sp

DECLARE @command varchar(1000) 
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
EXEC(''
exec [dbo].[YourSpName] parameters
'') END' 
EXEC sp_MSforeachdb @command



Hope this helps.
Go to Top of Page

rternier
Starting Member

44 Posts
Posted - 2009-03-17 : 16:46:09
I'm just trying to get the Stored procedure into each database. We had a lot of issues before because of the temp table being declared in the stored procedure, I'm hopeing to get it done another way.

Doing this:


DECLARE @Command VARCHAR(1000)
SELECT @Command =  'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
EXEC(''IF NOT EXISTS (SELECT * FROM sysobjects WHERE ID = Object_ID(''''procSelectUsers''''))
 BEGIN
  EXEC sp_executesql N''''CREATE PROCEDURE procSelectUsers AS SELECT GetDate()''''
END
 '') END'

EXEC sp_MSforeachdb @Command


Will work, however this:


DECLARE @Command VARCHAR(1000)
SELECT @Command =  'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
EXEC(''IF NOT EXISTS (SELECT * FROM sysobjects WHERE ID = Object_ID(''''procSelectUsers''''))
BEGIN
ALTER PROCEDURE [dbo].[procSelectUsers] 
(
      @p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int
)
      
AS
BEGIN
DECLARE @TempTable TABLE(RowID INT IDENTITY, UserID int)

INSERT INTO @TempTable(UserID)
SELECT intUserID 
      FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W
            WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
                  AND W.intWorkgroupID = U.intWorkgroupID
                  AND vchLastName LIKE @p_vchLetterFilter + ''''%''''
                  AND vchLastName LIKE ''''%'''' + @p_vchQuickSearch + ''''%''''
      ORDER BY 
            CASE
                  WHEN @p_vchOrderBy = ''''last'''' AND @p_vchSortDirection = ''''ASC'''' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))
                  WHEN @p_vchOrderBy = ''''last'''' AND @p_vchSortDirection = ''''DESC'''' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))
                  WHEN @p_vchOrderBy = ''''first'''' AND @p_vchSortDirection = ''''ASC'''' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))
                  WHEN @p_vchOrderBy = ''''first'''' AND @p_vchSortDirection = ''''DESC'''' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))
                  WHEN @p_vchOrderBy = ''''username'''' AND @p_vchSortDirection = ''''ASC'''' THEN (RANK() OVER (ORDER BY vchUserName DESC))
                  WHEN @p_vchOrderBy = ''''username'''' AND @p_vchSortDirection = ''''DESC'''' THEN (RANK() OVER (ORDER BY vchUserName ASC))
                  WHEN @p_vchOrderBy = ''''security'''' AND @p_vchSortDirection = ''''ASC'''' THEN (RANK() OVER (ORDER BY vchRoleTitle DESC, vchLastName DESC, vchFirstName DESC))
                  WHEN @p_vchOrderBy = ''''security'''' AND @p_vchSortDirection = ''''DESC'''' THEN (RANK() OVER (ORDER BY vchRoleTitle ASC, vchLastName ASC, vchFirstName ASC))
                  WHEN @p_vchOrderBy = ''''workgroup'''' AND @p_vchSortDirection = ''''ASC'''' THEN (RANK() OVER (ORDER BY vchDescription DESC, vchLastName DESC, vchFirstName DESC))
                  WHEN @p_vchOrderBy = ''''workgroup'''' AND @p_vchSortDirection = ''''DESC'''' THEN (RANK() OVER (ORDER BY vchDescription ASC, vchLastName ASC, vchFirstName ASC))
            END
/*Get the total Users that can be viewed*/
SELECT COUNT(userID) FROM @TempTable

/* Get the Alphabet Characters from the Last Name */
SELECT DISTINCT CONVERT(VARCHAR(1), vchLastName) FROM tblUsers


SELECT      intUserID, vchFirstName, vchLastName,vchUserName, vchPassword, vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID, 
            IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber, SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc
                  
      FROM @TempTable T, tblUsers U, tblSecurityRoles SR, tblWorkgroups W
            WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
                  AND T.UserID = U.intUserID
                  AND W.intWorkgroupID = U.intWorkgroupID
                  AND vchLastName LIKE @p_vchLetterFilter + ''''%''''
                  AND vchLastName LIKE ''''%'''' + @p_vchQuickSearch + ''''%''''
                  AND t.RowID BETWEEN (@p_intPage * @p_intPageSize + 1) AND ((@p_intPage + 1) * @p_intPageSize)
      
END

 '') END'

EXEC sp_MSforeachdb @Command


bombs all the time.

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]
Go to Top of Page

rternier
Starting Member

44 Posts
Posted - 2009-03-17 : 18:08:48
Well I bit the bullet and just did this using Excel. Got a list of Every database I needed (540 of em) and added my query row and repeated it down. Worked like a charm.




----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts
Posted - 2009-03-17 : 19:19:20
This is an example of how I have queried against multiple databases. I did not want to use sp_MSforeachdb as it is an unsupported command.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[EventID_RP_2] Script Date: 03/18/2009 09:45:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[EventID_RP_2]

@StartDateRange DateTime,
@EndDateRange DateTime,
@SiteID nvarchar (max)

AS

SET NOCOUNT ON
Declare @sql nvarchar(max)
Declare @ParamDefinition AS nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT EventDetails.EventID
FROM '+name+'.dbo.EventDetails INNER JOIN
'+name+'.dbo.SiteDetails ON '+name+'.dbo.EventDetails.SiteID = '+name+'.dbo.SiteDetails.SiteID
WHERE ((EventDetails.EventStartTime >= @StartDateRange) AND (EventDetails.EventStartTime <= @EndDateRange))
AND SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition =
' @StartDateRange DateTime,
@EndDateRange DateTime,
@SiteID nvarchar (50)'

Create table #t([EventID] int)
Insert into #t

Execute sp_Executesql @sql, @ParamDefinition, @StartDateRange, @EndDateRange, @SiteID

Select DISTINCT [EventID]
from #t

Drop table #t

SET NOCOUNT OFF

RETURN
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site