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:
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
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
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 +
' (' + @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 + ' 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_unique_constraint] = 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]
-----------------------------------------------------------------------------------------------------------------
--Generate check constraint statments.
SELECT 'PRINT ''Creating check constraints...''' + @crlf + @crlf
UNION ALL
SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] WITH ' + CASE WHEN co.[is_disabled] = 0 AND co.[is_not_trusted] = 0 THEN 'CHECK' ELSE 'NOCHECK' END + ' ADD CONSTRAINT [' + co.[name] + '] CHECK (' + co.[definition] + ')' + @crlf + 'GO' +
CASE WHEN (co.[is_disabled] = 0 AND co.[is_not_trusted] = 0) THEN @crlf + 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] CHECK CONSTRAINT [' + co.[name] + ']' + @crlf + 'GO' ELSE '' END +
CASE WHEN co.[is_disabled] = 1 THEN @crlf + 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] NOCHECK CONSTRAINT [' + co.[name] + ']' + @crlf + 'GO' ELSE '' END
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]
-----------------------------------------------------------------------------------------------------------------
--Generate shrink logfile commands.
--Have you created a backup of the original database? YOU MUST ENSURE THAT YOU BACK UP THE DATABASE FIRST
--AND THEN VERIFY THAT YOU CAN SUCCESSFULLY RESTORE THAT BACKUP. These commands are the kind that are used only
--in extremis and if they go wrong and you have no backup you are in trouble.
--Belt and braces approach: Use TRUNCATEONLY option with SHRINKDATABASE then perform a CHRINKFILE on the log, just in case.
--We use TRUNCATEONLY instead of specifying a percentage of free soace to be left for performance reasons.
SELECT 'PRINT ''Shrinking log file...'''
UNION ALL
SELECT 'ALTER DATABASE [' + DB_NAME() + '] SET RECOVERY SIMPLE WITH NO_WAIT;' + @crlf +
'DBCC SHRINKDATABASE ([' + DB_NAME() + '], TRUNCATEONLY);' + @crlf +
'DBCC SHRINKFILE(' + dbf.[name] + ', 1);' + @crlf +
'ALTER DATABASE [' + DB_NAME() + '] SET RECOVERY FULL WITH NO_WAIT;' + @crlf + 'GO'
FROM sys.database_files dbf
WHERE dbf.[type] = 1--Log file.
-----------------------------------------------------------------------------------------------------------------
SELECT 'USE [master]' + @crlf + 'GO'
-----------------------------------------------------------------------------------------------------------------
--Reset growth and Max size if they were previously off/limited for DB and log files as required.
IF @dbGrowth = 0 OR @dbMaxSize <> -1
BEGIN
SELECT 'PRINT ''Setting database to to original file growth and max size values...'''
UNION ALL
SELECT 'ALTER DATABASE [' + DB_NAME() + '] MODIFY FILE (NAME = ' + @dbFileName + ', FILEGROWTH = ' + CAST(@dbGrowth AS NVARCHAR(10)) + ', MAXSIZE = ' + CASE WHEN @dbMaxSize = -1 THEN 'UNLIMITED' ELSE CAST(@dbMaxSize AS NVARCHAR(10)) END + ');'
END
IF @logGrowth = 0 OR @logMaxSize <> -1
BEGIN
SELECT 'PRINT ''Setting log to original file growth and max size values...'''
UNION ALL
SELECT 'ALTER DATABASE [' + DB_NAME() + '] MODIFY FILE (NAME = ' + @logFileName + ', FILEGROWTH = ' + CAST(@logGrowth AS NVARCHAR(10)) + ', MAXSIZE = ' + CASE WHEN @logMaxSize = -1 THEN 'UNLIMITED' ELSE CAST(@logMaxSize AS NVARCHAR(10)) END + ');'
END
-----------------------------------------------------------------------------------------------------------------
--Set recovery model back to original value.
SELECT 'PRINT ''Setting recovery model to ' + @dbRecoveryModel + '...'''
UNION ALL
SELECT 'ALTER DATABASE [' + DB_NAME() + '] SET RECOVERY ' + @dbRecoveryModel + ' WITH NO_WAIT;' + @crlf + 'GO'
-----------------------------------------------------------------------------------------------------------------
SELECT 'PRINT ''Script complete. Please check that SQL Server reported no errors.'''
-----------------------------------------------------------------------------------------------------------------
--Re-enable warnings and null yields null.
SET ANSI_WARNINGS ON