|
Executive Summary: You can use AbaPerls, a set of command-line tools, to quickly automate and script the development, configuration management, and implementation of SQL Server databases and schema objects. AbaPerls offers both development (e.g., ABASQL) and database management features (e.g., DBBUILD). AbaPerls requires SQL Server 2000 and later, and Microsoft Visual SourceSafe 2005 and 6.0. |
This month’s cool, free tool is called AbaPerls, which was written by long-time SQL Server MVP Erland Sommarskog of Sweden. As its name implies, AbaPerls is a collection of command-line tools written in Perl. These tools aid in the development, configuration management, and implementation of SQL Server databases and schema objects by making automating and scripting these operations fast and easy.
Development Features
AbaPerls is a rich tool with features that fall in a lot of different areas. In the area of SQL development, AbaPerls includes a command-line tool called ABASQL. ABASQL lets you automate often difficult operations, such as interacting with source code libraries and running scripts automatically when the script depends on many variables that might change at the time of execution, than you could with tools such as Query Analyzer, sqlcmd.exe, or SQL Server Management Studio. AbaPerls also includes the following development features:
- An object checker that avoids deferred-name resolution.
- A means of including IF EXISTS ... DROP (and explicit GRANT statements) in your files if they aren’t already present.
- A feature that quickly encrypts T-SQL code.
- The auto-replacement of temporary tables and table variables with their definitions in your SQL scripts, so you don't need to have any types in tempdb.
- The automatic addition of COLLATE clauses to character columns in temporary tables and table variables, facilitating truly mixed collations on a server.
- A preprocessor (called Preppis) that acts like a C processor to allow the use of conditional compilation and include files.
- The ability to invoke ABASQL from an external text editor such as TextPad.
Database Management Features
AbaPerls facilitates the building and updating of databases using its DBBUILD tool. DBBuild integrates with Microsoft Visual SourceSafe and builds an empty database in the optimal object order using the projects and subprojects from ABASQL. It also automatically extracts errors and warnings into a log file for you. The DBUPDGEN tool lets you compare two SourceSafe versions of a project tree and generate an update script from the differences.
You can also configure the database build process to install and update subsets of the database together using an AbaPerls configuration file. For example, you can specify that all stored procedures and user-defined functions be encrypted before they’re stored, while other objects aren’t encrypted. You can view the configuration file using the LISTCONFIG tool.
AbaPerls also includes a data loading feature called INSFILGEN, which reads a Microsoft Excel workbook and creates all the INSERT statements needed to insert that data into a SQL Server database. In addition, AbaPerls includes the following Visual SourceSafe tools, which facilitate search and replace operations:
- SSGREP searches Visual SourceSafe for one or more regular expressions using Perl syntax, with the ability to ignore comments. SSGREP output is placed in a plain text or HTML file.
- SSREPLACE searches and replaces strings in Visual SourceSafe files by checking out the files and editing them. This tool also ignores comments. One downside to SSREPLACE is that you must manually check the altered files back into Visual SourceSafe (and you’ll want to spot check the changes made).
There are several other tools available in AbaPerls. DOBCP does a fast BCP load in or out of all the tables in a database. The TBLCNT tool counts the number of records in all your tables, which is useful in conjunction with DOBCP. TBLFIX is a rather limited tool that extracts all table and type definitions for SQL scripts and places the definitions into 1-3 files per table under the AbaPerls file structure. (For more information about the AbaPerls file structure, see the tool’s documentation at www.sommarskog.se/AbaPerls/doc/index.html.) SPFIX does the same thing for stored procedures. RUNSPS reads a comma-delimited file containing the names of stored procedures and all their parameters and executes them en masse.
System Requirements
Although some of AbaPerls features are a bit quirky because they were developed for an inhouse project with a developer close-at-hand, its usefulness is without question. AbaPerls works with SQL Server 2000 and later, and SourceSafe 2005 and Visual SourceSafe 6.0. AbaPerls runs on any OS that supports SQL Server 2000 and later. In addition, AbaPerls creates a SQL Server database for tracking and meta-objects, so you’ll need to designate a database for that purpose. You can download lots of other great articles and information by Sommarskog from www.sommarskog.se/index.html.
|
YourSQLDba Benefits: AbaPerls makes scripting and automating the development, configuration management, and implementation of databases and schema objects fast and easy. System Requirements/Notes: SQL Server 2000 or later; Microsoft Visual SourceSafe 2005 or Visual SourceSafe 6.0 How to Get It: You can download AbaPerls from www.sommarskog.se/AbaPerls/index.html. |
