The SQL QUERY ANALYZER extended. Yakov Shlafman.

"I hear and I forget. I see and I remember. I do and I understand." - Confucius.

Although the SQL Query Analyzer and all its tools provide a very large spectrum of functions in some situations, you may feel you need more functions and more tools. Why? While developing or debugging stored procedures, functions, batches, add-hoc reports and others Transact-SQL related scripts, you want to have all necessary tools available on the current screen. If you have them, you do not need to jump between windows, sessions and tools. You do not want to waste your time.

Here is a little example: In the Object Browser you can see all the columns of a table, but you cannot see if a column has the identity property set and seed and increment values. Yes, you can generate the create table statement the to clipboard or to a new window, but these are extra steps and extra time...

Now, please compare two create table statements: First format, Generated by SQL Query Analyzer

CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE Cyrillic_General_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[ShipName] [nvarchar] (40) COLLATE Cyrillic_General_CI_AS NULL ,
   [ShipAddress] [nvarchar] (60) COLLATE Cyrillic_General_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL
)
GO

Second format, Generated by user stored procedure:

create table Orders (
OrderID            int
,CustomerID         nchar(5)
,EmployeeID         int
,OrderDate          datetime
,RequiredDate       datetime
,ShippedDate        datetime
,ShipVia            int
,Freight            money
,ShipName           nvarchar(40)
,ShipAddress        nvarchar(60)
,ShipCity           nvarchar(15)
,ShipRegion         nvarchar(15)
,ShipPostalCode     nvarchar(10)
,ShipCountry        nvarchar(15)
)

I prefer the second format. You should be aware - no matter what format you use to create a table Query Analyzer or Enterprise Manager will always return your code in First format. This is one of the reasons why I use, develop and modify my scripts and safe them in Visual Source Safe. I could open my script in the format I developed it and this is important... There are third party tools to generate your code from database (reverse engineering) and they do great job but it's not enough... And please do not tell me that in SQL Server 2005 we are going to miss these features again.

To write or debug a stored procedure or Transact-SQL script you may need information about a table - column names and data types. This information should be available for use in different formats. To speed up the typing process, you can use user stored procedure up_CT_Q (up stands for user procedure, CT stands for Cut Typing, Q stands for Query and informs that no Insert, Update or Delete statements are used in this procedure). Stored procedure up_CT_Q (SBE001_STP01_CT_Q.SQL) returns table or view information in needed formats. All examples below were ran in Northwind database against the table Customers.

Here is the description of different formats of output generated by procedure up_CT_Q:

------------
Format 1
------------
CustomerID       nchar(5)
,CompanyName      nvarchar(40)
,ContactName      nvarchar(30)
,ContactTitle     nvarchar(30)
,Address          nvarchar(60)
,City             nvarchar(15)
,Region           nvarchar(15)
,PostalCode       nvarchar(10)
,Country          nvarchar(15)
,Phone            nvarchar(24)
,Fax              nvarchar(24)

This is useful for a quick check of datatype and column length. It could be used as input parameters list of a stored procedure. Could be used in create table statement for Reverse Engineering when table should be dropped and created again with modified definition.

------------
Format 2
------------
Declare @CustomerID       nchar(5)
Declare @CompanyName      nvarchar(40)
Declare @ContactName      nvarchar(30)
Declare @ContactTitle     nvarchar(30)
Declare @Address          nvarchar(60)
Declare @City             nvarchar(15)
Declare @Region           nvarchar(15)
Declare @PostalCode       nvarchar(10)
Declare @Country          nvarchar(15)
Declare @Phone            nvarchar(24)
Declare @Fax              nvarchar(24)

This is useful for declaring variables in a stored procedure.

----------------------
Format 3, type 1
----------------------
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax

In this format a Select or Insert statement is easier to read and manage. If table contains a column with identity property you should list all not null columns in an Insert statement. In this format it's easier to comment out a column(s) if needed. You could get a select statement generated by SQL Query Analyzer but if a table has multiple columns this select will be difficult to read and modify. The rule number 1 of my standards is – any line of a script shoud feet into the width of the scrieen.

----------------------
Format 3, type 2
----------------------
Cust.CustomerID
,Cust.CompanyName
,Cust.ContactName
,Cust.ContactTitle
,Cust.Address
,Cust.City
,Cust.Region
,Cust.PostalCode
,Cust.Country
,Cust.Phone
,Cust.Fax

In addition to type 1, all column names are prefixed by alias name. Useful for all kinds of joins and subqueries.

----------------------
Format 3 type 3
----------------------
Cust.CustomerID       as [CustomerID]
,Cust.CompanyName      as [CompanyName]
,Cust.ContactName      as [ContactName]
,Cust.ContactTitle     as [ContactTitle]
,Cust.Address          as [Address]
,Cust.City             as [City]
,Cust.Region           as [Region]
,Cust.PostalCode       as [PostalCode]
,Cust.Country          as [Country]
,Cust.Phone            as [Phone]
,Cust.Fax              as [Fax]

This makes easier the column renaming coding when the output should have column names

different then in the tables.

----------------------
Format 4
----------------------
CustomerID       = @CustomerID
,CompanyName      = @CompanyName
,ContactName      = @ContactName
,ContactTitle     = @ContactTitle
,Address          = @Address
,City             = @City
,Region           = @Region
,PostalCode       = @PostalCode
,Country          = @Country
,Phone            = @Phone
,Fax              = @Fax

This format is used in "where" clause of Select, Update or Delete statement in stored procedure.

-------------------------------------------
Format 5, no input parameters provided
-------------------------------------------

To get procedure description run it without any parameters. You will get:

----------------------------
DOCUMENTATION and USAGE
----------------------------
The user defined stored procedure up_CT_Q is used for Reverse Engineering and
for simplifying the script writing and debugging processes.
The first input parameter is the table name
The second input parameter is the Format Number that represents the format 
of the output.
FormatNumber = 1, column name and its datatype list is returned.
This is useful as input parameters list of a stored procedure
FormatNumber = 2, column name prefixed by @ sign.
useful for declaring variables in a stored procedure
FormatNumber = 3 Type 1, returns all column names, in vertical, one column 
table format.
In this format a Select or Insert statement is easier to read and manage if 
there is a
need to comment out a column
FormatNumber = 3 Type 2, in addition to type 1, all column names are prfixed 
by alias name.
FormatNumber = 3 Type 3, makes easier the column renaming coding.
FormatNumber = 4, is used in "where" clause of Select, Update or Delete 
statement.
If table name is not supplied the procedure returns the description and 
usage examples of this procedure.
Examples, using table Customers in Northwind database
Examples:
Example 1, Format 1,                               exec up_CT_Q Customers,1
Example 2, Format 2,                               exec up_CT_Q Customers,2
Example 3, Format 3, Type 1                        exec up_CT_Q 
Customers,3,1
Example 3, Format 3, Type 2                        exec up_CT_Q 
Customers,3,2,Cust
Example 3, Format 3, Type 3                        exec up_CT_Q 
Customers,3,3,Cust
Example 4, Format 4,                               exec up_CT_Q Customers,4
Example 5, to get procedure description:           exec up_CT_Q

The source code is in the file SBE001_STP01_CT_Q.txt. This article could be a part of a "Standards by Examples" discussion. You may see how documentation standards are applied to the written procedure and its description of the script.