Perform a series of operations

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)

Perform a series of operations

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-09-26 : 14:50:46

Hi Everyone,

I have a script that is comprised of three blocks of cursors.

Each cursor block operates after the previous one sequentially.

I would like to wrap these three blocks in a master cursor, and execute the three blocks for each item in the master cursor.

The master cursor consists of all the databases on the server.

Each block performs a series of permission granting.

The question is...

How do i tell the detail block to use the database selected from
the master cursor??
OR
What do i need to change in the detail block to work with the database that is returned in the master cursor?

Here is the master cursor detail


FOR 
  SELECT [NAME] FROM sys.databases
  WHERE NAME NOT IN ('dbawork', 'QuestWorkDatabase', 'msdb', 'model', 'tempdb', 'master')
  ORDER BY [NAME]
OPEN curDatabases
FETCH NEXT FROM curDatabases INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN

One of the detail cursor blocks


DECLARE curTables CURSOR FAST_FORWARD 
FOR SELECT table_name
  FROM [INFORMATION_SCHEMA].tables
  ORDER BY table_name
OPEN curTables
FETCH NEXT FROM curTables INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    SET @sql = N'GRANT SELECT,INSERT,DELETE,UPDATE ON ' + @name + ' TO xxxuser;'
    PRINT @sql
    EXEC sp_executesql @sql
  END
  FETCH NEXT FROM curTables INTO @name
END
CLOSE curTables
DEALLOCATE curTables

thanks in advance

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-26 : 16:18:35

You are going to have to use three-part naming convention in your inner cursors, and you are going to have to execute them dynamically after inserting the database name using either concatenation or dynamic parameters.

Boycotted Beijing Olympics 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:18:52

i think you can do all of these using below. try it

EXEC sp_Msforeachdb 'USE ? EXEC sp_Msforeachtable ''GRANT SELECT,INSERT,DELETE,UPDATE ON ? TO xxxuser;'''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources