Ever feel left behind when one of those SQL Server jockies breezes through a complex query that generates SQL on the fly? After all its a bit over the top to use SQL to generate SQL, isn't it? Well, its time to level the playing field. I'm going to pass on a simple pattern that you can use to generate the most sophisticated queries. This pattern will lead to an endless supply of SQL/Database tools to add to your library.

Problem to Solve
Write a query that generates a CREATE TABLE definition from any table. Then write a query that generates INSERT statements to fill a copy of that table based on the current data.

So you are probably saying to yourself...Wouldn't just be easier to type the CREATE TABLE definition by hand? Maybe so for the first one, but imagine doing it for an entire database of 300+ tables. Or you may say...Couldn't I just use the T-SQL generator in Enterprise Manager (or equivalent)? Sure, but if you want to take your skills to the next level, its time to make an investment. Besides, does anyone else hate the excess baggage that those tools include in the generated SQL? Me too. So, dynamic SQL to the rescue.

Goals
I always try to make my SQL tools reusable and tight. I could do a lot of things using brute force CURSORS or WHILE loops in SQL, but I try to avoid that. So I'm going to give you a little gem that will aggregate the statement on-the-fly in one SQL statement. So without further delay....

Solution to the Problem
Paste the following query into a SQL Server Query Analyzer window and set the current database to Northwind. Set the output format to Text. Then run the query....


declare @vsSQL varchar(8000)
declare @vsTableName varchar(50)
select @vsTableName = 'Customers'

select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)

select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +
st.Name +
case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +
case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)
from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes st on st.xusertype = sc.xusertype
where so.name = @vsTableName
order by
sc.ColID

select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'


The resulting output will be a auto-generated CREATE TABLE statement:

CREATE TABLE Customers
(
CustomerID nchar(10) NOT NULL,
CompanyName nvarchar(80) NOT NULL,
ContactName nvarchar(60) NULL,
ContactTitle nvarchar(60) NULL,
Address nvarchar(120) NULL,
City nvarchar(30) NULL,
Region nvarchar(30) NULL,
PostalCode nvarchar(20) NULL,
Country nvarchar(30) NULL,
Phone nvarchar(48) NULL,
Fax nvarchar(48) NULL
)

Things to grok:

• Notice the nifty main SQL statement that aggregates the SQL string variable @vsSQL on-the-fly.
• Notice how to leverage the combination of sysobjects (for tables), (for column Des) and systypes (for data types properties).
• Notice the use of char(10) to append carriage returns to the SQL for decent formatting.
• Notice the use of the CASE statement to conditionally append the length. This could be used to handled scale/precision for FLOAT, REAL and DECIMAL data types.

Coming next on my blog:
See my next Blog post on how to "Dynamically generate INSERT statements" from the contents of a table. This is like gold when creating database migration scripts and especially for transferring data between varying SQL based platforms (SQL Server, Sybase, Oracle, Informix, etc.).

Now, go and impress your SQL jockey friends!