Create Table DDL via TSQL

, 2019-04-16

Everyone has noticed this: there's no easy way to get a table's definition via TSQL. You can use sp_helptext for procedures/functions/views, but what do you do when you want the table definition in TSQL?

Whether in the middle of a procedure or anywhere in TSQL code, there's no simple utility for a Table's definition. Yeah, there are band-aids and workarounds, but nothing for a simple inline SQL. Since there is a suite of tools like SMO and others, and you can call THAT from TSQL, no one really goes to the effort to do the grunt work to get a table definition via TSQL.

The thing is sometimes you want that definition in TSQL. Maybe you want to loop through all the tables to stick their DDL in a table, or generate a file, or maybe you want that DDL in the middle of an audit trigger. There are lots of reasons why you might want this.

I put together a stored procedure that returns the CREATE TABLE statement via TSQL; maybe one of the most rewarding chunks of code I ever wrote, because It really made me familiar with the meta data on SQL Server.

This first of my articles will introduce you to my procedure, sp_GETDDL, and tell you what it does, as well as what it does not do. The output of the procedure was based on my personal tastes...you may want to adapt it to your ownrequirements. Subsequent Articles will show other ways to use it like how to use it in an audit of DDL changes.

This version of sp_GetDDL accepts one parameter, a table name. It's purpose was to produce a well formatted, standardized DDL of a table for readability and comparison. I work in a shop with lots of developers, and different styles of table definitions come from every developer. Since I organize many of the final scripts, as long as the developer created their objects, I would use this procedure for the final script.

It's very simple to call, and the schema name is optional.

EXEC sp_GetDDL YourTableName or EXEC sp_GetDDL 'bobsschema.YourTableName'

It returns a single field, a varchar(max) with the tables definition. It's simple enough to use, and hopefully somewhat readable when you look at the procedure itself.

Here's the results of a simple table:

CREATE TABLE [TBSTATE] ( 
[STATETBLKEY]  INT                              NOT NULL,
[STATECODE]    CHAR (2)                         NOT NULL,
[STATENAME]    VARCHAR (50)                     NOT NULL,
[FIPS]         CHAR (3)                             NULL,
CONSTRAINT [PK__TBSTATE__17A421EC] PRIMARY KEY ([STATETBLKEY]),
CONSTRAINT STATECODEUNIQUE UNIQUE  NONCLUSTERED(STATECODE))

I mentioned it was built to my specifications, likes, and dislikes. Here's what the procedure does:

I mentioned it was built to my specifications, likes, and dislikes. Here's what the procedure does:

  • returns [schemaname].[tablename], defaulting to dbo when not specified.
  • table names and column names get upper cased.
  • columns are aligned for readability
  • calculated columns are included correctly.
  • identity, with its seed and increment are part of the column definition
  • default values are in-line with the column they are defaulted to.
  • PK Index and Unique indexes are named, and added to the end of the table script.
  • foreign keys are unnamed and added at the end of the table script.
  • any other indexes on the table are AFTER the DDL script
  • rule creation and assignment to a column are added AFTER the DDL script.
  • all triggers are scripted are added AFTER the DDL script.

Just as important, here is what the procedure does not do:

  • does not bother with ANSI settings,even when a column was created with other settings.
  • does not bother with collation statements, which are also really easy to add.
  • does not bother with which filegroup a table belongs to.
  • does not bother with partitions either.

Let's actually create a pretty complex sample table that has all the features we might look to test: copy this code to test. This table has everything I could think of that might break the scripting of a table:

