Repeating a Script - SQL Server Forums

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)

Repeating a Script

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-15 : 04:54:53

I have a SQL Server that hosts lots of DB's and I would like to run a script against each database every month.

My script is below and everything is constand apart from the databasename. Is there a way to repeat this script on every database in the SQL Server without having to define each database name every time? The reason for this is DB's can be added/removed each month.

USE [DatabaseName] UPDATE USERS SET GROUPID = '9', UserDisabledDate = GETDATE() WHERE LASTACCESSDATE <=DATEADD(mm,DATEDIFF(mm,0,GETDATE())-18,0) AND USERDISABLEDDATE IS NULL

I am using SQL 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 04:57:17

You can use undocumented procedure

EXEC sp_msforeachdb 'UPDATE ?..USERS SET GROUPID = ''9'', UserDisabledDate = GETDATE() WHERE LASTACCESSDATE <=DATEADD(mm,DATEDIFF(mm,0,GETDATE())-18,0) AND USERDISABLEDDATE IS NULL'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 04:58:24

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-15 : 05:13:34

Thanks very much for that!

I am using the below but 2 of my databases are called commit and KEY which are used by SQL. How can I run it and tell it to put a [] around the DB names?

declare @sql varchar(max), @table_name varchar(100)
select @sql='', @table_name='your_table'
select @sql=@sql+ 'SELECT table_catalog FROM '+name+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''' from sys.databases
exec(@sql)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-15 : 07:12:26

Maybe I am missing something but....


declare @sql varchar(max), @table_name varchar(100)
select @sql='', @table_name='your_table'
select @sql=@sql+ 'SELECT table_catalog FROM ['+name+'].INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''' from sys.databases
exec(@sql)

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources