February 19, 2013 at 8:16 am
nikus thank you for the feedback! i really appreciate it!
Lowell
February 22, 2013 at 6:56 am
Hi
I added another small improvment.
The keyword "clustered" or "nonclustered" in the index creation statement.
Without this all indexes are created as nonclustered indexes which is in the most cases correct.
But theoreticly it is possible to create a clustered index which ist not a PK constraint.
For such an index this keyword is necessary.
Look for:
--##############################################################################
--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
and replace it with:
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
Just the "CREATE INDEX" line was changed.
Nikus
February 22, 2013 at 7:14 am
damn Nikus you've gotten two great improvements I've overlooked in as many days; my personal tunnel vision for the indexes was all scripted indexes were non clustered, so i never noticed differences. You've got a great attention for detail, awesome!
Thank you very, very much for the feedback, and I've already updated the scripts with your improvements.
Lowell
February 22, 2013 at 8:08 am
Hi Lowell
I am using your procedure for an automatic update script (from develpoment --> productive server).
During the test of the update script I found some differences which have lead to the improvements.
So it was a matter of chance.
I made an another improvement to add the filegroup of the table or indexes.
This time I will just post the changed script to keep the post shorter.
I added the declartion of the @DATASPACE_NAME variable.
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),
@DATASPACE_NAME VARCHAR(max)
The "dataspace_name" column was added on the end of the @Results table.
The "ISNULL(DSP.name, '---') dataspace_name" line was added in the select part.
The "INNER JOIN sys.data_spaces DSP ON DSP.data_space_id = IDX.data_space_id" line was added in the from part.
--##############################################################################
--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),
[dataspace_name]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,
ISNULL(DSP.name, '---') dataspace_name
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 sys.data_spaces DSP ON DSP.data_space_id = IDX.data_space_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
The last "case when dataspace_name ...." statement was added in the select part.
--##############################################################################
--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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
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
The last "case when dataspace_name ...." statement was added in the select part.
--##############################################################################
--indexes
--##############################################################################
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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
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
The "SELECT @DATASPACE_NAME .." statement was added
and the last line was changed.
The "WHERE type < 2" condition retuns either the filegroup of the clusterd index (if exists)
which is the physical sort order of the table or the filegroup of the heap entry which is the filegroup of the table.
--##############################################################################
--FINAL CLEANUP AND PRESENTATION
--##############################################################################
--at this point, there is a trailing comma, or it blank
SELECT @DATASPACE_NAME = dataspace_name FROM @Results
WHERE type < 2
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 + ') ON [' + @DATASPACE_NAME + ']' + @vbCrLf ;
I am pretty far with the testing now,
so I don't think that I will find anything else to improve.
As I said earlier this procedure saved me a lot of work.
Thanks again for sharing it.
Nikus
February 22, 2013 at 8:33 am
i had avoided that part for portability issues;
when i run scripts in my environment, we don't want to say what filegroup an object is on as far as the scripts go, because that can change from server to server, but i do have an alternate version i created for our clustered server that does exactly what you are suggesting, because on that server the scripts need to match the filegroups and such.
I also have a 2008 version that does the filtered indexes, which were introduced with 2008; the version here is 2005, which had include columns, but not filtered indexes.
I even have version that runs on a SQL server, but generates the DDL scripts compatible for use in an Oracle Environment(ie identity() columns become sequence and triggers, a lot more.
I really appreciate your feedback on this; makes it really worthwhile for me to have put it out to the community.
Lowell
March 13, 2013 at 5:01 am
Hi Lowell
I found 3 other things which could be interesting for you.
1) The column names in the index and constraint statements should be in squre brackets
for indexes where the column name is an reserved word (like 'table').
This can be easy done by inserting into the @result table
like
SELECT '[' + COLS.[name] + ']' + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
2) Since you have scripted the index option "FILLFACTOR"
you may reconsider to script the "PAD_INDEX" too.
"PAD_INDEX" status is stored in sys.indexes (column "is_padded")
so it can be read out during the insert into the @result table.
3) An "unique index" (clustered or nonclustered) is scripted as an "unique constraint"
which is more o less a bug.
But it is easy to fix.
Orginal code (constraint part):
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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
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
Corrected code (constraint part).
"is_unique" was replaced by "is_unique_constraint":
SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS
+ CASE
WHEN is_primary_key = 1 or is_unique_constraint = 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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
ELSE ''
END
ELSE ''
END + ','
FROM @RESULTS
WHERE [type_desc] != 'HEAP'
AND (is_primary_key = 1
OR is_unique_constraint = 1)
You yould go a step further and remove the outher "case when" statement which is not necessary
this will make the code shorter and easier to read.
SELECT @CONSTRAINTSQLS += @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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
ELSE ''
END
+ ','
FROM @RESULTS
WHERE [type_desc] != 'HEAP'
AND (is_primary_key = 1
OR is_unique_constraint = 1)
Orginal code (index part):
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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
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
Corrected code (index part).
"is_unique" was replaced by "is_unique_constraint"
AND the "or" in the "where condition" and the "when condition" was replaced by "and"
SELECT @INDEXSQLS = @INDEXSQLS
+ CASE
WHEN is_primary_key = 0 and is_unique_constraint = 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
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
ELSE ''
END
END
FROM @RESULTS
WHERE [type_desc] != 'HEAP'
AND is_primary_key = 0
AND is_unique_constraint = 0
ORDER BY
is_primary_key DESC,
is_unique DESC
In "index part" statement you can also remove the outer "case when" if you want.
Here is a query to check if you have any unique indexes in your system.
select *
from sys.indexes i
inner join sys.objects o
on i.object_id = o.object_id
where i.is_unique = 1
and i.is_unique_constraint = 0
and i.is_primary_key = 0
and o.type = 'U'
Here is some small code to test the 3 issues.
Create the table and compare the management studio code
with the code of your procedure.
CREATE TABLE [dbo].[test](
varchar(10) NOT NULL)
CREATE UNIQUE NONCLUSTERED INDEX index_test ON [dbo].test
(
ASC
)WITH (PAD_INDEX = ON, FILLFACTOR = 20) ON [INDEX]
CREATE UNIQUE CLUSTERED INDEX index_test2 ON [dbo].test
(
ASC
)WITH (PAD_INDEX = ON, FILLFACTOR = 20) ON [INDEX]
Nikus
January 15, 2014 at 10:21 am
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
January 30, 2014 at 11:19 am
Hi,
I am having an issue with the getting the DDL for tables with nonclustered indexes having the addition columns as constraints.
The script marks this in the comment sections that the syntax is only compatible with SQL Server 2005/08 editions.
"
-PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax
--##############################################################################
"
Are there any modifications happening in place to convert the index logic to match the 2012 standard?
July 30, 2014 at 7:11 am
Great job !
Just what I need.
I'm going to try and if I'll have suggesionns.. I' ll post them.
Thanks All
Sal
December 19, 2014 at 3:58 pm
When the index is scripted, it doesn't include the schema, i.e. line 566:
' ON [' + [object_name] + ']'
Should be
' ON [' + [schema_name] + '].[' + [object_name] + ']'
And the same further down.
Great script. Thanks
January 15, 2015 at 9:36 am
This is helpful, very nice. Thanks for sharing.
It would appear this does not account for Extended Properties at the column level. Added this code to catch the extended properties for columns:
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 + '],
@level2type = N''COLUMN'', @level2name = [' + [objname] + '];'
--SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, 'column', NULL)
Also there is no sorting on included columns in indexes.
So I changed the line in the 2nd sub select in two queries
from
SELECT COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
to
SELECT COLS.[name] + ',' + ' '
March 30, 2015 at 12:53 am
Hi All,
Could you please share latest and updated script.
Many Thanks in advance!!
-Ganesh Choudhari
March 30, 2015 at 1:03 pm
OK for the folks who have bothered to follow this thread, a minor update, with a bunch of changes that i added myself,as well as some that were also contributed through the fine people here, who helped make this even better.
Updated yet again...same old links but new code.
--V314 03/30/2015
-- did i mention this scripts out temp tables too? sp_GetDDL #tmp
-- scripts a LOT more objects... a better replacement for sp_helptext i think.
-- added ability to script synonyms
-- moved logic for REAL datatype to fix error when scripting real columns
-- added OmaCoders suggestion to script column extended properties as well.
-- added matt_slack suggestion to script schemaname as part of index portion of script.
-- minor script cleanup to use QUOTENAME insead of concatenating square brackets.
-- changed compatibility to 2008 and above only, now filtered idnexes with WHERE statmeents script correctly
-- foreign key tables and columns in script now quotenamed to accoutn for spaces in names; previously an error for Applciation ID instead of [Application ID]
Lowell
October 22, 2015 at 6:11 am
it does not work on SQL 2008 R2. Is there any other working version available ?
October 22, 2015 at 6:14 am
I downloaded the script from http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt, it does not work on SQL2008 R2. It does not print all the columns of the below table.
CREATE TABLE dbo.Test1 ( id int NOT NULL, name VARCHAR(100), PRIMARY KEY (id) );
Is there any other version available ?
Viewing 15 posts - 61 through 75 (of 127 total)
You must be logged in to reply to this topic. Login to reply