Make string columns unicode compliant

Question

Make string columns unicode compliant

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 1

Thought I'd update this since I have re-visited the original script quite a lot recently and made some improvements. The major change is that when I ran this on real-world databases instead of test databases (i.e databases of many tens of GB rather than ones which were practically empty) it took a VERY long time. In fact I stopped it running in most cases. The problem was that trying to change lots of columns in a table with tens of millions of rows is pretty slow and requires acres of log space. As a result, tables are now created afresh with the suffix '_new' and data is then transferred across, the original table is dropped and the new one renamed. This process also takes care of explicitly casting data, identity inserts and so on. As a result, a database which we gave up on after some 15 or 16 hours now takes around 50 minutes to convert.

Other enhancements include:

Database recovery model is changed to BULK_LOGGED at the start and reverts to its original value afterwards.
If the database and/or log are set to no auto-growth and/or fixed size, this is changed to allow auto-growth and unlimited size, with these values reverting to their original state at the end.
Handles multiple file groups to ensure replacement tables end up on the correct filegroup (indexes etc already had this).
Adds include columns to indexes where they exist.

Anyway, here it is - hope it's useful. Will try and take account of any mistakes or omissions pointed out when I get the time.

/************************************************************************************************************************

Created By: Steve Pettifer

Date: 25/08/2010

First and foremost YOU MUST ENSURE THAT YOU BACK UP THE DATABASE FIRST AND THEN VERIFY THAT YOU CAN SUCCESSFULLY RESTORE

THAT BACKUP. This phrase will be repeated several times. If you do not do this and the process goes wrong you will have NO

rollback plan at all. This script involves commands that should only be used in extremis and only when you have a solid,

verifiable backout position which demonstrably works.

This script is designed allow all TEXT, NTEXT, CHAR and VARCHAR columns within a database to be changed en-masse to

NVARCHAR. To accomplish this in the simplest way, we must drop all foreign keys, primary keys, indexes, default

constraints, unique constraints and check constraints before altering the columns and then recreating all the dropped

objects. The purpose of this script is to generate, based on the system views of a database, all the nescesary DROP,

ALTER and CREATE statements for this operation. All keys, indexes etc will be re-created exactly as they were in terms

of column order, data sorting direction and so on, and foreign keys will also be re-created WITH NOCHECK if they were

previously disabled (as will check constraints, althoguh if check constraints were previously enabled and trusted then

a statement will be generated to ensure the constraint is set that way again). Schema names are automatically prepended

to appropriate obejct names according to ownership. Indexes, Primary Keys and Unique Constraints will be assigned to the

appropriate filegroups automatically.

LIMITATIONS

1) This script will only work on SQL Server 2005 and above as it uses system views in the sys schema, and not INFORMATION_SCHEMA

views or the old SQL 2000-style system tables.

2) Foreign keys that are disabled are re-created as disabled. However, if after this you simply re-enable the key this

does not mean the optimiser will use it as the is_not_trusted field may not have been reset. To counter this, either drop

the key re-create it WITH CHECK or user ALTER TABLE <tableName> CHECK CONSTRAINT ALL command. Avoid using the DBCC

CHECKCONSTRAINTS command as this has been deprecated after SQL 2005.

3) This script was designed to work with a specific database and thus only encompasses those objects which would

be a barrier to changing the string type columns in that database. In theory at least, this script should work on ANY database,

however it may not take into account all objects which could potentially affect a column, and therefore it may require

additional script blocks for those objects. Once again, it will NOT work on SQL 2000.

4) Objects covered: Primary Keys, Foreign Keys, Indexes, Default Constraints, Unique Constraints, Check Constraints.

5) Does NOT currently script XML or Spatial indexes.

USAGE

1) Ensure that all users are disconnected from the database. However, do not put the database into single user mode as if

something goes wrong it can be difficult to get it out again, and if you back up a database in single user mode, it will

restore in single user mode.

2) As always, before making any structural or data changes to any database YOU MUST ENSURE THAT YOU BACK UP THE DATABASE FIRST

AND THEN VERIFY THAT YOU CAN SUCCESSFULLY RESTORE THAT BACKUP. Do not assume that just because you have performed a backup that

you are protected, always verify that the backup will restore correctly before you proceed. Once you have a functioning backup,

it is strongly recommended that you make a dry-run of this procedure by trying it on a restored copy of the database before

running it against the final target, especially if the final target is a production platform.

3) Ensure that you replace DBNAME_HERE in the USE statement near the top with the name of the database that you wish to alter.

4) Ensure that you output results to text and set the column width to 8192 (In SSMS go to Tools -> Options and select the

Query Results node in the treeview on the left. Change the 'Default destination for results' drop down to 'Results to text'.

Now expand the 'Query Results' node and then the 'SQL Server' node below it, and finally select the 'Results To Text' node.

Set the 'Maximum number of characters displayed in each column' field to 8192 (this is the largest allowed value). Now click

OK. These changes will only be applied to new query windows so if needs be, open a new query window and then re-open this script.

5) Run this script (it should not take very long).

6) Copy the ouput from this script to a new query window and then parse the query to verify that the syntax is correct

(this is the blue tick button on the SSMS toolbar next to the Execute button).

7) Once you are satisfied that the query will parse, you can execute it. The length of time it takes to execute will vary

depending upon the size of the database, and the capability of the hardware but it may take anything up to 10 or 15 minutes

or more. You can see the progress of the script in the results as the script will print out which section it is

executing.

8) If you encounter any errors you may need to refer to the script author to include any extra object types which prove to be

a barrier to making column alterations. DO NOT ATTEMPT TO EDIT THIS SCRIPT YOURSELF UNLESS YOU ARE COMPLETELY FAMILIAR WITH

SQL SERVER SYSTEM CATALOG VIEWS AND HOW TO DROP AND CREATE OBJECTS. Also, avoid editing the output from this script.

************************************************************************************************************************/

