Get DDL for any SQL 2005 table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lowell » Get DDL for any SQL 2005 table

66 posts, Page 7 of 7

«««3 4 5 6 7

Get DDL for any SQL 2005 table

Rate Topic

Display Mode

Topic Options

Author

Message

Lowell

Posted Tuesday, February 19, 2013 8:16 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

nikus thank you for the feedback! i really appreciate it!

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1421667

nikus

Posted Friday, February 22, 2013 6:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:48 AM
Points: 4, Visits: 15

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

Post #1423041

Lowell

Posted Friday, February 22, 2013 7:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1423048

nikus

Posted Friday, February 22, 2013 8:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:48 AM
Points: 4, Visits: 15

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

Post #1423082

Lowell

Posted Friday, February 22, 2013 8:33 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1423102

nikus

Posted Wednesday, March 13, 2013 5:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:48 AM
Points: 4, Visits: 15

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](
	[table] varchar(10) NOT NULL)

CREATE UNIQUE NONCLUSTERED INDEX index_test ON [dbo].test 
(
	[table] ASC
)WITH (PAD_INDEX  = ON, FILLFACTOR = 20) ON [INDEX]

CREATE UNIQUE CLUSTERED INDEX index_test2 ON [dbo].test 
(
	[table] ASC
)WITH (PAD_INDEX  = ON, FILLFACTOR = 20) ON [INDEX]

Nikus

Post #1430299

« Prev Topic | Next Topic »

66 posts, Page 7 of 7

«««3 4 5 6 7

Permissions