Home > Articles > Databases > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Data Management Objects: Database Information

Last updated Mar 28, 2003.

We're up to the point in this series of SQL-DMO programming articles where we can talk about creating useful code. In this article, I introduce VBScript file system operators and show you how to send information about a database to a log.

Let's begin by talking a bit about working with files. Earlier, I explained that the SQL-DMO library is really a .DLL file that has objects within it. Parent objects, we learned, have various properties and methods. Some of these "parent" objects (such as SQLServer) have "child" objects (such as Databases).

To work with files, we need to invoke the parent object for them, called Scripting. This isn't the only route to working with files; as a matter of fact, it's a fairly new one.

NOTE

You might run into references that point out some security implications for the Scripting object. If you're patched properly, however, and follow recommended security guidelines, you should have no problem with this object.

The Scripting parent object gives you easy access to the FileSystem child object, which allows you to work with files and directories. The FileSystem parent object, in turn, contains various operations that can create a child File object.

Not only can the FileSystem object create files, it can also read them, check to see if they exist, delete them, and so on. You can also work with drives using FileSystem. (A couple of references at the end of the article show you more about this useful object.)

But the file system isn't what we're after – it's just a means to an end. What we want to do is send the information about a database to somewhere a bit more permanent than a message box. Once you learn this technique, you can extend the concept into other applications.

I'll begin by outlining what I want the code to do, write the comments, and then code the comments.

First the outline. Here's what I came up with:

  1. Enter a server name to work with

  2. Connect to the server

  3. Access the "pubs" database

  4. Get several bits of information about the database

    • Create Date

    • Owner

    • Version

    • Status

    • File Path

    • Total Size

    • Data Space Used

    • Index Space Used

    • Space Available

    • Auto Close

    • Auto Create Stats

    • Auto Shrink

    • Auto Update Stats

    • Null Column Inserts

    • Null Compares

    • DBO Use Only

    • Trunc. on Ckpt.

    • Select Into/bulk copy

    • Last Log Backup

  5. Create a log file

    • Overwrite if it exists

  6. Show the data on screen as well

  7. Clean up

Notice that my requirements are the same database every time: pubs. You might want more flexibility in the database choice. The same holds true for the log file. To make those two hard-coded values into user choices, set up a variable for each, and then prompt the user for the information. Then you can use the return values in the proper locations.

With the outline complete, I'll write my comments and then code them:

' dmoDatabaseInfoToFile.vbs
' Created: 	12/27/04 - Buck Woody
' Change Log:	

' Variable to hold Server Name
Dim ServerNameVar

' Variables for the text boxes
Dim ServerDisplayVar
Dim MsgTxtVar

' Variables for objects
Dim oServer
Dim oDatabase
Dim oFileSystem
Dim oFile
Dim oMessageBox

' Ask for the name
ServerNameVar = InputBox("Enter Server Name:")
	If ServerNameVar = "" Then
		ServerNameVar = "(local)"

	End If

' Create the server object using SQL-DMO
Set oServer = CreateObject("SQLDMO.SQLServer2")
' Login with current Windows account
oServer.LoginSecure = True
oServer.Connect ServerNameVar

' Get a Database to work with - you could ask the user for one 
Set oDatabase = oServer.Databases("pubs")

' Fill out the info you want
MsgTxtVar = "Name:         " & oDatabase.name & VBCrLf _
	   & "Create Date:      " & oDatabase.CreateDate & VBCrLf _ 
	   & "Owner:         " & oDatabase.Owner & VBCrLf _ 
	   & "Version:        " & oDatabase.Version & VBCrLf _
	   & "Status:        " & oDatabase.Status & VBCrLf _
	   & "File Path:       " & oDatabase.PrimaryFilePath & VBCrLf _
	   & "Total Size:      " & oDatabase.Size & " MB" & VBCrLf _
	   & "Data Space Used:    " & oDatabase.DataSpaceUsage & " MB" & VBCrLf _
	   & "Index Space Used:   " & oDatabase.IndexSpaceUsage & " MB" & VBCrLf _
	   & "Space Available:    " & oDatabase.SpaceAvailableInMB & " MB" & VBCrLf _
	   & "Auto Close:      " & oDatabase.DBOption.AutoClose & VBCrLf _
	   & "Auto Create Stats:   " & oDatabase.DBOption.AutoCreateStat & VBCrLf _
	   & "Auto Shrink:      " & oDatabase.DBOption.AutoShrink & VBCrLf _
	   & "Auto Update Stats:   " & oDatabase.DBOption.AutoUpdateStat & VBCrLf _
	   & "Null Column Inserts:  " & oDatabase.DBOption.ColumnsNullByDefault & VBCrLf _
	   & "Null Compares:     " & oDatabase.DBOption.CompareNull & VBCrLf _
	   & "DBO Use Only:     " & oDatabase.DBOption.DBOUseOnly & VBCrLf _
	   & "Trunc. on Ckpt.:    " & oDatabase.DBOption.TruncateLogOnCheckpoint & VBCrLf _
	   & "Select Into/bulk copy: " & oDatabase.DBOption.SelectIntoBulkCopy & VBCrLf _
	   & "Last Log Backup:    " & oDatabase.TransactionLog.LastBackup
	   

' Create File System Object
Set oFileSystem = CreateObject("Scripting.FileSystemObject")

' Create a file with the Database name embedded
Set oFile = oFileSystem.CreateTextFile("C:\temp\Info.log")

' Write the Database Data
oFile.Write(MsgTxtVar)
oFile.Close

'Show the file
oMessageBox = MsgBox(MsgTxtVar,VBOK,"Log file contents for " & oDatabase.Name)

' Clean up
Set oFilesystem = Nothing
set oDatabase = Nothing
Set oServer = Nothing

Many of these variables are part of the Database child object, but a few (such as oDatabase.DBOption.SelectIntoBulkCopy) use child objects of Database.

The code writes a log file out to the temp directory. If you're running this code on your test system, make sure you have that directory first.

You can extend this code in many ways. I've used this kind of code with an Append operation to track the growth of a database over time. I run the code each day, pipe just the size results to a file, and then (once a month) I bring it into a spreadsheet. From there, I graph the data, and do estimations on trends.

Another extension on this type of code is to read the file rather than create one. The file can contain directives to perform a backup or some other maintenance. Your program runs with your privileges, and the users can create a file that you read to see if they want to perform a backup or some other action. In this way, you can allow them to perform only certain operations even if they don't have access to the database. The possibilities are endless.

In my next article, I'll show you how to use SQL-DMO to perform various operations on the database. See you then!

Online Resources

The full documentation for the SQL Server Object Library can be found here.

The Filesystem object model is here, and there's more information on the Filesystem object here.

You can bundle up your source code so that it runs and others can't read it. Microsoft has the details here.

Save To Your Account