SET NOCOUNT ON

--USE [DBNAME_HERE]

--Declare and set global line break constant.

DECLARE @crlfCHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

--Declare some variables to hold the existing growth and max size values for the system files. If the values for either faile

--are set to 0 (i.e. no growth) or the max size is not set to -1 (i.e. unlimited) then those values will be set arbitrarily to

--10 and -1 then reset back to what they were afterwards. Don't care if growth is in percent or pages so long as it can grow.

DECLARE @dbGrowth INT

DECLARE @logGrowth INT

DECLARE @dbMaxSize BIGINT

DECLARE @logMaxSize BIGINT

DECLARE @dbFileName SYSNAME

DECLARE @logFileName SYSNAME

DECLARE @dbId INT

SET @dbGrowth = 0

SET @logGrowth = 0

SET @dbMaxSize = 0

SET @logMaxSize = 0

--Declare variable to hold recovery model.

DECLARE @dbRecoveryModel NVARCHAR(25)

--We don't want to be warned that aggregate functions have eliminated nulls values - this is behaviour that we want.

--Temporarily turn off the warnings.

SET ANSI_WARNINGS OFF

-----------------------------------------------------------------------------------------------------------------

--Set context to master.

SELECT 'USE [master]' + @crlf + 'GO'

-----------------------------------------------------------------------------------------------------------------

--Get ID of this database. Need this as we execute the first few commnds of the generated script in the context of the master db.

--Need recovery model so we can revert to it at the end.

SELECT @dbId = d.[database_id], @dbRecoveryModel = d.[recovery_model_desc] FROM sys.databases d WHERE d.[name] = DB_NAME()

--Get the max size and growth information for the first data file you can find. Doesn't matter which data file it is,

--but at least one MUST have autogrowth switched on for this script to work. This also applies to log files in that

--at least one must be able to autogrow. Original settings are restored afterwards.

SELECT TOP 1 @dbFileName = m.[name], @dbGrowth = m.[growth], @dbMaxSize = m.[max_size] FROM sys.master_files m WHERE m.[database_id] = @dbId AND m.[type] = 0

SELECT TOP 1 @logFileName = m.[name], @logGrowth = m.[growth], @logMaxSize = m.[max_size] FROM sys.master_files m WHERE m.[database_id] = @dbId AND m.[type] = 1

IF @dbGrowth = 0 OR @dbMaxSize <> -1

--Either auto growth is off or a max size is specified. Set to on and unlimited.

BEGIN

SELECT 'PRINT ''Setting database to unresctricted file growth and size...'''

UNION ALL

SELECT 'ALTER DATABASE [' + DB_NAME() + '] MODIFY FILE (NAME = ' + @dbFileName + ', FILEGROWTH = 10, MAXSIZE = UNLIMITED)'

END

IF @logGrowth = 0 OR @logMaxSize <> -1

--Either auto growth is off or a max size is specified. Set to on and unlimited.

BEGIN

SELECT 'PRINT ''Setting log to unresctricted file growth and size...'''

UNION ALL

SELECT 'ALTER DATABASE [' + DB_NAME() + '] MODIFY FILE (NAME = ' + @logFileName + ', FILEGROWTH = 10, MAXSIZE = UNLIMITED)'

END

-----------------------------------------------------------------------------------------------------------------

--Set recovery model to BULK_LOGGED to try and minimise log writes when copying data.

SELECT 'PRINT ''Setting recovery model to BULK_LOGGED...'''

UNION ALL

SELECT 'ALTER DATABASE [' + DB_NAME() + '] SET RECOVERY BULK_LOGGED WITH NO_WAIT;' + @crlf + 'GO'

-----------------------------------------------------------------------------------------------------------------

--Change DB context to target DB.

SELECT 'USE [' + DB_NAME() + ']' + @crlf + 'GO'

-----------------------------------------------------------------------------------------------------------------

--Generate index/unique constraint DROP statements

SELECT 'PRINT ''Dropping indexes and unique constraints...''' + @crlf + @crlf

UNION ALL

SELECT CASE

WHEN i.is_unique_constraint = 1 THEN

'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + i.[name] + ']' + @crlf + 'GO'

ELSE

'DROP INDEX [' + i.[name] + '] ON ' + s.[name] + '.[' + t.[name] + '] WITH (ONLINE=OFF) ' + @crlf + 'GO'

