“I hear and I forget. I see and I remember. I do and I understand.” – Confucius.
As SQL Server developers, we often find ourselves in situations where we need more functions and tools to efficiently develop and debug stored procedures, functions, batches, and ad-hoc reports. Jumping between windows, sessions, and tools can be time-consuming and inefficient.
Let’s consider a scenario where we want to view all the columns of a table in the Object Browser. Unfortunately, we cannot see if a column has the identity property set and the seed and increment values. Although we can generate the create table statement to view this information, it involves extra steps and time.
Now, let’s 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) )
Personally, I prefer the second format. However, it’s important to note that no matter what format you use to create a table, Query Analyzer or Enterprise Manager will always return your code in the first format. This is why I use, develop, and modify my scripts and save them in Visual Source Safe. This allows me to open my script in the format I developed it, which is crucial for maintaining consistency.
While there are third-party tools available for generating code from a database (reverse engineering), they may not provide all the necessary features. Additionally, it’s worth mentioning that SQL Server 2005 may not have these features.
When writing or debugging a stored procedure or Transact-SQL script, it’s often necessary to have information about a table, such as column names and data types, readily available in different formats. To streamline the typing process, you can use a user stored procedure called up_CT_Q (up stands for user procedure, CT stands for Cut Typing, Q stands for Query, indicating that no Insert, Update, or Delete statements are used in this procedure).
The stored procedure up_CT_Q returns table or view information in various formats. Let’s explore some examples using the Customers table in the Northwind database:
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 format is useful for quickly checking the datatype and column length. It can be used as an input parameters list for a stored procedure or in a create table statement for reverse engineering when a table needs to be dropped and created again with a 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 format 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 a table contains a column with the identity property, you should list all non-null columns in an Insert statement. This format also makes it easier to comment out a column if needed. While SQL Query Analyzer can generate a select statement, it becomes difficult to read and modify when a table has multiple columns. As a best practice, each line of a script should fit within the width of the screen.
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 an alias name. This format is useful for various 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 format makes it easier to rename columns when the output should have different names than the table.
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 the “where” clause of Select, Update, or Delete statements in a stored procedure.
Format 5, no input parameters provided:
To get the procedure description, simply run it without any parameters. This will provide the following information:
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 an input parameters list for 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 a 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 prefixed by an alias name. FormatNumber = 3 Type 3: makes it easier to rename columns. FormatNumber = 4: used in the "where" clause of Select, Update, or Delete statements. If the table name is not supplied, the procedure returns the description and usage examples of this procedure. Examples, using the Customers table in the Northwind database: 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
By utilizing the up_CT_Q stored procedure, we can enhance the functionality of SQL Server Query Analyzer and simplify the script writing and debugging processes. This procedure provides various output formats that cater to different needs, making it easier to work with tables and columns.
Remember, as developers, it’s important to find ways to optimize our workflow and save time. The up_CT_Q stored procedure is just one example of how we can achieve this in SQL Server.
Thank you for reading!