Scripting all MS SQL Databases using SMOscript

SMOscript user Eric reported that the tool fails to script all remaining objects of a database once an error occurs trying to generate a CREATE PROCEDURE statement for an encrypted stored procedure.

This problem has been fixed in the latest version 0.14 which is now available for download.

Based on his feedback I want to demonstrate more capabilities of SMOscript:

Logging

While SMOscript does not implement log file functionality, you can still redirect its output to files from the command line using the standard shell redirectors >, >> and 2>.

smoscript ...parameters... >filename.log 2>filename.err

Scripting all databases on a server

To script all databases of a server into separate files in distinct directories, you can use SMOscript to first list all databases, then invoke SMOscript to script each database in the original list:

set basepath=c:\path\to\output\
set first=

for /f in "usebackq" %%i in (`smoscript -s localhost`) do ↵
                                         (set db=%%i& call :smo)
goto :end

:smo

if "%first%"=="." (
    echo database %db%
    mkdir %basepath%%db%
    smoscript -s localhost -d %db% -f %basepath%%db%\create.sql ↵
                                         > %basepath%%db%.log
)
set first=.

goto :eof
:end

The first line of “smoscript -s” is not a database name, so we include it from the list by using the variable named “first”.

The simple combination of SMOscript with a bit of shell magic can be quite efficient.

Schema-Qualified Foreign Keys in SMOscript

The MSSQL SMO library has some funny functionality and options (depending on your sense of humor).

If you tell an Smo.Table to script itself and its dependent objects (indexes, constraints, etc.) using its Script(ScriptingOptions) method, it will correctly generate the table name in Schema.Table notation (schema qualified), but the foreign key constraints to other tables will not contain the schema of the referenced table.

That is, unless you set the ScriptingOptions.SchemaQualifyForeignKeyReferences member to true. Thanks to user Oliver for pointing that out.

SMOscript has now been fixed and is available for download here.

Synching SQL Server Databases

Synchronizing developer databases and synchronizing production with development databases are recurring topics at Stackoverflow:

• keeping sql database schemas synced between developers

small dev team, and “The biggest issue that we are having now is that our DB schema is totally out of sync between all of us”

• Syncing development/production databases

“My main concern is deploying schema + data updates to developer machines from the “master” development copy.”

Databases under version control is a more difficult problem than version control for regular source code, as you not only need

• the SQL source of the data model, but also
• a change script to upgrade from one version to the next,
• and populate application tables with pre-defined values (constants).

I think that dbscript and SMOscript are two useful tools which can help you to address these issues (which is why I wrote them and use them).

SMOscript lets you script all database objects that are scriptable through SMO. You can script the objects to a single file, or as single files in subdirectories named after their type (Table, View, StoredProcedure, etc)

dbscript helps you with the other two items:

Import the current database schemas of two SQL Server databases, and have dbscript compare them. You can then select which changes (on objects and column level) to include in the change script, and have dbscript generate the migration script.

Instead of importing from a database, you can also upload a SQL file containing the schema definition.

Based on the schema data, you can then define all your system tables (those tables which hold pre-defined “magic” values), and let dbscript generate a stored procedure which will script the data as Insert/Update statements, or have the live data scripted directly by dbscript.

To keep your code in sync with database values, you can also create C# constants from your database values.

Last not least, let’s deal with the question of how to keep several programmers in sync.

dbscript keeps all data (projects, schemas, versions, objects) in a database, and lets multiple users work in the same project. Project Versions can be tagged as “development”, “test”, “beta”, “minor”, “major” and other stages. If versions are tagged, they are visible to every member of the project; if they are not tagged, they are only visible to the user who created them.

A strategy to handle multiple developers in dbscript could be implemented as follows:

• Schemas/Versions of the master development database are tagged
• Developer generates migration script to sync his development database with the latest master database version
• Developer applies his changes locally
• Developer uploads or imports private database schema without tagging the created version
• If the master schema has changed since the developer retrieved it, apply change scripts from previous to current master development database schema.
• Developer compares private database schema with latest master schema
• Developer modifies value generation scripts
• Developer generates migration script, and applies the script to the master database
• Developer imports master schema and tags the version

dbscript is available for download here.

Standalone SMO 2008 download

The Microsoft SQL Server 2008 Feature Pack (link to April 2009 version) contains tools and libraries for SQL Server 2008, among them the latest version of SQL Server Management Objects (SMO) which support 2000, 2005, and 2008.

SMO is the technology used by my applications SMOscript and automssqlbackup to query and control SQL Server databases.

Listing database properties using SMO

Recently I came across a couple of questions asking how to retrieve the physical file names of databases via SMO on Stackoverflow.

These questions prompted me to add a “List Database Properties” function to SMOscript.

This new function lists all database properties accessible via SMO, the DatabaseOptions object, and the database’s FileGroups and Files.

Sample output:

C:\Projects\smoscript>smoscript -s localhost -d dbscript2dev db
Database dbscript2dev on server localhost:

Current user dbo is DBO DbOwner

Owner DOMAIN\User Created 26.03.2008 21:23
Accessible True Updatable True SystemObject False
Status Normal CompatibilityLevel Version80
Size 386 MB SpaceAvailable 148.694 kB
DataSpaceUsage 133.016 kB IndexSpaceUsage 15.984 kB
PrimaryFilePath C:\Programme\Microsoft SQL Server\MSSQL\data
Default FileGroup PRIMARY Schema dbo
Collation Latin1_General_CI_AS CaseSensitive False
FullTextEnabled False
LastBackup 20.06.2009 03:01
LastLogBackup 11.09.2008 23:50
ActiveConnections 0

Options AnsiNullDefault False AnsiNullsEnabled False AnsiPaddingEnabled False An
siWarningsEnabled False ArithmeticAbortEnabled False AutoClose False AutoCreateS
tatistics True AutoShrink False AutoUpdateStatistics True CloseCursorsOnCommitEn
abled False ConcatenateNullYieldsNull False DatabaseOwnershipChaining True Local
CursorsDefault False NumericRoundAbortEnabled False PageVerify TornPageDetection
 QuotedIdentifiersEnabled False RecoveryModel Full RecursiveTriggersEnabled True
 UserAccess Multiple

Filegroup PRIMARY
 Default True ReadOnly False Size 318.208 MB
 File dbscriptdev_Data
 Path C:\Programme\Microsoft SQL Server\MSSQL\data\dbscript2dev.mdf
 Primary True
 Size 318.208 MB MaxSize unlimited
 Used 176.768 kB Available 141.440 kB
 Growth 10 %

Logfile dbscriptdev_Log
 Path C:\Programme\Microsoft SQL Server\MSSQL\data\dbscript2dev_log.ldf
 Size 76.736 MB MaxSize unlimited
 Used 70.090 kB
 Growth 10 %

The latest version 0.12 of SMOscript is available for download here.

SMOscript: Scripting database objects using SMO

I created SMOscript a couple of months ago with the intention to make it a replacement for the SQL Server 2000 tool called scptxfr.

After installing SQL Server 2008 and AdventureWorks, I found that, besides some other shortcomings, SMOscript would not connect to an SQL Server 2008. Clearly time for an update!

The new version of SMOscript provides these improvements and new features:

The database dictionary is iterated faster by calling SetDefaultInitFields.

CREATE scripts now include child objects such as constraints and triggers as a result of SetDefaultInitFields.

SMOscript scripts all scriptable database objects as defined by SMO API. (The previous version only scripted tables, views, synonyms, stored procedures, and functions)

SMOscript now supports SQL Server 2008.

The latest version of SMOscript is available for download here.

Note: This is a command-line tool. You won’t see an icon under Start/Programs!

Forum open now

I set up a forum for all of my software that I describe in this blog:

• dbscript
• graspx
• automssqlbackup
• SMOscript

Please feel free to register and post.

One Year devioblog – a Summary

I started this blog one year ago to write about topics that I deal with in my software projects, mostly about MS SQL Server and Asp.Net programming.

Since September 2007, this activity generated 71 posts (I did not realize I was publishing an article about every 5 days!) and 20.000 views (says my stats page).

In this time, I also released 3 freeware programs to the public: SchemaFind, graspx, and SMOscript (downloads here).

From the list of Top Posts, my personal favorites are those about automatically building Visual Studio solutions and automated project releases here, here, here, and here.

Sometimes I also documented software installation procedures if I thought I had run into unusual problems: TRAC, Bugzilla, or GForge.

And occasionally I was simply enjoying working with Visual Studio (2005), SQL Server (2005), and C#.

To be continued…

Introducing SMOscript

SQL Server 2000 provides a tool called scptxfr which generates DROP and CREATE scripts for all objects in a database. Unfortunately it is not included in SQL Server 2005.

In the batch files I described in my posts about automating the build process, I also call it to generate a script for all database objects which is then used in version control.

Since it is not included in the latest SQL Server versions, I developed a replacement for scptxfr which uses the .Net SMO libraries for script generation called SMOscript:

smoscript 0.10.3169.17676 (c) by devio.at 2008

    list and script databases and database objects.

    usage: smoscript [options] [command]

    options: (leading '-' or '/')

    -s server       server name
    -d database     database name
    -u username     username (default: integrated authentication)
    -p password     password (if -u is missing, password for sa)

    -r              generate DROP statements
    -i              include IF NOT EXISTS statements

    -f filename     output to file
    -F directory    output to directory

    -A              current ANSI codepage
    -O              ASCII
    -T              Unicode
    -U              UTF8

    commands:

    l               list
    s               script object/s

    list databases on server (implied by -s)
    list objects in database (implied by -s -d)
    script object (implied by -s -d -o)
    script all objects (implied by -s -d -F/-f)

The SMOscript utility implements the following functions:

• list all databases on a server
• list all objects in a database
• script CREATE or DROP statements for each object in database

Resulting script are either written to a single file, or to a separate file for each object.

Command line switches were selected to be compatible with scptxfr, but support both “-” and “/” as switch marker.

SMOscript is available for download here.