Microsoft SQL Server™ Assistant

A plugin for SQuirreL SQL Client (http://squirrel-sql.sourceforge.net)
Developed by Ryan Walberg <generalpf@yahoo.com>
With many thanks to Colin Bell

Table of Contents

1. Introduction
2. Features
3. To Do

Introduction

Microsoft SQL Server™ Assistant is a plugin for the excellent SQuirreL SQL Client. It it designed for SQL Server 2000 and performs MSSQL-specific commands with a few clicks. It is ultimately designed to incorporate all functionality from both SQL Enterprise Manager and Query Analyzer.

The plugin does not interact with system tables, which are not guaranteed to stay the same between MSSQL versions. Instead, it uses the system stored procedures.

The plugin has only been tested with the jTDS driver (http://jtds.sourceforge.net). I assume it will work with Microsoft's JDBC driver, but given that Microsoft's JDBC driver isn't fully functional, I don't recommend it.

I'd like to stress that this is your plugin; unless you tell me what you want it to do, it may not ever get done. Feel more than free to email me with feature requests.

Features

• Right-clicking a database and selecting SQL Server brings up a submenu with the following options:

  Shrink Database

  Performs a DBCC SHRINKDATABASE on the selected database(s).

  Truncate Log

  Performs a BACKUP LOG ... WITH TRUNCATE_ONLY on the selected database(s).

  Shrink Database File

  Produces a dynamically-generated submenu containing a menu item for each data/log file in the database, along with its size. A separator bar divides the data files from the log files. Performs a DBCC SHRINKFILE on the selected file.
  WARNING: Shrinking a log file will truncate it!

• Right-clicking a table and selecting SQL Server brings up a submenu with the following options:

  Update Statistics

  Performs an UPDATE STATISTICS on the selected table(s). This only works if the active catalog is the table's catalog.

  Show Statistics

  Produces a dynamically-generated submenu containing a menu item for each index on the table. Performs a DBCC SHOW_STATISTICS on the selected index.

  Defragment Index

  Produces a dynamically-generated submenu containing a menu item for each index on the table. Performs a DBCC INDEXDEFRAG on the selected index.

• Right-clicking a procedure and selecting SQL Server brings up a submenu with the following options:

  Script Procedure

  Copies the selected procedure(s)' CREATE PROCEDURE scripts to the SQL pane. Does not execute it.

  Execute Procedure

  Creates variable declarations and an EXECUTE statement for the selected procedure(s).
  NOTE: Since JDBC considers a user-defined function to be a procedure, they can be acted upon in this manner, but the plugin will only script out a comment about it.
  WARNING: Since output parameters are not required to be called as output, the plugin does not script those parameters for output.

• When using a Microsoft SQL Server™ session, under the Session menu are the above options and more:

  Generate T-SQL Script

  Brings up a copy of the SQL Server Enterprise Manager's Generate SQL Script dialog. All implemented options are enabled; disabled options aren't implemented yet. See the To Do section for restrictions on this feature.

To Do

1. If your JDBC URL contains the DB name, you must have that same catalog active to use the Generate T-SQL Script feature. Most MSSQL system stored procedures will not work across catalogs, and for some reason, calling setCatalog() on the java.sql.Connection object will not alleviate this. If you don't specify the DB name in the URL, the feature will only work for that user's default database. You'll get errors like "table 'foo' does not exist in catalog 'quux'." If anyone knows how I might fix this, *please* let me know. I've tried everything.
2. Generate T-SQL Script doesn't do column-level permissions (yet).
3. Generate T-SQL Script doesn't generate the sp_dboption statements (yet).
4. Generate T-SQL Script doesn't yet obey descending columns on primary keys. It does understand such columns on all other indexes, however.