Technical Article

Make string columns unicode compliant

,

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.

 

Apologies if any of the instructions seem a bit noddy - they were originally intended for colleagues who might not be the most fluent SQL developers and I might not have edited them enough!

 

LIMITATIONS

  1. This script will only work on SQL Server 2005 and above as it uses system catalog 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. 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 Heap, 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 MSUT ENSURE THAT YOU BACK UP THE DATABASE FIRST AND THEN VERIFY THAT YOU CAN SUCCESSFUULY 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 highly 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> 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.
  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 include any extra object types which prove to be a barrier to making column alterations. DO NOT ATTEMPT TO EDIT THIS SCRIPT 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.
/************************************************************************************************************************

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 <DBName>

--Declare and set global line break constant.
DECLARE @crlfCHAR(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 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.


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

--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_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 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_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 + ' 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_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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,613 reads