CREATE RULE range_rule
AS 
@range>= $1000 AND @range <$20000;
GO
CREATE TABLE WHATEVERREF(WHATEVERREFID INT IDENTITY(2,5) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30) )
DROP TABLE WHATEVER 
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(2,5) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30) UNIQUE,
myvarbinary VARBINARY,
mybinary BINARY DEFAULT 42,
myimage IMAGE,
myvarchar VARCHAR,
mymaxvarchar VARCHAR(MAX),
mychar CHAR DEFAULT 'Y',
mynvarchar NVARCHAR,
mynchar NCHAR,
mytext TEXT,
myntext NTEXT,
myuniqueidentifier UNIQUEIDENTIFIER,
myrowversion rowversion,
mybit bit,
mytinyint tinyint,
mysmallint smallint,
myint INT REFERENCES WHATEVERREF(WHATEVERREFID),
mybigint bigint,
mysmallmoney smallmoney,
mymoney money,
mynumeric numeric,
mydecimal DECIMAL NOT NULL DEFAULT 0,
myreal REAL,
myfloat FLOAT,
mysmalldatetime smalldatetime,
mydatetime DATETIME,
myCalculatedColumn AS CASE WHEN mydatetime < GETDATE() THEN 'Valid' ELSE 'EXPIRED' END,
mysql_variant SQL_VARIANT,
myxml XML
)
CREATE INDEX IX_WHATEVER ON WHATEVER(mydatetime,myvarchar)
CREATE INDEX IX_ANOTHERWHATEVER ON WHATEVER(mytinyint,myvarchar)
--now bind a column to a rule
EXEC sp_bindrule 'range_rule', 'WHATEVER.mymoney'
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI' 
ALTER TABLE WHATEVER ADD INSERTDT DATETIME DEFAULT GETDATE()WITH VALUES,
UPDATEDDT DATETIME DEFAULT GETDATE() WITH VALUES
SELECT * FROM WHATEVER
GO
CREATE TRIGGER TR_WHATEVER 
ON WHATEVER
FOR INSERT,UPDATE
AS 
UPDATE WHATEVER
SET UPDATEDDT = GETDATE()
FROM INSERTED
WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID

Here's an example of the output from the stored procedure of the complex table.

Here's an example of the output from the stored procedure of the complex table.

