Get DDL for any SQL 2005 table

,

The purpose of sp_GetDDL is to get the full DDL of any table in TSQL; a function version of the same code also exists.

since the results are in a single varchar(max), it makes it easy to use in the auditing of DDL triggers, or capturing an occasional schema snapshot for disaster recovery scnarios.

Behind the scenes it's smart, readable but somewhat complicated code. usage upfront is easy:

exec sp_GetDDL YourTableName
-- or

exec sp_GetDDL 'schemaname.tablename'

-- or

exec sp_GetDDL [schemaname].[tablename]

 

it produces well formatted CREATE TABLE scripts like this: note that a lot of testing went into the spacing to make everything have a sharp appearance;

The proc has been enhanced to fix minor issues, and recently added any extended properties for the table ror it's columns.

CREATE TABLE [dbo].[TBSTATE] ( 
[STATETBLKEY]  INT                              NOT NULL,
[INDEXTBLKEY]  INT                              NOT NULL,
[STATECODE]    CHAR(2)                          NOT NULL,
[STATENAME]    VARCHAR(50)                      NOT NULL,
[FIPS]         CHAR(3)                              NULL,
CONSTRAINT   [PK__TBSTATE__17A421EC]                   PRIMARY KEY CLUSTERED    (STATETBLKEY),
CONSTRAINT   [STATECODEUNIQUE]                         UNIQUE      NONCLUSTERED (STATECODE))
-- USAGE: exec sp_GetDDL YourTableName
-- or exec sp_GetDDL 'bob.example' 
-- or exec sp_GetDDL '[schemaname].[tablename]' 
--############################################################################# 
-- copyright 2004-2009 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved. 
-- http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005_V304.txt
-- V300 uses String concatination and sys.tables instead of a cursor
-- V301 enhanced 07/31/2009 to include extended properties definitions
-- V302 fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found
-- V303 fixes an issue where all rules are appearing, instead of jsut the rule related to a column
-- V304 testing whether vbCrLf is better than just CHAR(13), some formatting cleanup with GO statements
-- also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]
-- You can use this however you like...this script is not rocket science, but it took a bit of work to create. 
-- 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, 
-- 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. 
-- add this to your toolbox of helpful scripts. 
--############################################################################# 
--if you are going to put this in MASTER, and want it to be able to query 
--each database's sys.indexes, you MUST mark it as a system procedure: 
--EXECUTE sp_ms_marksystemobject 'sp_GetDDL' 
--############################################################################# 
CREATE PROCEDURE [dbo].[sp_GetDDL] 
 @TBL VARCHAR(255) 
AS 
BEGIN 
 SET NOCOUNT ON 
 DECLARE @TBLNAME VARCHAR(200), 
 @SCHEMANAME VARCHAR(255), 
 @STRINGLEN INT, 
 @TABLE_ID INT, 
 @FINALSQL VARCHAR(max), 
 @CONSTRAINTSQLS VARCHAR(max), 
 @CHECKCONSTSQLS VARCHAR(max), 
 @RULESCONSTSQLS VARCHAR(max), 
 @FKSQLS VARCHAR(max), 
 @TRIGGERSTATEMENT VARCHAR(max),
 @EXTENDEDPROPERTIES VARCHAR(max), 
 @INDEXSQLS VARCHAR(max),
 @vbCrLf CHAR(2) 
--############################################################################## 
-- INITIALIZE 
--############################################################################## 
 --SET @TBL = '[DBO].[WHATEVER1]' 
 --does the tablename contain a schema? 
 SET @vbCrLf = CHAR(13) + CHAR(10) 
 SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') , 
 @TBLNAME = PARSENAME(@TBL,1) 
 SELECT 
 @TABLE_ID = [object_id] 
 FROM sys.objects 
 WHERE [type] = 'U' 
 AND [name] <> 'dtproperties' 
 AND [name] = @TBLNAME 
 AND [schema_id] = schema_id(@SCHEMANAME) ; 
 
--############################################################################## 
-- Check If TableName is Valid 
--############################################################################## 
 IF ISNULL(@TABLE_ID,0) = 0 
 BEGIN 
 SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database [' + db_name() + ']' 
 SELECT @FINALSQL; 
 RETURN 0 
 END 
