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