END

FROM sys.indexes i

INNER JOIN sys.tables t

ON t.[object_id] = i.[object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

WHERE i.[is_primary_key] = 0

AND i.[type] IN (1, 2)--Ignore heap, xml and spatial indexes.

AND t.[type] = 'U'--Ensure we only look at user tables.

-----------------------------------------------------------------------------------------------------------------

--Generate foreign key DROP statements.

SELECT 'PRINT ''Dropping foreign keys...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + OBJECT_NAME(f.parent_object_id) + '] DROP CONSTRAINT [' + f.[name] + '] ' + @crlf + 'GO'

FROM sys.foreign_keys f

INNER JOIN sys.tables t

ON t.[object_id] = f.[parent_object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

WHERE t.[type] = 'U'--Ensure we only look at user tables.

-----------------------------------------------------------------------------------------------------------------

--Generate primary key DROP statements.

SELECT 'PRINT ''Dropping primary keys...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + i.[name] + ']' + @crlf + 'GO'

FROM sys.indexes i

INNER JOIN sys.tables t

ON t.[object_id] = i.[object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

WHERE i.[is_primary_key] = 1

AND t.[type] = 'U'--Ensure we only look at user tables.

-----------------------------------------------------------------------------------------------------------------

--Generate default constraint DROP statements.

SELECT 'PRINT ''Dropping default constraints...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + d.[name] + ']' + ' ' + @crlf + 'GO'

FROM sys.default_constraints d

INNER JOIN sys.tables t

ON t.[object_id] = d.[parent_object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

WHERE t.[type] = 'U'--Ensure we only look at user tables.

-----------------------------------------------------------------------------------------------------------------

--Generate check constraint DROP statements.

SELECT 'PRINT ''Dropping check constraints...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + co.[name] + ']' + @crlf + 'GO'

FROM sys.check_constraints co

INNER JOIN sys.tables t