--############################################################################## 
-- Valid Table, Continue Processing 
--############################################################################## 
 SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] ( ' 
 SELECT @TABLE_ID = OBJECT_ID(@TBLNAME) 
 SELECT 
 @STRINGLEN = MAX(LEN(sys.columns.[name])) + 1 
 FROM sys.objects 
 INNER JOIN sys.columns 
 ON sys.objects.[object_id] = sys.columns.[object_id] 
 AND sys.objects.[object_id] = @TABLE_ID; 
--############################################################################## 
--Get the columns, their definitions and defaults. 
--############################################################################## 
 SELECT 
 @FINALSQL = @FINALSQL 
 + CASE 
 WHEN sys.columns.[is_computed] = 1 
 THEN @vbCrLf 
 + '[' 
 + UPPER(sys.columns.[name]) 
 + '] ' 
 + SPACE(@STRINGLEN - LEN(sys.columns.[name])) 
 + 'AS ' + UPPER(sys.columns.[name]) 
 ELSE @vbCrLf 
 + '[' 
 + UPPER(sys.columns.[name]) 
 + '] ' 
 + SPACE(@STRINGLEN - LEN(sys.columns.[name])) 
 + UPPER(TYPE_NAME(sys.columns.[system_type_id])) 
 + CASE 
--IE NUMERIC(10,2) 
 WHEN TYPE_NAME(sys.columns.[system_type_id]) IN ('decimal','numeric') 
 THEN '(' 
 + CONVERT(VARCHAR,sys.columns.[precision]) 
 + ',' 
 + CONVERT(VARCHAR,sys.columns.[scale]) 
 + ') ' 
 + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[precision]) 
 + ',' 
 + CONVERT(VARCHAR,sys.columns.[scale]))) 
 + SPACE(7) 
 + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
--IE FLOAT(53) 
 WHEN TYPE_NAME(sys.columns.[system_type_id]) IN ('float','real') 
 THEN 
 --addition: if 53, no need to specifically say (53), otherwise display it 
 CASE 
 WHEN sys.columns.[precision] = 53 
 THEN SPACE(11 - LEN(CONVERT(VARCHAR,sys.columns.[precision]))) 
 + SPACE(7) 
 + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 ELSE '(' 
 + CONVERT(VARCHAR,sys.columns.[precision]) 
 + ') ' 
 + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[precision]))) 
 + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 END 
--ie VARCHAR(40) 
 WHEN TYPE_NAME(sys.columns.[system_type_id]) IN ('char','varchar') 
 THEN CASE 
 WHEN sys.columns.[max_length] = -1 
 THEN '(max)' 
 + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[max_length]))) 
 + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 ELSE '(' 
 + CONVERT(VARCHAR,sys.columns.[max_length]) 
 + ') ' 
 + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[max_length]))) 
 + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 END 
--ie NVARCHAR(40) 
 WHEN TYPE_NAME(sys.columns.[system_type_id]) IN ('nchar','nvarchar') 
 THEN CASE 
 WHEN sys.columns.[max_length] = -1 
 THEN '(max)' 
 + SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]/2)))) 
 + SPACE(7) 
 + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 ELSE '(' 
 + CONVERT(VARCHAR,(sys.columns.[max_length]/2)) 
 + ') ' 
 + SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]/2)))) 
 + SPACE(7) 
 + SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 END 
--ie datetime 
 WHEN TYPE_NAME(sys.columns.[system_type_id]) IN ('datetime','money','text','image') 
 THEN SPACE(18 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + ' ' 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
--IE INT 
 ELSE SPACE(16 - LEN(TYPE_NAME(sys.columns.[system_type_id]))) 
 + CASE 
 WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0 
 THEN ' ' 
 ELSE ' IDENTITY(' 
 + CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) ) 
 + ',' 
 + CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) ) 
 + ')' 
 END 
 + SPACE(2) 
 + CASE 
 WHEN sys.columns.[is_nullable] = 0 
 THEN ' NOT NULL' 
 ELSE ' NULL' 
 END 
 END 
 + CASE 
 WHEN sys.columns.[default_object_id] = 0 
 THEN '' 
 ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'') 
 --optional section in case NAMED default cosntraints are needed: 
 --ELSE @vbCrLf + 'CONSTRAINT [' + def.name + '] DEFAULT ' + ISNULL(def.[definition] ,'') 
 END --CASE cdefault 
--############################################################################## 
-- COLLATE STATEMENTS 
-- personally i do not like collation statements, 
-- but included here to make it easy on those who do 
--############################################################################## 
/* 
 + CASE 
 WHEN collation IS NULL 
 THEN '' 
 ELSE ' COLLATE ' + sys.columns.collation 
 END 
*/ 
 END --iscomputed 
 + ',' 
 FROM sys.columns 
 LEFT OUTER JOIN sys.default_constraints DEF 
 on sys.columns.[default_object_id] = DEF.[object_id] 
 Where sys.columns.[object_id]=@TABLE_ID 
 ORDER BY sys.columns.[column_id] 