CREATE TABLE [WHATEVER] (
[WHATEVERID]          INT              IDENTITY(3,5)   NOT NULL,
[DESCRIP]             VARCHAR(30)                          NULL,
[MYVARBINARY]         VARBINARY                            NULL,
[MYBINARY]            BINARY                               NULL DEFAULT ((42)),
[MYIMAGE]             IMAGE                                NULL,
[MYVARCHAR]           VARCHAR(1)                           NULL,
[MYMAXVARCHAR]        VARCHAR(MAX)                         NULL,
[MYCHAR]              CHAR(1)                              NULL DEFAULT ('Y'),
[MYNVARCHAR]          NVARCHAR(1)                          NULL,
[MYNCHAR]             NCHAR(1)                             NULL,
[MYTEXT]              TEXT                                 NULL,
[MYNTEXT]             NTEXT                                NULL,
[MYUNIQUEIDENTIFIER]  UNIQUEIDENTIFIER                     NULL,
[MYROWVERSION]        TIMESTAMP                        NOT NULL,
[MYBIT]               BIT                                  NULL,
[MYTINYINT]           TINYINT                              NULL,
[MYSMALLINT]          SMALLINT                             NULL,
[MYINT]               INT                                  NULL,
[MYBIGINT]            BIGINT                               NULL,
[MYSMALLMONEY]        SMALLMONEY                           NULL,
[MYMONEY]             MONEY                                NULL,
[MYNUMERIC]           NUMERIC(18,0)                        NULL,
[MYDECIMAL]           DECIMAL(18,0)                    NOT NULL DEFAULT ((0)),
[MYREAL]              REAL(24)                             NULL,
[MYFLOAT]             FLOAT                                NULL,
[MYSMALLDATETIME]     SMALLDATETIME                        NULL,
[MYDATETIME]          DATETIME                             NULL,
[MYCALCULATEDCOLUMN]  AS (CASE WHEN [MYDATETIME]= $1000 AND @range <$20000;
EXEC sp_binderule range_rule,'[WHATEVER].[UPDATEDDT]'
GO
CREATE TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT,UPDATE
AS
UPDATE WHATEVER
SET UPDATEDDT = GETDATE()
FROM INSERTED
WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID
GO

Hopefully, you can see that a big emphasis was made to make the output readable and well formatted, but the other thing to notice is the complexity of the output...it's all there. Using this procedure to generate your scripts makes your output formatted in a consistent manner.

I'm not saying this is the best way to get a table definition via TSQL. It is simply my way,and the only one I know of that is shared with the community. Also, it works pretty well. If you want to go over the code in detail, we can tackle that in the forum posting related to this article.

There are lots of different ways to do this or improve upon this idea. This procedure originated in SQL 2000, which is why it uses cursors, and will give truncated information in SQL 2000 on big tables because of the varchar(8000) maximum.

The only thing that I ask, is that if you adapt my procedure or make it better, to simply send me a copy of it at scripts *at* stormrage.com, so I can learn from the things you've enhanced.The feedback you give will be what makes it worthwhile to me, and will be fed back to the SQL community. Heck I'm working on an improved version now. There are sections that cursors can be replaced with tally tables, extra calls that can be dropped by simply joining syscolumns to syscomments, and so much more.

If you place this procedure in master, you can call it from any database. if you use the function version, the function must be created in each database you use.

Exporting your Entire Schema via T-SQL

OK, let's put this puppy to work and export our entire schema of any database. Exporting one table at a time isn't going to cut it. Let's 'Get er done' and export an entire database schema: first, we need to get all our database objects into a table, so we can loop through and export them. We'll base everything on the built in stored proc sp_msdependencies; this procedure gets all your objects in dependency order...you know..parent tables before child tables that reference the parent,

There was just a tiny bit of trial and error when using this procedure though. Synonyms in a database show up as a function, but you cannot sp_helptext a synonym, so they should not be exported. Also, there are synonyms in the master database, as well as the current database. All of that had to be taken into account. Here's the procedure:

CREATE PROCEDURE sp_export_all(@WithData int = 0)
AS
BEGIN
  SET NOCOUNT ON
  CREATE TABLE #MyObjectHierarchy 
   (
    HID int identity(1,1) not null primary key,
    ObjectId int,
    TYPE int,OBJECTTYPE AS CASE 
                             WHEN TYPE =  1 THEN 'FUNCTION' 
                             WHEN TYPE =  4 THEN 'VIEW' 
                             WHEN TYPE =  8 THEN 'TABLE' 
                             WHEN TYPE = 16 THEN 'PROCEDURE'
                             WHEN TYPE =128 THEN 'RULE'
                             ELSE ''
                           END,
   ONAME varchar(255), 
   OOWNER varchar(255), 
   SEQ int
   )
  --our results table
  CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
  --our list of objects in dependency order
  INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
    EXEC sp_msdependencies @intrans = 1 
 Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
  --synonyms are object type 1 Function?!?!...gotta remove them
  DELETE FROM #MyObjectHierarchy WHERE objectid in(
    SELECT [object_id] FROM sys.synonyms UNION ALL
    SELECT [object_id] FROM master.sys.synonyms)
  DECLARE
    @schemaname     varchar(255),
    @objname        varchar(255),
    @objecttype     varchar(20),
    @FullObjectName varchar(510)
  DECLARE cur1 CURSOR FOR 
    SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
  OPEN cur1
  FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
  WHILE @@fetch_status <> -1
        BEGIN
       SET @FullObjectName = @schemaname + '.' + @objname
       IF @objecttype = 'TABLE'
         BEGIN
           INSERT INTO #Results(ResultsText)
                    EXEC sp_getddl @FullObjectName
                   IF @WithData > 0 
             INSERT INTO #Results(ResultsText)
               EXEC sp_export_data @table_name = @FullObjectName,@ommit_images = 1
          END
        ELSE IF @objecttype IN('VIEW','FUNCTION','PROCEDURE')--it's a FUNCTION/PROC/VIEW
          BEGIN
            --CREATE PROC/FUN/VIEW object needs a GO statement
            INSERT INTO #Results(ResultsText)
              SELECT 'GO'
            INSERT INTO #Results(ResultsText)
              EXEC sp_helptext @FullObjectName
          END
           FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
         END
    CLOSE cur1
    DEALLOCATE cur1
  SELECT ResultsText FROM #Results ORDER BY ResultsID
END
GO

The proc is not all exciting, but look what it does. It's looping through all the objects in the database; if it's a table, it calls sp_GetDDL, if it's a procedure/function/view, it calls sp_helptext. It's putting everything into a temp table, and at the end, it selects from that temp table.

The proc is not all exciting, but look what it does. It's looping through all the objects in the database; if it's a table, it calls sp_GetDDL, if it's a procedure/function/view, it calls sp_helptext. It's putting everything into a temp table, and at the end, it selects from that temp table.

There is one optional parameter,

@WithData INT = 0

If this is non zero, then after it builds a table, it will create INSERT INTO tablename statements! This is both good and bad; if you have a table with a million billion rows, and you try to generate those statements...well...it's gonna take more time than it is worth.

But if you have an empty database, that just has lookup tables, like statuses, counties, states, the basic lookups, you've got a ready made deployment script, and it looks GOOD!.

See the function called sp_export_data? It is nothing more than a slightly modified version of Narayana Vyas Kondreddi's generate_inserts_2005.txt from http://vyaskn.tripod.com I changed some PRINT statements to select statements so the SET IDENTITY_INSERT commands were part of the output, and made a few fields that were varchar(8000) varchar(max) due to some errors I got on some really wide tables. I renamed it to reflect the changes, and make logical sense with the rest of this feature set.

it's up to you to read the code and test the results. Give me feedback, both good and bad, I'll appreciate it!

Here's a link to a zip file with the following scripts: Create Table DDL via T-SQL

  • whatever_sample_tables.sql -- contains the sample above to create a test table or two
  • sp_GetDDL2005.sql -- the 2005 version of sp_GetDDL to get the CREATE TABLE statements.
  • sp_export_all.sql -- creates a table with every schema.object in a given database.
  • sp_export_data_2005.sql --modified version of sp_generate_inserts

Contact me if you want the SQL 2000 versions of the same scripts; just be aware that they truncate results at 8000 characters.

My next article shows how to use this procedure to save the table definition in an Audit function for DDL changes.

Thank you!

Lowell.

Resources

Rate

4.8 (5)

Share

Share

Rate

4.8 (5)

Related content

Service Broker Part 1: Service Broker Basics

This article covers SQL Server 2008 Service Broker, an asynchronous messaging framework that is directly integrated within the relational engine of SQL Server. The series will provides you with the basics about implementing Service Broker applications and how you can transparently scale them out to support any required workload.

5 (1)

2019-04-15

2,451 reads

Auditing DDL Changes in SQL Server databases

Even where Source Control isn't being used by developers, it is still possible to automate the process of tracking the changes being made to a database and put those into Source Control, in order to track what changed and when. You can even get an email alert when it happens. With suitable scripting, you can even do it if you don't have direct access to the live database. Grant shows how easy this is with SQL Compare.

2011-10-20

2,823 reads

SQL Server 2008 and 2008 R2 Integration Services - Analysis Services Execute DDL Task

The term DDL designates Data Definition Language, which implies an action involving creating data structures. In the context of Analysis Services Execute DDL Task, this is a bit of a misnomer because its capabilities are much more diverse, extending to such areas as backups, restores, deletions, modifications, or processing. This is accomplished by employing Analysis Services Scripting Language (ASSL).

2011-09-07

2,257 reads