Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1445

Re: Needed to create a SQL script based on coloum in the domain table in a SystDB Database

Show all headers | View raw

Sonny (hairnhenna@gmail.com) writes:
> I have a system which has 200+ databases and needed to run the following
> script automatically on each one of them. The 200 databases names can be
> found in the databasename coloum in the domain table in one of the
> databases which is the system database. So if I run the following script
> i get all the databases that I need to run the scripts below on. 

What other languages beside T-SQL do you master? About any other of them 
would be better suited for the task.

But you can do (note that this is untested, and is likely to include typos):

DECLARE @sp_executesql nvarchar(200),
        @sp_addrolemember nvarchar(200)

DECLARE cur CURSOR STATIC LOCAL FOR
   SELECT  quotename(databasename) + '..sp_executesql',
           quotename(databasename) + '..sp_addrolemember'
   FROM    domain

OPEN cur
 
WHILE 1 = 1
BEGIN
   FETCH cur INTO @sp_executesql, @sp_addrolemember
   IF @@fetch_status <> 0 
      BREAK

   EXEC @sp_executesql N'CREATE USER [Master60SP] FOR LOGIN [Master60SP]'
   EXEC @sp_executesql N'ALTER USER [Master60SP] WITH 
DEFAULT_SCHEMA=[Master60SP]'
   EXEC @sp_executesql N'CREATE SCHEMA [Master60SP] AUTHORIZATION 
[Master60SP]'
   EXEC @sp_addrolemember N'db_datareader', N'Master60SP'
   EXEC @sp_addrolemember N'MSDynamicsSL', N'Master60SP'
END

DEALLOCATE cur

The key here is that you can specify the procedure to execute through a 
variable. And when you call a system procedure in three-part notation, it 
executes in the context of that database.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Needed to create a  SQL script based on coloum in the domain table in a  SystDB Database Sonny <hairnhenna@gmail.com> - 2013-04-14 18:55 -0700
  Re: Needed to create a SQL script based on coloum in the domain table in a SystDB Database Erland Sommarskog <esquel@sommarskog.se> - 2013-04-15 07:31 +0000

csiph-web