ON t.[object_id] = co.[parent_object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

WHERE t.[type] = 'U'--Ensure we only look at user tables.

-----------------------------------------------------------------------------------------------------------------

SELECT 'PRINT ''Generating table and column definitions...'''

--Generate statements to create new copies of tables which require alteration, copy data across, drop old table and rename new ones.

--Altering data types on columns in tables which have lots of rows (millions of rows are not uncommon) is deeply inefficient. For the

--purposes of generating scripts which can run without interference the simplest and most efficient alternative is to make copies of

--the tables with the new data types in place, copy and transform data into them then drop the originals and rename the new ones. It's

--still not what you'd call lightning fast but you can't have everything.

DECLARE @tables TABLE (tableId INT IDENTITY(1,1), schemaName SYSNAME, tableName SYSNAME, tableDef NVARCHAR(4000), hasIdentity BIT, fileGroup SYSNAME)

DECLARE @columns TABLE (fieldId INT IDENTITY(1,1), tableId INT, systemTypeId INT, width INT, orderId INT, colName SYSNAME, colDef NVARCHAR(4000))

DECLARE @columnLists TABLE (listId INT IDENTITY(1,1), tableId INT, columnList NVARCHAR(4000), selectList NVARCHAR(4000))

--Get table names and various bits of metadata for tables which contain TEXT, NTEXT, CHAR or VARCHAR types as these need to be

--created afresh and have data copied across as changing column types on millions of rows is pretty inefficient and this is

--considerably faster.

INSERT INTO @tables ([schemaName], [tableName], [tableDef], [hasIdentity], [fileGroup])

SELECT DISTINCT s.[name], t.[name], 'CREATE TABLE [' + s.[name] + '].[' + t.[name] + '_new] (', x.[is_identity], f.[name]

FROM sys.columns c

INNER JOIN sys.tables t

ON t.[object_id] = c.[object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

LEFT OUTER JOIN (SELECT DISTINCT c2.[object_id], MAX(CAST(c2.[is_identity] AS INT)) AS [is_identity] FROM sys.columns c2 GROUP BY c2.[object_id]) x

ON x.[object_id] = t.[object_id]

INNER JOIN sys.indexes i

ON i.[object_id] = t.[object_id]

INNER JOIN sys.filegroups f

ON f.[data_space_id] = i.[data_space_id]

WHERE c.[system_type_id] IN (35,99,167,175)--TEXT, NTEXT, VARCHAR, CHAR

AND t.[type] = 'U'--Ensure we only look at user tables.

GROUP BY s.[name], t.[name], x.[is_identity], f.[name]

--Now we want column definitions and various permutations of the columns lists. This will be used with the data extracted above to form

--CREATE TABLE statements with corrected datatypes (NVARCHAR and NCHAR).

INSERT INTO @columns ([tableId], [systemTypeId], [width], [orderId], [colName], [colDef])

SELECT tt.[tableId], c.[system_type_id], c.[max_length], c.[column_id], c.[name],

'[' + c.[name] + '] ' +

--Data types.

CASE

WHEN c.[system_type_id] = 167 OR c.[system_type_id] = 35 OR c.[system_type_id] = 99 THEN

--VARCHAR, TEXT and NTEXT change to NVARCHAR

'[NVARCHAR] '

WHEN c.[system_type_id] = 175 THEN

--CHAR changes to NCHAR

'[NCHAR] '

WHEN c.[system_type_id] = 231 THEN

--Check for SYSNAME amongst NVARCHAR fields.

CASE

WHEN c.[user_type_id] = 256 THEN

--SYSNAME has a system_type_id of 231, same as NVARCHAR as it is a synonym for a specific size of N VARCHAR field.

--It is identified by the user_type_id of 256.

'[SYSNAME] '

ELSE

'[NVARCHAR] '

END

ELSE

--Everything else stays the same.

'[' + UPPER(ty.[name]) + '] '

END +

--Field width.

CASE

WHEN c.[system_type_id] = 106 OR c.[system_type_id] = 108 THEN

--DECIMAL and NUMERIC declared with precision and scale (p,s)

'(' + CAST(c.[precision] AS NVARCHAR(2)) + ',' + CAST(c.[scale] AS NVARCHAR(2)) + ') '

WHEN c.[system_type_id] = 62 THEN

--FLOAT has only one parameter for width, the precision value.

'(' + CAST(c.[precision] AS NVARCHAR(2)) + ') '

WHEN c.[system_type_id] = 167 THEN

--VARCHAR fields above 4000 become NVARCHAR(MAX). -1 means they were already MAX fields.

CASE

WHEN c.[max_length] = -1 THEN

--Declared as MAX.

'(MAX) '

WHEN c.[max_length] > 4000 THEN

'(MAX) '

ELSE

'(' + CAST(c.[max_length] AS NVARCHAR(4)) + ') '

END

WHEN c.[system_type_id] = 175 THEN

--CHAR fields above 4000 become NCHAR(4000) which is the maximum width for NCHAR fields.

CASE

WHEN c.[max_length] >= 4000 THEN

'(4000) '

ELSE

'(' + CAST(c.[max_length] AS NVARCHAR(4)) + ') '

END

WHEN c.[system_type_id] = 231 THEN

--NVARCHAR and SYSNAME fields stay as they are. SYSNAME does not require a width declaration so check if the field is an

--NVARCHAR in the user_type_id and set its length as needed. Note that c.[max_length] must be divided by 2 for N fields.

-- -1 means NVARCHAR(MAX)

CASE

WHEN c.[user_type_id] = 231 THEN

CASE

WHEN c.[max_length] = -1 THEN

'(MAX) '

ELSE

'(' + CAST(c.[max_length]/2 AS NVARCHAR(4)) + ') '

END

WHEN c.[system_type_id] = 239 THEN

--NCHAR stays as it is. Note that c.[max_length] must be divided by 2 for N fields.

'(' + CAST(c.[max_length]/2 AS NVARCHAR(4)) + ') '

WHEN c.[system_type_id] = 35 OR c.[system_type_id] = 99 THEN

--TEXT and NTEXT become NVARCHAR(MAX)

'(MAX) '

ELSE

--INT, BIGINT, BIT, TINYINT, REAL, DATETIME etc etc etc.

''

END +

CASE

--Add NULL/NOT NULL.

WHEN c.[is_nullable] = 1 THEN

'NULL '

ELSE

'NOT NULL '

END +

CASE

--Add identity specification.

WHEN c.[is_identity] = 1 THEN

'IDENTITY(' + CAST(ic.[seed_value] AS NVARCHAR(4)) + ',' + CAST(ic.[increment_value] AS NVARCHAR(4)) + ') '

ELSE

''

END

FROM sys.columns c

INNER JOIN sys.tables t

ON t.[object_id] = c.[object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

INNER JOIN sys.types ty

ON ty.[system_type_id] = c.[system_type_id]

AND ty.[user_type_id] = c.[user_type_id]

LEFT OUTER JOIN sys.identity_columns ic

ON c.[object_id] = ic.[object_id]

INNER JOIN @tables tt

ON tt.[tableName] = t.[name]

ORDER BY c.[object_id], c.[column_id];

--Now lets build up the declarations to create tables and columns from the assembled information.

DECLARE @tableCount INT

DECLARE @tableMax INT

DECLARE @columnCount INT

DECLARE @columnMax INT

SELECT @tableCount = MIN(tableId), @tableMax = MAX(tableId) FROM @tables

DECLARE @columnSpec NVARCHAR(4000)

DECLARE @selectList NVARCHAR(4000)

DECLARE @columnList NVARCHAR(4000)

--Loop through each table and then for each table loop through the columns.

WHILE (@tableCount <= @tableMax)

BEGIN

SET @columnSpec = ''

SET @selectList = ''

SET @columnList = ''

SELECT @columnCount = MIN(orderId), @columnMax = MAX(orderId) FROM @columns WHERE tableId = @tableCount

WHILE (@columnCount <= @columnMax)

BEGIN

IF EXISTS (SELECT 1 FROM @columns WHERE tableId = @tableCount AND orderId = @columnCount)

BEGIN

--Build table definition from column info.

SELECT @columnSpec = @columnSpec + colDef + @crlf FROM @columns WHERE tableId = @tableCount AND orderId = @columnCount

--Build column list.

SELECT @columnList = @columnList + '[' + colName + ']' FROM @columns WHERE tableId = @tableCount AND orderId = @columnCount

--Build select list.

SELECT @selectList = @selectList +

CASE

WHEN systemTypeId = 175 THEN

--CHAR fields will have to be truncated if they are over 4000 long because NCHAR cannot exceed 4000 (cannot use MAX on CHAR/NCHAR as it is a fixed width field)

CASE

WHEN width > 4000 THEN

'SUBSTRING ([' + colName + '], 0, 4000)'

ELSE

'[' + colName + ']'

END

ELSE

'[' + colName + ']'

END

FROM @columns WHERE tableId = @tableCount AND orderId = @columnCount

IF @columnCount <> @columnMax

BEGIN

SET @columnSpec = @columnSpec + ', '

SET @selectList = @selectList + ', '

SET @columnList = @columnList + ', '

END

SET @columnCount = @columnCount + 1

END

SELECT t.[tableDef] + @crlf + @columnSpec + ') ON [' + t.[fileGroup] + ']' FROM @tables t WHERE t.[tableId] = @tableCount

--Save column list and select list for later use (that way we don't have to go thought and build it up a second time).

INSERT INTO @columnLists (tableId, columnList, selectList) VALUES(@tableCount, @columnList, @selectList)

SET @tableCount = @tableCount + 1

END

--Build and print statements to transfer data from existing table to new table including any truncation/conversion statements.

SELECT 'PRINT ''Creating new tables and transferring data...''' + @crlf + @crlf

UNION ALL

SELECT 'PRINT ''Table: ' + t.[tableName] + '...''' +

CASE

WHEN t.[hasIdentity] = 1 THEN

--Identity field is present. Switch on IDENTITY_INSERT.

'SET IDENTITY_INSERT [' + t.[schemaName] + '].[' + t.[tableName] + '_new] ON' + @crlf

ELSE

''

END +

'INSERT INTO [' + t.[schemaName] + '].[' + t.[tableName] + '_new](' + l.[columnList] + ')' + @crlf +

'SELECT ' + l.[selectList] + @crlf +

'FROM [' + t.[schemaName] + '].[' + t.[tableName] + ']' + @crlf +

CASE

WHEN t.[hasIdentity] = 1 THEN

--Switch of IDENTITY_INSERT,

'SET IDENTITY_INSERT [' + t.[schemaName] + '].[' + t.[tableName] + '_new] OFF' + @crlf + @crlf

ELSE

@crlf + @crlf

END

FROM @tables t

INNER JOIN @columnLists l

ON l.[tableId] = t.[tableId]

--Now drop old tables and rename new ones.

SELECT 'PRINT ''Dropping old tables and renaming new ones...''' + @crlf + @crlf

UNION ALL

SELECT 'DROP TABLE [' + t.[schemaName] + '].[' + t.[tableName] + '];' + @crlf +

'EXEC sp_rename ''' + t.[schemaName] + '.' + t.[tableName] + '_new'', ''' + t.[tableName] + ''';' + @crlf + @crlf

FROM @tables t

-----------------------------------------------------------------------------------------------------------------

--Generate default constraint statements.

SELECT 'PRINT ''Creating default constraints...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] ADD CONSTRAINT [' + d.[name] + '] DEFAULT ' + d.[definition] + ' FOR [' + c.[name] + ']' + ' ' + @crlf + 'GO'

FROM sys.default_constraints d

INNER JOIN sys.columns c

ON c.[object_id] = d.[parent_object_id]

AND c.[column_id] = d.[parent_column_id]

INNER JOIN sys.tables t

ON t.[object_id] = d.[parent_object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

WHERE t.[type] = 'U'--Ensure we only look at user tables.

-----------------------------------------------------------------------------------------------------------------

--Generate PRIMARY KEY statments.

SELECT 'PRINT ''Creating primary keys...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] ADD CONSTRAINT [' + i.[name] + '] PRIMARY KEY ' + CASE i.[type] WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + @crlf +

' (' + @crlf +

ISNULL(' [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 1) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 2) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 3) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 4) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 5) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 6) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 7) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 8) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 9) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 10) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 11) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 12) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 13) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 14) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 15) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], 16) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

')' + @crlf + ' WITH( ' +

'PAD_INDEX = ' + CASE WHEN i.[is_padded] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' STATISTICS_NORECOMPUTE = ' + CASE WHEN st.[no_recompute] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' SORT_IN_TEMPDB = OFF,' +

' IGNORE_DUP_KEY = ' + CASE WHEN i.[ignore_dup_key] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' ONLINE = OFF,' +

' ALLOW_ROW_LOCKS = ' + CASE WHEN i.[allow_row_locks] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' ALLOW_PAGE_LOCKS = ' + CASE WHEN i.[allow_page_locks] = 1 THEN ' ON' ELSE ' OFF' END +

') ON [' + f.[name] + ']' + @crlf + 'GO'

FROM sys.indexes i

INNER JOIN sys.index_columns ic

ON ic.[object_id] = i.[object_id]

AND ic.[index_id] = i.[index_id]

INNER JOIN sys.stats st

ON st.[name] = i.[name]

INNER JOIN sys.tables t

ON t.[object_id] = i.[object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

INNER JOIN sys.filegroups f

ON f.[data_space_id] = i.[data_space_id]

WHERE i.[is_primary_key] = 1

AND t.[type] = 'U'--Ensure we only look at user tables.

GROUP BY s.[name], t.[name], i.[name], i.[type], ic.[is_descending_key], i.[is_padded], st.[no_recompute], i.[ignore_dup_key], i.[allow_row_locks], i.[allow_page_locks], f.[name], i.[object_id], i.[index_id]

PRINT '-----------------------------------------------------------------------------------------------------------------'

--Generate FOREIGN KEY statements.

SELECT 'PRINT ''Creating foreign keys...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s1.[name] + '].[' + t1.[name] + '] WITH ' + CASE WHEN fk.[is_disabled] = 0 AND fk.[is_not_trusted] = 0 THEN 'CHECK' ELSE 'NOCHECK' END +

' ADD CONSTRAINT [' + fk.[name] + '] FOREIGN KEY (' +

CASE WHEN r.[fkey1] = 0 THEN '' ELSE '[' + c1.[name] + ']' END +

CASE WHEN r.[fkey2] = 0 THEN '' ELSE ', [' + c2.[name] + ']' END +

CASE WHEN r.[fkey3] = 0 THEN '' ELSE ', [' + c3.[name] + ']' END +

CASE WHEN r.[fkey4] = 0 THEN '' ELSE ', [' + c4.[name] + ']' END +

CASE WHEN r.[fkey5] = 0 THEN '' ELSE ', [' + c5.[name] + ']' END +

CASE WHEN r.[fkey6] = 0 THEN '' ELSE ', [' + c6.[name] + ']' END +

CASE WHEN r.[fkey7] = 0 THEN '' ELSE ', [' + c7.[name] + ']' END +

CASE WHEN r.[fkey8] = 0 THEN '' ELSE ', [' + c8.[name] + ']' END +

CASE WHEN r.[fkey9] = 0 THEN '' ELSE ', [' + c9.[name] + ']' END +

CASE WHEN r.[fkey10] = 0 THEN '' ELSE ', [' + c10.[name] + ']' END +

CASE WHEN r.[fkey11] = 0 THEN '' ELSE ', [' + c11.[name] + ']' END +

CASE WHEN r.[fkey12] = 0 THEN '' ELSE ', [' + c12.[name] + ']' END +

CASE WHEN r.[fkey13] = 0 THEN '' ELSE ', [' + c13.[name] + ']' END +

CASE WHEN r.[fkey14] = 0 THEN '' ELSE ', [' + c14.[name] + ']' END +

CASE WHEN r.[fkey15] = 0 THEN '' ELSE ', [' + c15.[name] + ']' END +

CASE WHEN r.[fkey16] = 0 THEN '' ELSE ', [' + c16.[name] + ']' END +

') REFERENCES [' + s2.[name] + '].[' + t2.[name] + '](' +

CASE WHEN r.[rkey1] = 0 THEN '' ELSE '[' + c17.[name] + ']' END +

CASE WHEN r.[rkey2] = 0 THEN '' ELSE ', [' + c18.[name] + ']' END +

CASE WHEN r.[rkey3] = 0 THEN '' ELSE ', [' + c19.[name] + ']' END +

CASE WHEN r.[rkey4] = 0 THEN '' ELSE ', [' + c20.[name] + ']' END +

CASE WHEN r.[rkey5] = 0 THEN '' ELSE ', [' + c21.[name] + ']' END +

CASE WHEN r.[rkey6] = 0 THEN '' ELSE ', [' + c22.[name] + ']' END +

CASE WHEN r.[rkey7] = 0 THEN '' ELSE ', [' + c23.[name] + ']' END +

CASE WHEN r.[rkey8] = 0 THEN '' ELSE ', [' + c24.[name] + ']' END +

CASE WHEN r.[rkey9] = 0 THEN '' ELSE ', [' + c25.[name] + ']' END +

CASE WHEN r.[rkey10] = 0 THEN '' ELSE ', [' + c26.[name] + ']' END +

CASE WHEN r.[rkey11] = 0 THEN '' ELSE ', [' + c27.[name] + ']' END +

CASE WHEN r.[rkey12] = 0 THEN '' ELSE ', [' + c28.[name] + ']' END +

CASE WHEN r.[rkey13] = 0 THEN '' ELSE ', [' + c29.[name] + ']' END +

CASE WHEN r.[rkey14] = 0 THEN '' ELSE ', [' + c30.[name] + ']' END +

CASE WHEN r.[rkey15] = 0 THEN '' ELSE ', [' + c31.[name] + ']' END +

CASE WHEN r.[rkey16] = 0 THEN '' ELSE ', [' + c32.[name] + ']' END +

')' + @crlf + 'GO' +

CASE WHEN fk.[is_disabled] = 1 THEN @crlf + 'ALTER TABLE [' + s1.[name] + '].[' + t1.[name] + '] NOCHECK CONSTRAINT [' + fk.[name] + ']' + @crlf + 'GO' ELSE '' END

FROM sys.foreign_keys fk

INNER JOIN sys.sysreferences r

ON r.[constid] = fk.[object_id]

LEFT OUTER JOIN sys.columns c1 ON c1.[object_id] = r.[fkeyid] AND c1.[column_id] = r.[fkey1]

LEFT OUTER JOIN sys.columns c2 ON c2.[object_id] = r.[fkeyid] AND c2.[column_id] = r.[fkey2]

LEFT OUTER JOIN sys.columns c3 ON c3.[object_id] = r.[fkeyid] AND c3.[column_id] = r.[fkey3]

LEFT OUTER JOIN sys.columns c4 ON c4.[object_id] = r.[fkeyid] AND c4.[column_id] = r.[fkey4]

LEFT OUTER JOIN sys.columns c5 ON c5.[object_id] = r.[fkeyid] AND c5.[column_id] = r.[fkey5]

LEFT OUTER JOIN sys.columns c6 ON c6.[object_id] = r.[fkeyid] AND c6.[column_id] = r.[fkey6]

LEFT OUTER JOIN sys.columns c7 ON c7.[object_id] = r.[fkeyid] AND c7.[column_id] = r.[fkey7]

LEFT OUTER JOIN sys.columns c8 ON c8.[object_id] = r.[fkeyid] AND c8.[column_id] = r.[fkey8]

LEFT OUTER JOIN sys.columns c9 ON c9.[object_id] = r.[fkeyid] AND c9.[column_id] = r.[fkey9]

LEFT OUTER JOIN sys.columns c10 ON c10.[object_id] = r.[fkeyid] AND c10.[column_id] = r.[fkey10]

LEFT OUTER JOIN sys.columns c11 ON c11.[object_id] = r.[fkeyid] AND c11.[column_id] = r.[fkey11]

LEFT OUTER JOIN sys.columns c12 ON c12.[object_id] = r.[fkeyid] AND c12.[column_id] = r.[fkey12]

LEFT OUTER JOIN sys.columns c13 ON c13.[object_id] = r.[fkeyid] AND c13.[column_id] = r.[fkey13]

LEFT OUTER JOIN sys.columns c14 ON c14.[object_id] = r.[fkeyid] AND c14.[column_id] = r.[fkey14]

LEFT OUTER JOIN sys.columns c15 ON c15.[object_id] = r.[fkeyid] AND c15.[column_id] = r.[fkey15]

LEFT OUTER JOIN sys.columns c16 ON c16.[object_id] = r.[fkeyid] AND c16.[column_id] = r.[fkey16]

LEFT OUTER JOIN sys.columns c17 ON c17.[object_id] = r.[rkeyid] AND c17.[column_id] = r.[rkey1]

LEFT OUTER JOIN sys.columns c18 ON c18.[object_id] = r.[rkeyid] AND c18.[column_id] = r.[rkey2]

LEFT OUTER JOIN sys.columns c19 ON c19.[object_id] = r.[rkeyid] AND c19.[column_id] = r.[rkey3]

LEFT OUTER JOIN sys.columns c20 ON c20.[object_id] = r.[rkeyid] AND c20.[column_id] = r.[rkey4]

LEFT OUTER JOIN sys.columns c21 ON c21.[object_id] = r.[rkeyid] AND c21.[column_id] = r.[rkey5]

LEFT OUTER JOIN sys.columns c22 ON c22.[object_id] = r.[rkeyid] AND c22.[column_id] = r.[rkey6]

LEFT OUTER JOIN sys.columns c23 ON c23.[object_id] = r.[rkeyid] AND c23.[column_id] = r.[rkey7]

LEFT OUTER JOIN sys.columns c24 ON c24.[object_id] = r.[rkeyid] AND c24.[column_id] = r.[rkey8]

LEFT OUTER JOIN sys.columns c25 ON c25.[object_id] = r.[rkeyid] AND c25.[column_id] = r.[rkey9]

LEFT OUTER JOIN sys.columns c26 ON c26.[object_id] = r.[rkeyid] AND c26.[column_id] = r.[rkey10]

LEFT OUTER JOIN sys.columns c27 ON c27.[object_id] = r.[rkeyid] AND c27.[column_id] = r.[rkey11]

LEFT OUTER JOIN sys.columns c28 ON c28.[object_id] = r.[rkeyid] AND c28.[column_id] = r.[rkey12]

LEFT OUTER JOIN sys.columns c29 ON c29.[object_id] = r.[rkeyid] AND c29.[column_id] = r.[rkey13]

LEFT OUTER JOIN sys.columns c30 ON c30.[object_id] = r.[rkeyid] AND c30.[column_id] = r.[rkey14]

LEFT OUTER JOIN sys.columns c31 ON c31.[object_id] = r.[rkeyid] AND c31.[column_id] = r.[rkey15]

LEFT OUTER JOIN sys.columns c32 ON c32.[object_id] = r.[rkeyid] AND c32.[column_id] = r.[rkey16]

INNER JOIN sys.tables t1

ON t1.[object_id] = r.[fkeyid]

AND t1.[type] = 'U'--Ensure we only look at user tables.

INNER JOIN sys.tables t2

ON t2.[object_id] = r.[rkeyid]

INNER JOIN sys.schemas s1

ON s1.[schema_id] = t1.[schema_id]

INNER JOIN sys.schemas s2

ON s2.[schema_id] = t2.[schema_id]

-----------------------------------------------------------------------------------------------------------------

--Generate CREATE INDEX statements.

SELECT 'PRINT ''Creating Indexes and unique constraints...''' + @crlf + @crlf

UNION ALL

SELECT 'CREATE ' + CASE i.[is_unique] WHEN 1 THEN 'UNIQUE ' ELSE '' END + CASE i.[type] WHEN 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END + 'INDEX [' + i.[name] + ']' + @crlf +

'ON [' + s.[name] + '].[' + t.[name] + ']' + @crlf +

'(' + @crlf +

ISNULL('[' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 1 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 2 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 3 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 4 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 5 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 6 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 7 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 8 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 9 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 10 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 11 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 12 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 13 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 14 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 15 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

ISNULL(', [' + INDEX_COL(OBJECT_NAME(i.[object_id]), i.[index_id], SUM(CASE WHEN ic.[key_ordinal] = 16 THEN ic.[index_column_id] ELSE NULL END)) + ']' + CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + @crlf, '') +

')' + @crlf +

COALESCE('INCLUDE (' +

(SELECT DISTINCT LTRIM(RTRIM(STUFF((SELECT ', ' + ii.[name]

FROM (

SELECT TOP 100 PERCENT ic2.[object_id], ic2.[index_id], ic2.[column_id], c2.[name]

FROM sys.index_columns ic2

INNER JOIN sys.columns c2

ON c2.[object_id] = ic2.[object_id]

AND c2.[column_id] = ic2.[column_id]

INNER JOIN sys.tables t2

ON t2.[object_id] = ic2.[object_id]

AND t2.[type] = 'U'

WHERE ic2.[is_included_column] = 1

AND ic2.[object_id] = i.[object_id]

AND ic2.[index_id] = i.[index_id]

ORDER BY ic2.[object_id], ic2.[index_id], ic2.[index_column_id]

) ii

FOR XML PATH('')),1,1,'')))

FROM (

SELECT TOP 100 PERCENT ic2.[object_id], ic2.[index_id], ic2.[column_id], c2.[name]

FROM sys.index_columns ic2

INNER JOIN sys.columns c2

ON c2.[object_id] = ic2.[object_id]

AND c2.[column_id] = ic2.[column_id]

INNER JOIN sys.tables t2

ON t2.[object_id] = ic2.[object_id]

AND t2.[type] = 'U'

WHERE ic2.[is_included_column] = 1

AND ic2.[object_id] = i.[object_id]

AND ic2.[index_id] = i.[index_id]

ORDER BY ic2.[object_id], ic2.[index_id], ic2.[index_column_id]

) AS ii2

) + ')' + @crlf, '') +

' WITH( ' +

'PAD_INDEX = ' + CASE WHEN i.[is_padded] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' STATISTICS_NORECOMPUTE = ' + CASE WHEN st.[no_recompute] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' SORT_IN_TEMPDB = OFF,' +

' IGNORE_DUP_KEY = ' + CASE WHEN i.[ignore_dup_key] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' DROP_EXISTING = OFF,' +

' ONLINE = OFF,' +

' ALLOW_ROW_LOCKS = ' + CASE WHEN i.[allow_row_locks] = 1 THEN ' ON' ELSE ' OFF' END + ',' +

' ALLOW_PAGE_LOCKS = ' + CASE WHEN i.[allow_page_locks] = 1 THEN ' ON' ELSE ' OFF' END +

') ON [' + f.[name] + ']' + @crlf + 'GO'

FROM sys.indexes i

INNER JOIN sys.index_columns ic

ON ic.[object_id] = i.[object_id]

AND ic.[index_id] = i.[index_id]

INNER JOIN sys.stats st

ON st.[name] = i.[name]

INNER JOIN sys.tables t

ON t.[object_id] = i.[object_id]

INNER JOIN sys.schemas s

ON s.[schema_id] = t.[schema_id]

INNER JOIN sys.filegroups f

ON f.[data_space_id] = i.[data_space_id]

WHERE i.[is_primary_key] = 0

AND i.[is_unique_constraint] = 0

AND t.[type] = 'U'--Ensure we only look at user tables.

GROUP BY s.[name], t.[name], i.[name], i.[type], ic.[is_descending_key], i.[is_unique], i.[is_padded], st.[no_recompute], i.[ignore_dup_key], i.[allow_row_locks], i.[allow_page_locks], f.[name], i.[object_id], i.[index_id]

-----------------------------------------------------------------------------------------------------------------

--Generate unique constraint statments.

SELECT 'PRINT ''Creating unique constraints...''' + @crlf + @crlf

UNION ALL

SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] ADD CONSTRAINT [' + i.[name] + '] UNIQUE ' + CASE i.[type] WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + @crlf +