Use SQL-DMO objects in VBScript
When I first heard about SQL Distributed Management Objects (SQL-DMO), I thought, Who needs another way to manage databases when I've got T-SQL and Enterprise Manager? But when I needed to manage many servers hosting large, complex databases, I discovered the power of using SQL-DMO objects embedded in Windows Script Host (WSH) scripts. (For more information about SQL-DMO, see Jason Fisher, "SQL-DMO Picks Up Where Enterprise Manager Leaves Off," February 2001.) Let's look at some real-world scripts that use SQL-DMO objects and that can help you manage an enterprise environment where T-SQL scripts and Enterprise Manager aren't enough.
SQL-DMO objects can perform all Enterprise Manager functions and virtually all T-SQL functions. When you wrap SQL-DMO objects in Visual Basic for Applications (VBA), Visual Basic (VB), or either of the WSH languages (VBScript and JScript), they can do jobs—such as performing an iterative action on a large number of objects scattered over many databases and servers—that are beyond the capabilities of Enterprise Manager and T-SQL. Scripts that combine SQL-DMO objects with the rich features that the host language provides are useful when you need to interact with files, registries, networks, and so on.
I used VBScript to write the code in my examples and WSH to execute it. WSH is a technology that ships with Windows 2000 and Windows 98 and lets the OS execute scripts written in either VBScript or JScript. If you don't have WSH on your system, you can download it free from the Microsoft Windows Script Technologies Web site (http://msdn.microsoft.com/scripting). You can also copy these scripts into an ActiveX Script task in a Data Transformation Services (DTS) package, into an Active Server Pages (ASP) page, or into a SQLServerAgent service job step (in SQL Server 2000) with few modifications.
In this article, I examine scripts that a DBA can use to tackle simple but time-consuming jobs. These scripts are quick examples for one-time jobs, but you can modify the scripts for more permanent solutions such as scheduled DTS packages. Good DBAs have a toolbox of past scripts that they can quickly copy and change for any occasion.
Change Owner of All Tables in Database
When multiple users (who aren't sa or in the systems administrator's role) create objects (e.g., tables, views, stored procedures) without explicitly declaring database owner (dbo) as the owner, the user who creates the object owns it. Any other user who references the object must use the fully qualified name, such as fred.FredsTable instead of FredsTable.
The stored procedure sp_changeobjectowner works well for one object at a time, but what if you need to change the ownership of many objects? The script in Listing 1, page 50, changes the owner of all Pubs database tables to dbo. The first line creates an instance of the oServer class. The second line sets the oServer object's LoginSecure property to True to allow a Windows authentication connection. For SQL Server authentication, set the LoginSecure property to False (the default). The third line uses the Connect method to connect to the server in the first argument. For SQL Server authentication, the login name is the second argument and the password (in quotes) is the third.
The next line creates a Pubs database object. The For...Each loop iterates through the Tables collection of the Pubs database object and checks to determine whether each table object's Owner property equals dbo. If not, the code sets the property to dbo and outputs the name of the table that the owner changed.
If a user double-clicks a .vbs file in Windows Explorer, by default each Wscript.Echo statement opens a message box that requires acknowledgment. I recommend running .vbs scripts as arguments to the command line's cscript (C:\>cscriptMyScript.vbs). After you run the script, Wscript.Echo writes the output to the command-line window. UNIX users will feel at home with this method; you can pipe the output to More or redirect it to a file.
Get Version Information from List of Servers
To apply a script to more than one server, you can feed a list of servers to the script from a text file, as Listing 2 shows. This script's output displays the SQL Server version and service pack level. The first four statements provide error handling and a graceful exit if no argument is present. The next two lines create a SQLServer object and set its LoginSecure property to True. Then, the script creates a FileSystemObject class object, which isn't part of SQL-DMO but demonstrates the flexibility and the ease of integrating SQL-DMO with the native object libraries that VBScript and JScript include.