Embedded Files
Applicability:
Applicability:
SQL Server 2000: Not Tested
SQL Server 2005: Not Tested
SQL Server 2008: Not Tested
SQL Server 2008R2: Not Tested
SQL Server 2012: Not Tested
Credits:
Credits:
Author: Steve Pettifer
Date: 25 Aug 2010
Description
Description
With the advent of more databases that need to contain data with international content, and the increase in data interchange between the lesser known DBMSs (Oracle, DB2) and SQL Server, the need to convert a database from a specific code page/character set to UNICODE will become more common.
SQL Server allows column data types to be changed 'on-the-fly', but all constraints and defaults must be removed before and recreated after the change. This can be a mammoth undertaking
This script doesn't actually perform the changes, but dynamically generates an ordered set of commands to allow you to perform the task.
Note that the status of this code in this tip is marked as yellow (untested) for all SQL versions as I have yet to make use of it, but a read through of the results when run against AdventureWorksDW seems to indicate it works well. However, there is no substitute for testing, especially with a change of this magnitude. Pay special attention to checking that number of constraints/defaults dropped is matched by the number created
Code
Code
/************************************************************************************************************************
Created By: Steve Pettifer
Date: 25/08/2010
This script may be freely distributed and modified, provided a credit is given to the original author (Steve Pettifer).
You may use it for both commercial and non-commercial purposes, however the use of this script for personal commercial
or financial gain is strictly forbidden. In other wrods, feel free to use it at work or, if you are a contractor, as
part of your toolkit, but you may NOT sell this script. Provided to SQLServerCentral.com 26/08/2010.
************************************************************************************************************************/
SET NOCOUNT ON
USE AdventureWorksDW
--Declare and set global line break constant.
DECLARE @crlf CHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
--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
-----------------------------------------------------------------------------------------------------------------
SELECT 'USE ['+ DB_NAME() +']'
-----------------------------------------------------------------------------------------------------------------
--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 DROPP 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.
-----------------------------------------------------------------------------------------------------------------
--Generate ALTER COLUMN statments.
--NVARCHAR has a max width of 4000 unless MAX is specified which is 2^30-1 bytes. This is much more efficient than the old ntext type as
--although it can still hold huge amounts of data, the width is variable and therefore if only 5 bytes of data are in the field then only
--5 bytes of storage are used UNLESS the data is greater than 8000 bytes and then it is stored in the LOB structure with a pointer stored in the table structure.
--
--However - when changing a column from TEXT, NTEXT, VARCHAR or CHAR to NVARCHAR(MAX) we need to run an update on the column (setting it equal to itself)
--immediately afterwards so that SQL will move anything under 4000 bytes out of the LOB structure and into the table structure for maximum performance.
SELECT 'PRINT ''Altering column data types...''' + @crlf + @crlf
UNION ALL
SELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] ALTER COLUMN [' + c.[name] + '] NVARCHAR(' +
CASE
WHEN c.[system_type_id] = 35 OR c.[system_type_id] = 99 OR (c.[system_type_id] = 167 AND c.[max_length] > 4000) OR (c.[system_type_id] = 175 AND c.[max_length] > 4000) THEN
--Original data type is TEXT, NTEXT or CHAR or VARCHAR with a width greater than 4000 (maximum width for normal NVARCHAR column)
'MAX'
ELSE
CAST(c.[max_length] AS NVARCHAR(4))
END +
') ' +
CASE
WHEN c.[is_nullable] = 1 THEN
'NULL '
ELSE
'NOT NULL '
END +
@crlf + 'GO' + @crlf +
CASE
WHEN c.[system_type_id] = 35 OR c.[system_type_id] = 99 OR (c.[system_type_id] = 167 AND c.[max_length] > 4000) OR (c.[system_type_id] = 175 AND c.[max_length] > 4000) THEN
--New column type is NVARCHAR(MAX) so update column to ensure maximum performance optimisation.
' UPDATE [' + s.[name] + '].[' + t.[name] + '] SET [' + c.[name] + '] = [' + c.[name] + '] ' + @crlf + 'GO'
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]
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.
-----------------------------------------------------------------------------------------------------------------
--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], 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_ddescending_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 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'
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_iid] = 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 iic.[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 + ',' +
' 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_deescending_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] = 0
AND i.[is_unique] = 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 NOCHECK 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
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.
SELECT 'PRINT ''Shrinking log file...'''
UNION ALL
SELECT 'DBCC SHRINKFILE(' + dbf.[name] + ', 1)' + @crlf +
'BACKUP LOG [' + DB_NAME() + '] WITH TRUNCATE_ONLY' + @crlf +
'DBCC SHRINKFILE(' + dbf.[name] + ', 1)' +@crlf + 'GO'
FROM sys.database_files dbf
WHERE dbf.[type] = 1 --Log file.
-----------------------------------------------------------------------------------------------------------------
SELECT 'PRINT ''Script complete. Please check that SQL Server reported no errors.'''
-----------------------------------------------------------------------------------------------------------------
--Re-enable warnings.
SET ANSI_WARNINGS ON
Page updated
Google Sites
Report abuse