--############################################################################## 
--used for formatting the rest of the constraints: 
--############################################################################## 
 SELECT 
 @STRINGLEN = MAX(LEN([name])) + 1 
 FROM sys.objects 
--############################################################################## 
--PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax 
--############################################################################## 
DECLARE @Results TABLE ( 
 [schema_id] int, 
 [schema_name] varchar(255), 
 [object_id] int, 
 [object_name] varchar(255), 
 [index_id] int, 
 [index_name] varchar(255), 
 [Rows] int, 
 [SizeMB] decimal(19,3), 
 [IndexDepth] int, 
 [type] int, 
 [type_desc] varchar(30), 
 [fill_factor] int, 
 [is_unique] int, 
 [is_primary_key] int , 
 [is_unique_constraint] int, 
 [index_columns_key] varchar(max), 
 [index_columns_include] varchar(max)) 
INSERT INTO @Results 
SELECT 
sys.schemas.schema_id, sys.schemas.[name] AS schema_name, 
sys.objects.[object_id], sys.objects.[name] AS object_name, 
sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name, 
partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth, 
sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor, 
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint, 
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key, 
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include 
FROM 
sys.objects 
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id 
JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id] 
JOIN ( 
 SELECT 
 [object_id], index_id, SUM(row_count) AS Rows, 
 CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB 
 FROM sys.dm_db_partition_stats 
 GROUP BY [object_id], index_id 
) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id 
CROSS APPLY ( 
 SELECT 
 LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key, 
 LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include 
 FROM 
 ( 
 SELECT 
 ( 
 SELECT sys.columns.[name] + ',' + ' ' 
 FROM 
 sys.index_columns 
 JOIN sys.columns ON 
 sys.index_columns.column_id=sys.columns.column_id 
 AND sys.index_columns.[object_id]=sys.columns.[object_id] 
 WHERE 
 sys.index_columns.is_included_column=0 
 AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id 
 ORDER BY key_ordinal 
 FOR XML PATH('') 
 ) AS index_columns_key, 
 ( 
 SELECT sys.columns.[name] + ',' + ' ' 
 FROM 
 sys.index_columns 
 JOIN sys.columns ON 
 sys.index_columns.column_id=sys.columns.column_id 
 AND sys.index_columns.[object_id]=sys.columns.[object_id] 
 WHERE 
 sys.index_columns.is_included_column=1 
 AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id 
 ORDER BY index_column_id 
 FOR XML PATH('') 
 ) AS index_columns_include 
 ) AS Index_Columns 
) AS Index_Columns 
WHERE 
sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME='' THEN sys.schemas.[name] ELSE @SCHEMANAME END 
AND sys.objects.[name] LIKE CASE WHEN @TBLNAME='' THEN sys.objects.[name] ELSE @TBLNAME END 
ORDER BY sys.schemas.[name], sys.objects.[name], sys.indexes.[name] 
--@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results: 
SET @CONSTRAINTSQLS = '' 
SET @INDEXSQLS = '' 
 
--############################################################################## 
--constriants 
--############################################################################## 
SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS + 
CASE 
 WHEN is_primary_key = 1 or is_unique = 1 
 THEN @vbCrLf 
 + 'CONSTRAINT [' + index_name + '] ' 
 + SPACE(@STRINGLEN - LEN(index_name)) 
 + CASE WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END END 
 + type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE ' ' END 
 + ' (' + index_columns_key + ')' 
 + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END 
 + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END 
 ELSE '' 
END + ',' 
from @RESULTS 
where [type_desc] != 'HEAP' 
 AND is_primary_key = 1 or is_unique = 1 
order by is_primary_key desc,is_unique desc 
--############################################################################## 
--indexes 
--############################################################################## 
SELECT @INDEXSQLS = @INDEXSQLS + 
CASE 
 WHEN is_primary_key = 0 or is_unique = 0 
 THEN @vbCrLf 
 + 'CREATE INDEX [' + index_name + '] ' 
 + SPACE(@STRINGLEN - LEN(index_name)) 
 + ' ON [' + [object_name] + ']' 
 + ' (' + index_columns_key + ')' 
 + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END 
 + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END 
 
END 
from @RESULTS 
where [type_desc] != 'HEAP' 
 AND is_primary_key = 0 AND is_unique = 0 
order by is_primary_key desc,is_unique desc 
IF @INDEXSQLS <> ''
 SET @INDEXSQLS = @vbCrLf + 'GO' + @vbCrLf + @INDEXSQLS 
