SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Steve Pettifer / Make string columns unicode compliant / Latest Posts

RE: Make string columns unicode compliant

Steve Pettifer — Thu, 19 May 2011 03:35:36 GMT

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:[ul][li]Database recovery model is changed to BULK_LOGGED at the start and reverts to its original value afterwards.[/li][li]If the database and/or log are set to no auto-growth and/or fixed size, this is changed to allow auto-growth and unlimited size, with these values reverting to their original state at the end.[/li][li]Handles multiple file groups to ensure replacement tables end up on the correct filegroup (indexes etc already had this).[/li][li]Adds include columns to indexes where they exist.[/li][/ul]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.[code="sql"]/************************************************************************************************************************Created By: Steve PettiferDate: 25/08/2010First 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 NOrollback 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, defaultconstraints, unique constraints and check constraints before altering the columns and then recreating all the droppedobjects. 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 werepreviously disabled (as will check constraints, althoguh if check constraints were previously enabled and trusted thena 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 theappropriate filegroups automatically.LIMITATIONS1) 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 thisdoes 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 DBCCCHECKCONSTRAINTS 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 wouldbe 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.USAGE1) Ensure that all users are disconnected from the database. However, do not put the database into single user mode as ifsomething goes wrong it can be difficult to get it out again, and if you back up a database in single user mode, it willrestore 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 FIRSTAND THEN VERIFY THAT YOU CAN SUCCESSFULLY RESTORE THAT BACKUP. Do not assume that just because you have performed a backup thatyou 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 theQuery 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 clickOK. 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 varydepending upon the size of the database, and the capability of the hardware but it may take anything up to 10 or 15 minutesor 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 WITHSQL 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 @crlf CHAR(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 INTDECLARE @logGrowth INTDECLARE @dbMaxSize BIGINTDECLARE @logMaxSize BIGINTDECLARE @dbFileName SYSNAMEDECLARE @logFileName SYSNAMEDECLARE @dbId INTSET @dbGrowth = 0SET @logGrowth = 0SET @dbMaxSize = 0SET @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] = 0SELECT 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] = 1IF @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 ALLSELECT '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 statementsSELECT 'PRINT ''Dropping indexes and unique constraints...''' + @crlf + @crlfUNION ALLSELECT 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' ENDFROM sys.indexes iINNER 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 + @crlfUNION ALLSELECT 'ALTER TABLE [' + s.[name] + '].[' + OBJECT_NAME(f.parent_object_id) + '] DROP CONSTRAINT [' + f.[name] + '] ' + @crlf + 'GO' FROM sys.foreign_keys fINNER 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 + @crlfUNION ALLSELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + i.[name] + ']' + @crlf + 'GO'FROM sys.indexes iINNER 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 + @crlfUNION ALLSELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + d.[name] + ']' + ' ' + @crlf + 'GO'FROM sys.default_constraints dINNER 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 + @crlfUNION ALLSELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + co.[name] + ']' + @crlf + 'GO'FROM sys.check_constraints coINNER 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 cINNER 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 INTDECLARE @tableMax INTDECLARE @columnCount INTDECLARE @columnMax INTSELECT @tableCount = MIN(tableId), @tableMax = MAX(tableId) FROM @tablesDECLARE @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 + @crlfUNION ALLSELECT '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 ENDFROM @tables tINNER 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 + @crlfUNION ALLSELECT 'DROP TABLE [' + t.[schemaName] + '].[' + t.[tableName] + '];' + @crlf + 'EXEC sp_rename ''' + t.[schemaName] + '.' + t.[tableName] + '_new'', ''' + t.[tableName] + ''';' + @crlf + @crlfFROM @tables t-------------------------------------------------------------------------------------------------------------------Generate default constraint statements.SELECT 'PRINT ''Creating default constraints...''' + @crlf + @crlfUNION ALLSELECT 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] ADD CONSTRAINT [' + d.[name] + '] DEFAULT ' + d.[definition] + ' FOR [' + c.[name] + ']' + ' ' + @crlf + 'GO'FROM sys.default_constraints dINNER 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 + @crlfUNION ALLSELECT '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 iINNER 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 + @crlfUNION ALLSELECT '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 '' ENDFROM sys.foreign_keys fkINNER 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 + @crlfUNION ALLSELECT '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 iINNER 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 + @crlfUNION ALLSELECT '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 iINNER 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 + @crlfUNION ALLSELECT '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 '' ENDFROM sys.check_constraints coINNER 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 ALLSELECT '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 dbfWHERE 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 ALLSELECT '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[/code]

RE: Make string columns unicode compliant

Nadrek — Wed, 01 Sep 2010 09:22:51 GMT

Feel free to use the alterations I posts on Page 1; they properly handle trust/untrust and enabled/disabled, using the best of your code from Check and FK constraints.

RE: Make string columns unicode compliant

Steve Pettifer — Wed, 01 Sep 2010 09:00:52 GMT

Hmmm...clearly some brain melt going on from my end. Serves me right for checking this on my phone whilst out drinking! I'm going to amend the script to grab include columns (I though about doing them before, as I recall, but since the DB I was working on had none I didn't bother to save time, but it'll annoy me if I don't!) and yes, the constraints aren't coming up disabled/untrusted. Again, omission on my part. Oh well, never said it was complete or perfect!! Neither are insurmountable things for a competent develoepr to add, but I'll re-post the script once II have the time to make my amendments.

RE: Make string columns unicode compliant

Steve Pettifer — Mon, 30 Aug 2010 17:13:17 GMT

Cool, thanks for the info. Will test the same when I get a chance and confirm.

RE: Make string columns unicode compliant

Nadrek — Mon, 30 Aug 2010 16:30:29 GMT

Hmmm; I'm testing on SQL 2008 R2 Enterprise Edition.

RE: Make string columns unicode compliant

Steve Pettifer — Mon, 30 Aug 2010 16:22:54 GMT

I hate to use the old developers refrain but...it worked on my db. There were several constraints which had been disabled and they were again afterwards. I will re check this but I'm pretty certain it does this already.

RE: Make string columns unicode compliant

Steve Pettifer — Mon, 30 Aug 2010 16:22:53 GMT

RE: Make string columns unicode compliant

Nadrek — Mon, 30 Aug 2010 15:52:24 GMT

It doesn't actually disable constraints that were previously disabled; run a quick test and see for yourself.

RE: Make string columns unicode compliant

Steve Pettifer — Mon, 30 Aug 2010 15:41:09 GMT

It already does pay attention to trusted/untrusted and enabled/disabled status - and generates all columns without the need for loops/cursor or temp tables.Cheers

RE: Make string columns unicode compliant

Nadrek — Mon, 30 Aug 2010 13:39:39 GMT

Here's a variant for FK's and CK's that fully mirrors the Trusted/Untrusted, Enabled/Disabled states (it also generates more than one column at once, suitable for greater automation).If you're going to set things back "the same as they were", then disabled constraints should stay disabled until your constraint maintenance handles them.[code]IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#CheckSQL]'))DROP TABLE [dbo].[#CheckSQL]SELECT s.[name] AS OriginalSchemaName ,t.[name] AS OriginalTableName ,co.[name] AS OrginalConstraintName ,'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + co.[name] + '] ' AS DropCheckSQL ,'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] + ') ' AS CreateCheckSQL ,CASE WHEN co.[is_disabled] = 1 THEN 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] NOCHECK CONSTRAINT [' + co.[name] + ']' ELSE NULL END AS NocheckCheckSQLINTO #CheckSQLFROM sys.check_constraints coINNER 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.ORDER BY t.[name], co.[name]SELECT * FROM #CheckSQL ORDER BY OriginalTableName, OrginalConstraintNameIF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ForeignSQL]'))DROP TABLE [dbo].[#ForeignSQL]SELECT s1.[name] AS OriginalSchemaName ,stParent.[name] AS OriginalTableName ,fk.[name] AS OriginalConstraintName ,'ALTER TABLE [' + s1.[name] + '].[' + stParent.[name] + '] DROP CONSTRAINT [' + fk.[name] + '] ' AS DropForeignSQL ,'ALTER TABLE [' + s1.[name] + '].[' + stParent.[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] + '].[' + stReferences.[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 + ') ' AS CreateForeignSQL ,CASE WHEN fk.[is_disabled] = 1 THEN 'ALTER TABLE [' + s1.[name] + '].[' + stParent.[name] + '] NOCHECK CONSTRAINT [' + fk.[name] + ']' ELSE NULL END AS NocheckForeignSQLINTO #ForeignSQLFROM sys.foreign_keys fkINNER JOIN sys.sysreferences rON r.[constid] = fk.[object_id]INNER JOIN sys.tables stParentON stParent.[object_id] = r.[fkeyid] AND stParent.[type] = 'U' --Ensure we only look at user tables.INNER JOIN sys.tables stReferencesON stReferences.[object_id] = r.[rkeyid]INNER JOIN sys.schemas s1ON s1.[schema_id] = stParent.[schema_id]INNER JOIN sys.schemas s2ON s2.[schema_id] = stReferences.[schema_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]SELECT * FROM #ForeignSQL ORDER BY OriginalTableName, OriginalConstraintName[/code]

RE: Make string columns unicode compliant

terrance.steadman — Mon, 30 Aug 2010 10:28:05 GMT

Greetings,May as well let you know: MSUT -> MUST:-DHave a good day.

RE: Make string columns unicode compliant

Steve Pettifer — Mon, 30 Aug 2010 09:50:52 GMT

Good spot! I had fully intended to do include columns and forgot. That's something rather useful so I probably will add support for those. This was something of a rush job after a director sprung a demo to a client on us with short notice so there may be one or two errors!

RE: Make string columns unicode compliant

Nadrek — Mon, 30 Aug 2010 08:46:57 GMT

Thank you for the useful and very compact script. While I understand not supporting SQL 2008 features like filtered indexes and data compression, you're also missing support for include columns.See [url=http://www.sqlservercentral.com/scripts/Indexing/70737/]http://www.sqlservercentral.com/scripts/Indexing/70737/[/url] for references, though your code is much more streamlined.Also, in your legal disclaimer, wrods->words.

RE: Make string columns unicode compliant

Steve Pettifer — Mon, 30 Aug 2010 06:45:26 GMT

I fully agree - I was going to add support for changing data types in stored procedures and UDFs too, but since the database I wrote this for used neither I couldn't justify the time at work!! However, I already have scripts for re-assembling stored procedures so I might well add that support sometime soon when work is a little less chaotic. Mind you, considering that it would involve a lot of string manipulation I'd err on the side of utilising the CLR as it is more efficient at that sort of work. It's something I might have a look at sometime soon, but in the meantime there's quite a few scripts flying about to get hold of stored procedures and functions from a database so it could be an interesting project for someone! :-D

RE: Make string columns unicode compliant

terrance.steadman — Mon, 30 Aug 2010 06:09:55 GMT

Greetings,Thank you for posting this script to help standardize a database. I think it may come in handy for updating the fields in ours. It would be nice too if the script could also parse through the user created functions and stored procedures within a database and find the non-unicode compliant characters in them too.

Make string columns unicode compliant

Steve Pettifer — Sat, 28 Aug 2010 15:12:37 GMT

Comments posted to this topic are about the item [B]Make string columns unicode compliant[/B]