Alternative for a cursor

Question

Post reply

Alternative for a cursor

sandra pastrana

SSC Eights!

Points: 979
More actions
November 30, 2007 at 10:17 am

#184567

I need to run a query in all servers, but I can not write a cursor becuase It is to create a report in reporting services.
Is there a stored procedure that will run a query in all databases?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

Sure see BOL for more info on sp_MSforeachdb

Here is a sample:

EXEC sp_MSforeachdb "USE ? IF db_name() NOT IN ('master', 'tempdb', 'model', 'msdb' )

begin select db_name() EXEC sp_updatestats end "

This updates stats in all databases except the system databases

Francis

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

FYI... sp_MSforeachdb uses a cursor 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Antares686 SSC Guru Points: 125444 More actions · Answer 3

You say all servers did you mean databases?

If so the present solution is still a cursor but may simplify for your needs. Otherwise I need to know what you are doing in each database and may have a way to do.

fhanlon SSChampion Points: 13131 More actions · Answer 4

ok... but you are not writing the cursor. Why does the existance of a cursor matter?

To avoid use something like:

DECLARE @objName varchar(50)

DECLARE @cmd nvarchar(250)

SET@objName = ''

WHILE@objName IS NOT NULL

BEGIN

SELECT @objName = MIN( CATALOG_NAME )

FROMINFORMATION_SCHEMA.SCHEMATA

WHERE CATALOG_NAME > @objName

AND (CATALOG_NAME NOT IN ('MODEL', 'master' ,'msdb'))

IF@objName IS NOT NULL

BEGIN

--Insert code to do stuff here.

SELECT @objName

SELECT @cmd = 'USE ' + @objName + '; dbcc checkdb'

select @cmd

EXEC sp_executesql @cmd

END

This needs to be modified depending on the command being executed and may need to change for SQL 2005. But its a starting point

Francis

sandra pastrana SSC Eights! Points: 979 More actions · Answer 5

What I am trayng to do is a report in reporting services, so can not use a cursor (as far as I understand)

my report needs to read server roles from sys.syslogins

and also list what databases each user has acces to. in order to do the report, I need to write a select statement or a procedure that will return 1 result set.

so far what i have is a stored procedure that returns what users have acces to what databases,

and the idea is to join that result set with sys.syslogins.

not sure if that is possible. (I just posted that question in this forum under a different posting..)

This would be the stored procedure:

create proc P_DATABASEUSERS

as

IF OBJECT_ID('tempdb..#DatabaseUsers') is not null

drop table #DatabaseUsers

create table #DatabaseUsers(DatabaseName sysname not null,DatabaseUserName sysname not null,LoginName sysname null)

-- sys.database_principals.type is S = SQL user, U = Windows user, G = Windows group

exec master.dbo.sp_MSforeachdb @replacechar = N'?',

@command1 = 'insert into #DatabaseUsers(DatabaseName,DatabaseUserName,LoginName)select ''?'',

DatabaseUsers.name as DatabaseUserName, suser_sname( DatabaseUsers.SID) as LoginName

from [?].sys.database_principals as DatabaseUsers

where DatabaseUsers.type in (''S'',''G'',''U'')'

select * from #DatabaseUsers

GO

I WOULD LIKE TO JOIN THE RESULT SET OF THIS STORED PROC TO SYS.SYSLOGINS

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Unfortunately becuase of the variation of number of databases you may have a cursor will have to be used (but sp_MSForEachDB will do the trick)

As for how to join to SYS.SYSLOGINS you have to do INSERT INTO..EXEC (I posted wrong in your other post) Here is an example with your current procs code

IF OBJECT_ID('tempdb..#DatabaseUsers') is not null

drop table #DatabaseUsers

create table #DatabaseUsers(DatabaseName sysname not null,DatabaseUserName sysname not null,LoginName sysname null)

-- sys.database_principals.type is S = SQL user, U = Windows user, G = Windows group

EXEC INTO #DatabaseUsers sp_MSforeachdb 'select ''?'',

DatabaseUsers.name as DatabaseUserName, suser_sname( DatabaseUsers.SID) as LoginName

from [?].sys.database_principals as DatabaseUsers

where DatabaseUsers.type in (''S'',''G'',''U'')'

SELECT * FROM #DatabaseUsers

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Sorry had a typo and the edit feature of the site never works from here