Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Query Analyzer Extended

By Yakov Shlafman,

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.

Total article views: 16012 | Views in the last 30 days: 4
 
Related Articles
FORUM

how can i use declare text in sql store procedure

using text declare field in store procedure

FORUM

Format column

Format column

FORUM

date format in stored procedure

date format in stored procedure

FORUM

Identifying ASCII characters in NVARCHAR columns

Identifying ASCII characters in NVARCHAR columns

FORUM

FK selection (an nvarchar(30) column or four int columns)

FK selection (an nvarchar(30) column or four int columns)

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones