Execute SQL-DMO Database method on all databases

,

This script allows you to specify a method and a server (or multiple servers.) It will then execute the method on every database on the server. With very minor tweaking you could have it search for the servers, then call the server db script with each one, or add another nesting to call tables or whatever other object you like. You could even switch up the eval statements a little and call a generic object/method caller. Tested on sql2k with just a few databases.

<?xml version="1.0"?>

<package>
<job id="main">

<resource id="Name">ExecuteMethodOnServer[s].wsf</resource>
<resource id="Version">0.1</resource>
<resource id="Author">Roy Ashbrook</resource> 
<resource id="Email">RoyAshbrook@Yahoo.com</resource> 

<comment>performs a given sqldmo method on every database on a server</comment>

<runtime>
<unnamed
name="Method"
helpstring="name of method"
required="true"
type="string"
/>
<unnamed
name="Server"
helpstring="name of server or servers"
required="true"
many="true"
type="string"
/>
<description>
Performs a given sqldmo database object method on every database object on the given server or servers.
</description>
<example>
Example:

1. ExecuteMethodOnServer[s].wsf UpdateIndexStatistics Server1
2. ExecuteMethodOnServer[s].wsf UpdateIndexStatistics Server1 Server2 Server3

Note:

1. Some special characters may not work properly when passed as arguments.
2. Script currently only setup for windows authentication.

Methods:
1. UpdateIndexStatistics
2. Shrink
3. RecalcSpaceUsage
... more
check BOL for database object SQL-DMO methods.
</example>
</runtime>

<object id="objServer" progid="SQLDMO.SQLServer" events="true" reference="true" />

<script language="VBScript">
<![CDATA[

'strict
Option Explicit

'check args
if not WScript.Arguments.Unnamed.Count >= 2 and WScript.Arguments.Named.Count = 0 then
WScript.Arguments.ShowUsage()
WScript.Quit
end if

'call main sub
Main()

'main sub
Sub Main()

'dim
dim strServerName, strMethod, i

'get method name
strMethod = WScript.Arguments.Unnamed.Item(0)

'process each server given
for i = 1 to WScript.Arguments.Unnamed.Count-1

'get current server name
strServerName = WScript.Arguments.Unnamed.Item(i)

'perform method
PerformMethod strServerName, strMethod
next

End Sub

'this sub performs a given method on every database on a given server
Sub PerformMethod(strServerName, strMethod)

'dims
dim objDatabase

'connect to server
objServer.LoginSecure = TRUE
'objServer.Connect strServerName, "user", "password"

'iterate through databases
for each objDatabase in objServer.Databases

'custom error checking
on error resume next

'execute the method
Eval ("objDatabase." & strMethod)

'echo status
wscript.echo _
"Successful execution of method (" & strMethod & ") " & _
"on database (" & objDatabase.Name & ")? --- " & Cstr(isobject(err))

'return error checking to normal
on error goto 0

next

objServer.Disconnect

End Sub
]]>
</script>
</job>
</package>

Rate

Share

Share

Rate