I made the following changes to v311a:
1. Removed the UPPER() function from the table and column names because many people use case to separate words and there is no reason to force them all to uppercase. If a developer used uppercase it will be preserved, so this solution should work for everyone. If you have a reason to add it back, then please make it an option.
2. Added a "New Name" parameter in case you want to script the creation of backup/history tables in addition to the original table. This also allows you to define the same name in a different database/server, in case you want to make a copy of a table in an archive location. The parameter is optional, so you can leave it off (and use the original name) or include it (either in-line or as a named parameter)...
--Default will use the original name:
EXEC sp_GetDDL 'dbo.Customers'
--Adding an in-line parameter:
EXEC sp_GetDDL 'dbo.Customers', 'CustomersBackup'
--Adding a named parameter:
EXEC sp_GetDDL 'dbo.Customers', @NewName='CustomersBackup'
3. Added 10 optional parameters which let you turn off different parts of the code. This is important because if you are making a backup table you might not want the constraints, triggers or indexes. Note, I left the "collation" option turned off by default since you noted that you don't like it.
ALTER PROCEDURE [dbo].[sp_GetDDL]
@TBL VARCHAR(255),
@NewName VARCHAR(255) = NULL,
@IncludeIdentity BIT = 1,
@IncludeUniqueKeys BIT = 1,
@IncludeForeignKeys BIT = 1,
@IncludeIndexes BIT = 1,
@IncludeCheckConstraints BIT = 1,
@IncludeRules BIT = 1,
@IncludeTriggers BIT = 1,
@IncludeExtendedProperties BIT = 1,
@IncludeDefaults BIT = 1,
@IncludeCollation BIT = 0
Fyi, the optional parameters can be added individually if you just want to turn off one thing...
EXEC sp_GetDDL 'dbo.Customers', @IncludeTriggers=0
I did not fix it, but I wanted to mention that the horizontal spacing is not working properly. It creates a statement that is jagged (which I assume is the reason for it), and makes it take up more room on the screen so that it is easier to miss things which have been pushed to the far right. Personally I am not a fan of any unnecessary horizontal spacing (a single space is all I use) but if you want to include it you might want to get it working properly.
Here is the complete modified code:
ALTER PROCEDURE [dbo].[sp_GetDDL]
@TBL VARCHAR(255),
@NewName VARCHAR(255) = NULL,
@IncludeIdentity BIT = 1,
@IncludeUniqueKeys BIT = 1,
@IncludeForeignKeys BIT = 1,
@IncludeIndexes BIT = 1,
@IncludeCheckConstraints BIT = 1,
@IncludeRules BIT = 1,
@IncludeTriggers BIT = 1,
@IncludeExtendedProperties BIT = 1,
@IncludeDefaults BIT = 1,
@IncludeCollation BIT = 0
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),
@ISSYSTEMOBJECT INT,
@PROCNAME VARCHAR(256),
@input VARCHAR(max)
--##############################################################################
-- INITIALIZE
--##############################################################################
SET @input = ''
--new code: determine whether this proc is marked as a system proc with sp_ms_marksystemobject,
--which flips the is_ms_shipped bit in sys.objects
SELECT @ISSYSTEMOBJECT = ISNULL(is_ms_shipped,0),@PROCNAME = ISNULL(name,'pr_GetDDL') FROM sys.objects WHERE object_id = @@PROCID
IF @ISSYSTEMOBJECT IS NULL
SELECT @ISSYSTEMOBJECT = ISNULL(is_ms_shipped,0),@PROCNAME = ISNULL(name,'pp_GetDDL') FROM master.sys.objects WHERE object_id = @@PROCID
IF @ISSYSTEMOBJECT IS NULL
SET @ISSYSTEMOBJECT = 0
IF @PROCNAME IS NULL
SET @PROCNAME = 'sp_GetDDL'
--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 OBJS
WHERE [type] IN ('S','U')
AND [name] <> 'dtproperties'
AND [name] = @TBLNAME
AND [schema_id] = schema_id(@SCHEMANAME) ;
--##############################################################################
-- Check If TEMP TableName is Valid
--##############################################################################
IF LEFT(@TBLNAME,1) = '#'
BEGIN
PRINT '--TEMP TABLE [' + @TBLNAME + '] FOUND'
IF OBJECT_ID('tempdb..' + @TBLNAME) IS NOT NULL
BEGIN
PRINT '--GOIN TO TEMP PROCESSING'
GOTO TEMPPROCESS
END
END
ELSE
BEGIN
PRINT '--Non-Temp Table, [' + @TBLNAME + '] continue Processing'
END
--##############################################################################
-- Check If TableName is Valid
--##############################################################################
IF ISNULL(@TABLE_ID,0) = 0
BEGIN
--V309 code: see if it is an object and not a table.
SELECT
@TABLE_ID = [object_id]
FROM sys.objects OBJS
--WHERE [type_desc] IN('SQL_STORED_PROCEDURE','VIEW','SQL_TRIGGER','AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
WHERE [type] IN ('P','V','TR','AF','IF','FN','TF')
AND [name] <> 'dtproperties'
AND [name] = @TBLNAME
AND [schema_id] = schema_id(@SCHEMANAME) ;
IF ISNULL(@TABLE_ID,0) <> 0
BEGIN
SELECT
@FINALSQL = def.definition
FROM sys.objects OBJS
INNER JOIN sys.sql_modules def
ON OBJS.object_id = def.object_id
WHERE OBJS.[type] IN ('P','V','TR','AF','IF','FN','TF')
AND OBJS.[name] <> 'dtproperties'
AND OBJS.[name] = @TBLNAME
AND OBJS.[schema_id] = schema_id(@SCHEMANAME) ;
SET @input = @FINALSQL
SELECT @input;
RETURN 0
END
ELSE
BEGIN
SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + @TBLNAME + '] does not exist in Database [' + db_name() + '] '
+ CASE
WHEN @ISSYSTEMOBJECT = 0 THEN @vbCrLf + ' (also note that ' + @PROCNAME + ' is not marked as a system proc and cross db access to sys.tables will fail.)'
ELSE ''
END
IF LEFT(@TBLNAME,1) = '#'
SET @FINALSQL = @FINALSQL + ' OR in The tempdb database.'
SELECT @FINALSQL AS Item;
RETURN 0
END
END
--##############################################################################
-- Valid Table, Continue Processing
--##############################################################################
SELECT @FINALSQL = 'CREATE TABLE ' + COALESCE(@NewName, '[' + @SCHEMANAME + '].[' + @TBLNAME + ']') + ' ( '
--removed invalid code here which potentially selected wrong table--thanks David Grifiths @SSC!
SELECT
@STRINGLEN = MAX(LEN(COLS.[name])) + 1
FROM sys.objects OBJS
INNER JOIN sys.columns COLS
ON OBJS.[object_id] = COLS.[object_id]
AND OBJS.[object_id] = @TABLE_ID;
--##############################################################################
--Get the columns, their definitions and defaults.
--##############################################################################
SELECT
@FINALSQL = @FINALSQL
+ CASE
WHEN COLS.[is_computed] = 1
THEN @vbCrLf
+ '['
+ COLS.[name]
+ '] '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ 'AS ' + ISNULL(CALC.definition,'')
+ CASE
WHEN CALC.is_persisted = 1
THEN ' PERSISTED'
ELSE ''
END
ELSE @vbCrLf
+ '['
+ COLS.[name]
+ '] '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ UPPER(TYPE_NAME(COLS.[user_type_id]))
+ CASE
--IE NUMERIC(10,2)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('decimal','numeric')
THEN '('
+ CONVERT(VARCHAR,COLS.[precision])
+ ','
+ CONVERT(VARCHAR,COLS.[scale])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[precision])
+ ','
+ CONVERT(VARCHAR,COLS.[scale])))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeIdentity = 1 THEN CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 0
THEN ''
ELSE ' IDENTITY('
+ CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE FLOAT(53)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('float','real')
THEN
--addition: if 53, no need to specifically say (53), otherwise display it
CASE
WHEN COLS.[precision] = 53
THEN SPACE(11 - LEN(CONVERT(VARCHAR,COLS.[precision])))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,COLS.[precision])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[precision])))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie VARCHAR(40)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('char','varchar')
THEN CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[max_length])))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,COLS.[max_length])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[max_length])))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie NVARCHAR(40)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('nchar','nvarchar')
THEN CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,(COLS.[max_length] / 2))
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie datetime
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime','money','text','image')
THEN SPACE(18 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ ' '
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE VARBINARY(500)
WHEN TYPE_NAME(COLS.[user_type_id]) = 'varbinary'
THEN
CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length]))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,(COLS.[max_length]))
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length]))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--IE INT
ELSE SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeIdentity = 1 THEN CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
ELSE '' END
+ SPACE(2)
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
+ CASE WHEN @IncludeDefaults = 1 THEN CASE
WHEN COLS.[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] ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
ELSE '' END
END --iscomputed
+ ','
FROM sys.columns COLS
LEFT OUTER JOIN sys.default_constraints DEF
ON COLS.[default_object_id] = DEF.[object_id]
LEFT OUTER JOIN sys.computed_columns CALC
ON COLS.[object_id] = CALC.[object_id]
AND COLS.[column_id] = CALC.[column_id]
WHERE COLS.[object_id]=@TABLE_ID
ORDER BY COLS.[column_id]
--##############################################################################
--used for formatting the rest of the constraints:
--##############################################################################
SELECT
@STRINGLEN = MAX(LEN([name])) + 1
FROM sys.objects OBJS
--##############################################################################
--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
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.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 OBJS
INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER 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 STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.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 COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE SCH.[name] LIKE CASE
WHEN @SCHEMANAME = ''
THEN SCH.[name]
ELSE @SCHEMANAME
END
AND OBJS.[name] LIKE CASE
WHEN @TBLNAME = ''
THEN OBJS.[name]
ELSE @TBLNAME
END
ORDER BY
SCH.[name],
OBJS.[name],
IDX.[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
--##############################################################################
IF @IncludeUniqueKeys = 1 BEGIN
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
END --@IncludeUniqueKeys
--##############################################################################
--indexes
--##############################################################################
IF @IncludeIndexes = 1 BEGIN
SELECT @INDEXSQLS = @INDEXSQLS
+ CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE ' + type_desc + ' 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
END --@IncludeIndexes
--##############################################################################
--CHECK Constraints
--##############################################################################
SET @CHECKCONSTSQLS = ''
IF @IncludeCheckConstraints = 1 BEGIN
SELECT
@CHECKCONSTSQLS = @CHECKCONSTSQLS
+ @vbCrLf
+ ISNULL('CONSTRAINT [' + OBJS.[name] + '] '
+ SPACE(@STRINGLEN - LEN(OBJS.[name]))
+ ' CHECK ' + ISNULL(CHECKS.definition,'')
+ ',','')
FROM sys.objects OBJS
INNER JOIN sys.check_constraints CHECKS ON OBJS.[object_id] = CHECKS.[object_id]
WHERE OBJS.type = 'C'
AND OBJS.parent_object_id = @TABLE_ID
END --@IncludeCheckConstraints
--##############################################################################
--FOREIGN KEYS
--##############################################################################
SET @FKSQLS = '' ;
IF @IncludeForeignKeys = 1 BEGIN
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 FKEYS
WHERE fkeyid = @TABLE_ID
END --@IncludeForeignKeys
--##############################################################################
--RULES
--##############################################################################
SET @RULESCONSTSQLS = ''
IF @IncludeRules = 1 BEGIN
SELECT
@RULESCONSTSQLS = @RULESCONSTSQLS
+ ISNULL(
@vbCrLf
+ 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(varchar(30),OBJS.schema_id) + ' AND [name] = ''[' + object_name(COLS.[rule_object_id]) + ']'')' + @vbCrLf
+ MODS.definition + @vbCrLf + 'GO' + @vbCrLf
+ 'EXEC sp_binderule [' + OBJS.[name] + '], ''[' + OBJECT_NAME(COLS.[object_id]) + '].[' + COLS.[name] + ']''' + @vbCrLf + 'GO' ,'')
FROM sys.columns COLS
INNER JOIN sys.objects OBJS
ON OBJS.[object_id] = COLS.[object_id]
INNER JOIN sys.sql_modules MODS
ON COLS.[rule_object_id] = MODS.[object_id]
WHERE COLS.[rule_object_id] <> 0
AND COLS.[object_id] = @TABLE_ID
END --@IncludeRules
--##############################################################################
--TRIGGERS
--##############################################################################
SET @TRIGGERSTATEMENT = ''
IF @IncludeTriggers = 1 BEGIN
SELECT
@TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + MODS.[definition] + @vbCrLf + 'GO'
FROM sys.sql_modules MODS
WHERE [object_id] IN(SELECT
[object_id]
FROM sys.objects OBJS
WHERE type = 'TR'
AND [parent_object_id] = @TABLE_ID)
IF @TRIGGERSTATEMENT <> ''
SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT
END --@IncludeTriggers
--##############################################################################
--NEW SECTION QUERY ALL EXTENDED PROPERTIES
--##############################################################################
SET @EXTENDEDPROPERTIES = ''
IF @IncludeExtendedProperties = 1 BEGIN
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
END --@IncludeExtendedProperties
--##############################################################################
--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 ;
SET @input = @vbCrLf
+ @FINALSQL
+ @INDEXSQLS
+ @RULESCONSTSQLS
+ @TRIGGERSTATEMENT
+ @EXTENDEDPROPERTIES
SELECT @input
RETURN;
--##############################################################################
-- END Normal Table Processing
--##############################################################################
--simple, primitive version to get the results of a TEMP table from the TEMP db.
--##############################################################################
-- NEW Temp Table Logic
--##############################################################################
TEMPPROCESS:
SELECT @TABLE_ID = OBJECT_ID('tempdb..' + @TBLNAME)
--##############################################################################
-- Valid Table, Continue Processing
--##############################################################################
SELECT @FINALSQL = 'CREATE TABLE ' + COALESCE(@NewName, '[' + @SCHEMANAME + '].[' + @TBLNAME + ']') + ' ( '
--removed invalud cide here which potentially selected wrong table--thansk David Grifiths @SSC!
SELECT
@STRINGLEN = MAX(LEN(COLS.[name])) + 1
FROM tempdb.sys.objects OBJS
INNER JOIN tempdb.sys.columns COLS
ON OBJS.[object_id] = COLS.[object_id]
AND OBJS.[object_id] = @TABLE_ID;
--##############################################################################
--Get the columns, their definitions and defaults.
--##############################################################################
SELECT
@FINALSQL = @FINALSQL
+ CASE
WHEN COLS.[is_computed] = 1
THEN @vbCrLf
+ '['
+ COLS.[name]
+ '] '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ 'AS ' + ISNULL(CALC.definition,'')
+ CASE
WHEN CALC.is_persisted = 1
THEN ' PERSISTED'
ELSE ''
END
ELSE @vbCrLf
+ '['
+ COLS.[name]
+ '] '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ UPPER(TYPE_NAME(COLS.[user_type_id]))
+ CASE
--IE NUMERIC(10,2)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('decimal','numeric')
THEN '('
+ CONVERT(VARCHAR,COLS.[precision])
+ ','
+ CONVERT(VARCHAR,COLS.[scale])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[precision])
+ ','
+ CONVERT(VARCHAR,COLS.[scale])))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeIdentity = 1 THEN CASE
WHEN COLS.is_identity = 1
THEN ' IDENTITY(1,1)'
ELSE ''
----WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 1
----THEN ' IDENTITY('
---- + CONVERT(VARCHAR,ISNULL(IDENT_SEED('tempdb..' + @TBLNAME),1) )
---- + ','
---- + CONVERT(VARCHAR,ISNULL(IDENT_INCR('tempdb..' + @TBLNAME),1) )
---- + ')'
----ELSE ''
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE FLOAT(53)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('float','real')
THEN
--addition: if 53, no need to specifically say (53), otherwise display it
CASE
WHEN COLS.[precision] = 53
THEN SPACE(11 - LEN(CONVERT(VARCHAR,COLS.[precision])))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,COLS.[precision])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[precision])))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie VARCHAR(40)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('char','varchar')
THEN CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[max_length])))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,COLS.[max_length])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,COLS.[max_length])))
+ SPACE(7) + SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie NVARCHAR(40)
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('nchar','nvarchar')
THEN CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,(COLS.[max_length] / 2))
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeCollation = 1 THEN CASE
WHEN COLS.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + COLS.collation_name
END
ELSE '' END
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie datetime
WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime','money','text','image')
THEN SPACE(18 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ ' '
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE VARBINARY(500)
WHEN TYPE_NAME(COLS.[user_type_id]) = 'varbinary'
THEN
CASE
WHEN COLS.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length]))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,(COLS.[max_length]))
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length]))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--IE INT
ELSE SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id])))
+ CASE WHEN @IncludeIdentity = 1 THEN CASE
WHEN COLS.is_identity = 1
THEN ' IDENTITY(1,1)'
ELSE ' '
----WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 1
----THEN ' IDENTITY('
---- + CONVERT(VARCHAR,ISNULL(IDENT_SEED('tempdb..' + @TBLNAME),1) )
---- + ','
---- + CONVERT(VARCHAR,ISNULL(IDENT_INCR('tempdb..' + @TBLNAME),1) )
---- + ')'
----ELSE ' '
END
ELSE '' END
+ SPACE(2)
+ CASE
WHEN COLS.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
+ CASE WHEN @IncludeDefaults = 1 THEN CASE
WHEN COLS.[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] ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
ELSE '' END
END --iscomputed
+ ','
FROM tempdb.sys.columns COLS
LEFT OUTER JOIN tempdb.sys.default_constraints DEF
ON COLS.[default_object_id] = DEF.[object_id]
LEFT OUTER JOIN tempdb.sys.computed_columns CALC
ON COLS.[object_id] = CALC.[object_id]
AND COLS.[column_id] = CALC.[column_id]
WHERE COLS.[object_id]=@TABLE_ID
ORDER BY COLS.[column_id]
--##############################################################################
--used for formatting the rest of the constraints:
--##############################################################################
SELECT
@STRINGLEN = MAX(LEN([name])) + 1
FROM tempdb.sys.objects OBJS
--##############################################################################
--PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax
--##############################################################################
DECLARE @Results2 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 @Results2
SELECT
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM tempdb.sys.objects OBJS
INNER JOIN tempdb.sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
INNER JOIN tempdb.sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER 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 tempdb.sys.dm_db_partition_stats STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.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 COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
FROM tempdb.sys.index_columns IXCOLS
INNER JOIN tempdb.sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
FROM tempdb.sys.index_columns IXCOLS
INNER JOIN tempdb.sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE SCH.[name] LIKE CASE
WHEN @SCHEMANAME = ''
THEN SCH.[name]
ELSE @SCHEMANAME
END
AND OBJS.[name] LIKE CASE
WHEN @TBLNAME = ''
THEN OBJS.[name]
ELSE @TBLNAME
END
ORDER BY
SCH.[name],
OBJS.[name],
IDX.[name]
--@Results2 table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results:
SET @CONSTRAINTSQLS = ''
SET @INDEXSQLS = ''
--##############################################################################
--constriants
--##############################################################################
IF @IncludeUniqueKeys = 1 BEGIN
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 @Results2
WHERE [type_desc] != 'HEAP'
AND is_primary_key = 1
OR is_unique = 1
ORDER BY
is_primary_key DESC,
is_unique DESC
END --@IncludeUniqueKeys
--##############################################################################
--indexes
--##############################################################################
IF @IncludeIndexes = 1 BEGIN
SELECT @INDEXSQLS = @INDEXSQLS
+ CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE ' + type_desc + ' 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 @Results2
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
END --@IncludeIndexes
--##############################################################################
--CHECK Constraints
--##############################################################################
SET @CHECKCONSTSQLS = ''
IF @IncludeCheckConstraints = 1 BEGIN
SELECT
@CHECKCONSTSQLS = @CHECKCONSTSQLS
+ @vbCrLf
+ ISNULL('CONSTRAINT [' + OBJS.[name] + '] '
+ SPACE(@STRINGLEN - LEN(OBJS.[name]))
+ ' CHECK ' + ISNULL(CHECKS.definition,'')
+ ',','')
FROM tempdb.sys.objects OBJS
INNER JOIN tempdb.sys.check_constraints CHECKS ON OBJS.[object_id] = CHECKS.[object_id]
WHERE OBJS.type = 'C'
AND OBJS.parent_object_id = @TABLE_ID
END --@IncludeCheckConstraints
--##############################################################################
--FOREIGN KEYS
--##############################################################################
SET @FKSQLS = '' ;
IF @IncludeForeignKeys = 1 BEGIN
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 FKEYS
WHERE fkeyid = @TABLE_ID
END --@IncludeForeignKeys
--##############################################################################
--RULES
--##############################################################################
SET @RULESCONSTSQLS = ''
IF @IncludeRules = 1 BEGIN
SELECT
@RULESCONSTSQLS = @RULESCONSTSQLS
+ ISNULL(
@vbCrLf
+ 'if not exists(SELECT [name] FROM tempdb.sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(varchar(30),OBJS.schema_id) + ' AND [name] = ''[' + object_name(COLS.[rule_object_id]) + ']'')' + @vbCrLf
+ MODS.definition + @vbCrLf + 'GO' + @vbCrLf
+ 'EXEC sp_binderule [' + OBJS.[name] + '], ''[' + OBJECT_NAME(COLS.[object_id]) + '].[' + COLS.[name] + ']''' + @vbCrLf + 'GO' ,'')
FROM tempdb.sys.columns COLS
INNER JOIN tempdb.sys.objects OBJS
ON OBJS.[object_id] = COLS.[object_id]
INNER JOIN tempdb.sys.sql_modules MODS
ON COLS.[rule_object_id] = MODS.[object_id]
WHERE COLS.[rule_object_id] <> 0
AND COLS.[object_id] = @TABLE_ID
END --@IncludeRules
--##############################################################################
--TRIGGERS
--##############################################################################
SET @TRIGGERSTATEMENT = ''
IF @IncludeTriggers = 1 BEGIN
SELECT
@TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + MODS.[definition] + @vbCrLf + 'GO'
FROM tempdb.sys.sql_modules MODS
WHERE [object_id] IN(SELECT
[object_id]
FROM tempdb.sys.objects OBJS
WHERE type = 'TR'
AND [parent_object_id] = @TABLE_ID)
IF @TRIGGERSTATEMENT <> ''
SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT
END --@IncludeTriggers
--##############################################################################
--NEW SECTION QUERY ALL EXTENDED PROPERTIES
--##############################################################################
SET @EXTENDEDPROPERTIES = ''
IF @IncludeExtendedProperties = 1 BEGIN
SELECT @EXTENDEDPROPERTIES =
@EXTENDEDPROPERTIES + @vbCrLf +
'EXEC tempdb.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
END --@IncludeExtendedProperties
--##############################################################################
--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 ;
SET @input = @vbCrLf
+ @FINALSQL
+ @INDEXSQLS
+ @RULESCONSTSQLS
+ @TRIGGERSTATEMENT
+ @EXTENDEDPROPERTIES
SELECT @input;
RETURN;
END --PROC
Ben