--############################################################################## 
--CHECK Constraints 
--############################################################################## 
 SET @CHECKCONSTSQLS = '' 
 SELECT 
 @CHECKCONSTSQLS = @CHECKCONSTSQLS 
 + @vbCrLf 
 + ISNULL('CONSTRAINT [' + sys.objects.[name] + '] ' 
 + SPACE(@STRINGLEN - LEN(sys.objects.[name])) 
 + ' CHECK ' + ISNULL(sys.check_constraints.definition,'') 
 + ',','') 
 FROM sys.objects 
 INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id] 
 WHERE sys.objects.type = 'C' 
 AND sys.objects.parent_object_id = @TABLE_ID 
--############################################################################## 
--FOREIGN KEYS 
--############################################################################## 
 SET @FKSQLS = '' ; 
 SELECT 
 @FKSQLS=@FKSQLS 
 + @vbCrLf 
 + 'CONSTRAINT [' + OBJECT_NAME(constid) +']' 
 + SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) )) 
 + ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey) 
 + ') REFERENCES ' + OBJECT_NAME(rkeyid) 
 +'(' + COL_NAME(rkeyid,rkey) + '),' 
 from sysforeignkeys 
 WHERE fkeyid = @TABLE_ID 
--############################################################################## 
--RULES 
--############################################################################## 
SET @RULESCONSTSQLS = '' 
SELECT 
 @RULESCONSTSQLS = @RULESCONSTSQLS 
 + ISNULL( 
 @vbCrLf 
 + 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(varchar(30),sys.objects.schema_id) + ' AND [name] = ''[' + object_name(sys.columns.[rule_object_id]) + ']'')' + @vbCrLf 
 + sys.sql_modules.definition + @vbCrLf + 'GO' + @vbCrLf 
 + 'EXEC sp_binderule [' + sys.objects.[name] + '], ''[' + OBJECT_NAME(sys.columns.[object_id]) + '].[' + sys.columns.[name] + ']''' + @vbCrLf + 'GO' ,'') 
from sys.columns 
 inner join sys.objects 
 on sys.objects.[object_id] = sys.columns.[object_id] 
 inner join sys.sql_modules 
 on sys.columns.[rule_object_id] = sys.sql_modules.[object_id] 
WHERE sys.columns.[rule_object_id] <> 0 
 and sys.columns.[object_id] = @TABLE_ID 
--############################################################################## 
--TRIGGERS 
--############################################################################## 
SET @TRIGGERSTATEMENT = '' 
SELECT 
 @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + sys.sql_modules.[definition] + @vbCrLf + 'GO' 
FROM sys.sql_modules 
WHERE [object_id] IN(SELECT 
 [object_id] 
 FROM sys.objects 
 WHERE type = 'TR' 
 AND [parent_object_id] = @TABLE_ID) 
IF @TRIGGERSTATEMENT <> ''
 SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT 
--############################################################################## 
--NEW SECTION QUERY ALL EXTENDED PROPERTIES 
--############################################################################## 
SET @EXTENDEDPROPERTIES = ''
SELECT @EXTENDEDPROPERTIES = 
 @EXTENDEDPROPERTIES + @vbCrLf +
 'EXEC sys.sp_addextendedproperty 
 @name = N''' + [name] + ''', @value = N''' + REPLACE(convert(varchar(max),[value]),'''','''''') + ''', 
 @level0type = N''SCHEMA'', @level0name = [' + @SCHEMANAME + '], 
 @level1type = N''TABLE'', @level1name = [' + @TBLNAME + '];'
--SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, NULL, NULL); 
IF @EXTENDEDPROPERTIES <> '' 
 SET @EXTENDEDPROPERTIES = @vbCrLf + 'GO' + @vbCrLf + @EXTENDEDPROPERTIES
--############################################################################## 
--FINAL CLEANUP AND PRESENTATION 
--############################################################################## 
--at this point, there is a trailing comma, or it blank 
 SELECT 
 @FINALSQL = @FINALSQL 
 + @CONSTRAINTSQLS 
 + @CHECKCONSTSQLS 
 + @FKSQLS 
--note that this trims the trailing comma from the end of the statements 
 SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ; 
 SET @FINALSQL = @FINALSQL + ')' + @vbCrLf ; 
 
SELECT @FINALSQL 
 + @INDEXSQLS 
 + @RULESCONSTSQLS 
 + @TRIGGERSTATEMENT 
 + @EXTENDEDPROPERTIES
 
END

Rate

5 (2)

Share

Share

Rate

5 (2)