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 = 0If 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.
