Change Database Collation

  • ArloUSA

    SSC Enthusiast

    Points: 127

    Comments posted to this topic are about the item Change Database Collation

  • Coypu

    SSC Rookie

    Points: 32

    Great script, saved me hours, thank you 🙂

    Only issue I found was it dropped ON DELETE CASCADE.

  • lasma

    Grasshopper

    Points: 22

    Great Script but, it works only for objects created in the DBO schema.

  • M. Gligorić

    Newbie

    Points: 9

    Great script, thanks.

    I run into two issues:

    • For ntext columns generated command was:

      ALTER TABLE <table_name> ALTER COLUMN <column_name> ntext(1073741823) COLLATE <collation_name> NULL

      instead of:

      ALTER TABLE <table_name> ALTER COLUMN <column_name> ntext COLLATE <collation_name> NULL

    • When there is a view dependant on other view(s), parent views should be created before child view, which is not the case.
  • scratt

    Grasshopper

    Points: 11

    Hello all,

    I've modified Arlo's SP for a data migration project I am working on in SQL Server 2014. I've added SQL Server 2014 to the list of supported versions and added the appropriate code so that different schema will be included in all steps.

    The problem I am running into seems to be related to the order in which objects are dropped. My (limited) understanding is that a PK can't be dropped if there is a FK that references it. So the dependency order should be to drop all FKs, then all PKs. I get an error when running the SP:

    Msg 3725, Level 16, State 0, Line 3

    The constraint 'pk_t_dbms_sql_cursor' is being referenced by table 't_dbms_sql_recordset', foreign key constraint 'FK_t_dbms_sql_recordset_t_dbms_sql_cursor'.

    Msg 3727, Level 16, State 0, Line 3

    Could not drop constraint. See previous errors.

    I don't know if I have the skill to modify the SP. Anyone willing to give me a hand?

    I was also thinking about making the database collation change a parameter, as I don't need that section. That's pretty easy to do, but I have just commented out the lines I don't want for now.

    Thanks,

    Matthew

    -- Name: sp_ChangeDatabaseCollation

    -- Type: Stored Procedure

    -- Version: 1.0

    -- Date: 9/30/2014

    -- Author: Arlo Fuller, with credit to:

    -- Raymund Macaalay - http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

    -- Adam Machanic - http://sqlblog.com/blogs/adam_machanic/default.aspx for the ScriptCreateTableKeys and ScriptDropTableKeys code

    -- Revisions: Matthew Garchow (6/22/2015) - Added schema references so that the SP will work with multiple schemas

    -- Purpose: Change the collation for an entire SQL Server database, compatible with SQL Server 2005 onwards

    -- Parameters: (1) DBName - NVARCHAR(255) - the name of the database to change the collation of - MANDATORY

    -- (2) NewCollation - NVARCHAR(255) - the name of the new collation to apply to the database - MANDATORY

    -- (3) JustPrintStatements - BIT (default 1 if not provided) - If JustPrintStatements = 1 then the SP returns two resultsets (drop statements and then create statements)

    -- If JustPrintStatements = 0 then the SP actually executes the generated SQL and applies the schema / collation changes

    -- (4) DebugPrintSQL - BIT (default 0 if not provided) - If DebugPrintSQL = 0 then nothing is output to the Messages tab in SSMS or the output window in osql

    -- If DebugPrintSQL = 1 then the statements being generated / run are output to the Messages tab in SSMS

    -- Description: This stored procedure (created in the master database by default) automates the process of changing the collation of a SQL Server database. It takes into account the impact of

    -- dependent objects that prevent collation from being changed at the database level e.g. foreign key constraints, default constraints, indexes etc. The script writes out a series of

    -- SQL DDL statements (in sequence order) to do the following:

    -- (1) Drop Primary keys and foreign keys

    -- (2) Drop Default constraints

    -- (3) Drop Indexes

    -- (4) Drop Check constraints

    -- (5) Drop Computed columns

    -- (6) Issues ALTER COLUMN statements for any (N)CHAR, (N)VARCHAR and (N)TEXT columns with the new collation.

    -- (7) Drops UDF's

    -- (8) Drops views

    -- (9) Set the target database into SINGLE_USER mode - THIS WILL DROP ALL EXISTING CONNECTIONS TO THE DATABASE AND ANY UNCOMMITTED TRANSACTIONS WILL BE LOST

    -- (10) Changes the database collation at the database level to the new collation

    -- (11) Set the target database back to MULTI_USER

    -- (12) Recreate Primary keys and foreign keys

    -- (13) Re-add Default constraints

    -- (14) Re-add Indexes

    -- (15) Re-add Check constraints

    -- (16) Re-add Computed columns

    -- (17) Re-add UDF's

    -- (18) Re-add views

    -- (19) Reassign permissions to objects that were dropped and recreated

    --

    -- This stored procedure is provided "as is". Use it at your own risk and test it out first of all on a test system / database. Always take a backup first! Enjoy!!

    USE master

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ChangeDatabaseCollation') DROP PROCEDURE sp_ChangeDatabaseCollation

    GO

    CREATE PROCEDURE sp_ChangeDatabaseCollation (@DBName NVARCHAR(255), @NewCollation NVARCHAR(255), @JustPrintStatements BIT = 1, @DebugPrintSQL BIT = 0) AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @CrLf NVARCHAR(10),

    @sql NVARCHAR(MAX),

    @bigsql NVARCHAR(MAX)

    DECLARE @DuplicateUsers TABLE (UserName NVARCHAR(255))

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

    IF @NewCollation NOT IN (SELECT name FROM sys.fn_HelpCollations())

    BEGIN

    SET @sql = 'ERROR: cannot proceed with collation change. The collation name ' + CHAR(39) + @NewCollation + CHAR(39) + ' is invalid.'

    RAISERROR(@sql, 11, 1)

    RETURN 1

    END

    -- If we are changing to a case-insensitive collation, then we need to check for duplicate user names first as any duplicate user names will prevent

    -- the change collation process to a case insensitive collation

    IF EXISTS (SELECT * FROM sys.fn_HelpCollations() WHERE description LIKE '%case-insensitive%' AND name = @NewCollation)

    BEGIN

    SET @sql = 'USE [' + @DBName + ']; SELECT LOWER(name) AS username FROM sys.sysusers GROUP BY LOWER(name) HAVING SUM(1) > 1'

    INSERT INTO @DuplicateUsers

    EXEC sp_executesql @sql

    IF EXISTS (SELECT UserName FROM @DuplicateUsers)

    BEGIN

    SET @sql = 'ERROR: cannot proceed with collation change as there are duplicate users in the Security section of database ' + @DBName + '. See the Results tab for details. Please resolve the duplicates before proceeding.'

    RAISERROR(@sql, 11, 1)

    SELECT UserName FROM @DuplicateUsers

    RETURN 1

    END

    END

    -- First drop (if already exists) and create a stored procedure to drop PK's and FK's

    -- Credit for this stored procedure goes to Adam Machanic - http://sqlblog.com/blogs/adam_machanic/default.aspx

    SET @sql = REPLACE('IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptDropTableKeys'') DROP PROCEDURE ScriptDropTableKeys', CHAR(39), CHAR(39)+CHAR(39))

    SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + @sql + '''';

    EXEC (@bigsql)

    SET @sql =

    'CREATE PROCEDURE ScriptDropTableKeys

    @table_name SYSNAME

    AS

    SET NOCOUNT ON

    --Note: Disabled keys and constraints are ignored

    --TODO: Drop and re-create referencing XML indexes, FTS catalogs

    DECLARE @crlf CHAR(2)

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

    DECLARE @version CHAR(4)

    SET @version = SUBSTRING(@@VERSION, LEN(''Microsoft SQL Server'') + 2, 4)

    DECLARE @object_id INT

    SET @object_id = OBJECT_ID(@table_name)

    DECLARE @sql NVARCHAR(MAX)

    IF @version NOT IN (''2005'', ''2008'', ''2012'', ''2014'')

    BEGIN

    RAISERROR(''This script only supports SQL Server 2005, 2008, 2012 and 2014'', 16, 1)

    RETURN

    END

    SELECT

    ''ALTER TABLE '' +

    QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + ''.'' +

    QUOTENAME(OBJECT_NAME(parent_object_id)) + @crlf +

    ''DROP CONSTRAINT '' + QUOTENAME(name) + '';'' +

    @crlf + @crlf COLLATE database_default AS [-- Drop Referencing FKs]

    FROM sys.foreign_keys

    WHERE

    referenced_object_id = @object_id

    AND is_disabled = 0

    ORDER BY

    key_index_id DESC

    SET @sql = '''' +

    ''SELECT '' +

    ''statement AS [-- Drop Candidate Keys] '' +

    ''FROM '' +

    ''( '' +

    ''SELECT '' +

    ''CASE '' +

    ''WHEN 1 IN (i.is_unique_constraint, i.is_primary_key) THEN '' +

    ''''''ALTER TABLE '''' + '' +

    ''QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''''.'''' + '' +

    ''QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + '' +

    ''''''DROP CONSTRAINT '''' + QUOTENAME(i.name) + '''';'''' + '' +

    ''@crlf + @crlf COLLATE database_default '' +

    ''ELSE '' +

    ''''''DROP INDEX '''' + QUOTENAME(i.name) + @crlf + '' +

    ''''''ON '''' + '' +

    ''QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + ''''.'''' + '' +

    ''QUOTENAME(OBJECT_NAME(object_id)) + '''';'''' + '' +

    ''@crlf + @crlf COLLATE database_default '' +

    ''END AS statement, '' +

    ''i.index_id '' +

    ''FROM sys.indexes AS i '' +

    ''WHERE '' +

    ''i.object_id = @object_id '' +

    ''AND i.is_unique = 1 '' +

    --filtered and hypothetical indexes cannot be candidate keys

    CASE @version

    WHEN ''2008'' THEN ''AND i.has_filter = 0 ''

    ELSE ''''

    END +

    ''AND i.is_hypothetical = 0 '' +

    ''AND i.is_disabled = 0 '' +

    '') AS x '' +

    ''ORDER BY '' +

    ''index_id DESC ''

    EXEC sp_executesql @sql, N''@object_id INT, @crlf CHAR(2)'', @object_id, @crlf'

    SET @bigsql = 'USE [' + @DBName + ']; EXEC (''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''')'

    EXEC (@bigsql)

    -- Next, drop (if already exists) and create a stored procedure to recreate PK's and FK's

    -- Credit for this stored procedure goes to Adam Machanic - http://sqlblog.com/blogs/adam_machanic/default.aspx

    SET @sql = 'IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptCreateTableKeys'') DROP PROCEDURE ScriptCreateTableKeys'

    SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''''

    EXEC (@bigsql)

    SET @sql = CONVERT(NVARCHAR(MAX),

    'CREATE PROCEDURE ScriptCreateTableKeys

    @table_name SYSNAME

    AS

    BEGIN

    SET NOCOUNT ON

    --Note: Disabled keys and constraints are ignored

    --TODO: Drop and re-create referencing XML indexes, FTS catalogs

    DECLARE @crlf CHAR(2)

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

    DECLARE @version CHAR(4)

    SET @version = SUBSTRING(@@VERSION, LEN(''Microsoft SQL Server'') + 2, 4)

    DECLARE @object_id INT

    SET @object_id = OBJECT_ID(@table_name)

    DECLARE @sql NVARCHAR(MAX)

    IF @version NOT IN (''2005'', ''2008'', ''2012'', ''2014'')

    BEGIN

    RAISERROR(''This script only supports SQL Server 2005, 2008, 2012 and 2014'', 16, 1)

    RETURN

    END

    SET @sql = '''' +

    ''SELECT '' +

    ''CASE '' +

    ''WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN '' +

    ''''''ALTER TABLE '''' + '' +

    ''QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''''.'''' + '' +

    ''QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + '' +

    ''''''ADD '''' + '' +

    ''CASE k.is_system_named '' +

    ''WHEN 0 THEN ''''CONSTRAINT '''' + QUOTENAME(k.name) + @crlf '' +

    ''ELSE '''''''' '' +

    ''END + '' +

    ''CASE k.type '' +

    ''WHEN ''''UQ'''' THEN ''''UNIQUE'''' '' +

    ''ELSE ''''PRIMARY KEY'''' '' +

    ''END + '''' '''' + '' +

    ''i.type_desc + @crlf + '' +

    ''kc.key_columns + @crlf '' +

    ''ELSE '' +

    ''''''CREATE UNIQUE '''' + i.type_desc + '''' INDEX '''' + '' +

    ''QUOTENAME(i.name) + @crlf + '' +

    ''''''ON '''' + '' +

    ''QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''''.'''' + '' +

    ''QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + '' +

    ''kc.key_columns + @crlf + '' +

    ''COALESCE '' +

    ''( '' +

    ''''''INCLUDE '''' + @crlf + '' +

    ''''''( '''' + @crlf + '' +

    ''STUFF '' +

    ''( '' +

    ''( '' +

    ''SELECT '' +

    ''( '' +

    ''SELECT '' +

    '''''','''' + @crlf + '''' '''' + QUOTENAME(c.name) AS [text()] '' +

    ''FROM sys.index_columns AS ic '' +

    ''JOIN sys.columns AS c ON '' +

    ''c.object_id = ic.object_id '' +

    ''AND c.column_id = ic.column_id '' +

    ''WHERE '' +

    ''ic.object_id = i.object_id '' +

    ''AND ic.index_id = i.index_id '' +

    ''AND ic.is_included_column = 1 '' +

    ''ORDER BY '' +

    ''ic.key_ordinal '' +

    ''FOR XML PATH(''''''''), TYPE '' +

    '').value(''''.'''', ''''VARCHAR(MAX)'''') '' +

    ''), '' +

    ''1, '' +

    ''3, '' +

    '''''''''' '' +

    '') + @crlf + '' +

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

    '''''''''' '' +

    '') '' +

    ''END + '' +

    ''''''WITH '''' + @crlf + '' +

    ''''''('''' + @crlf + '' +

    '''''' PAD_INDEX = '''' + '' +

    ''CASE CONVERT(VARCHAR, i.is_padded) '' +

    ''WHEN 1 THEN ''''ON'''' '' +

    ''ELSE ''''OFF'''' '' +

    ''END + '''','''' + @crlf + '' +

    ''CASE i.fill_factor '' +

    ''WHEN 0 THEN '''''''' '' +

    ''ELSE '' +

    '''''' FILLFACTOR = '''' + '' +

    ''CONVERT(VARCHAR, i.fill_factor) + '''','''' + @crlf '' +

    ''END + '' +

    '''''' IGNORE_DUP_KEY = '''' + '' +

    ''CASE CONVERT(VARCHAR, i.ignore_dup_key) '' +

    ''WHEN 1 THEN ''''ON'''' '' +

    ''ELSE ''''OFF'''' '' +

    ''END + '''','''' + @crlf + '' +

    '''''' ALLOW_ROW_LOCKS = '''' + '' +

    ''CASE CONVERT(VARCHAR, i.allow_row_locks) '' +

    ''WHEN 1 THEN ''''ON'''' '' +

    ''ELSE ''''OFF'''' '' +

    ''END + '''','''' + @crlf + '' +

    '''''' ALLOW_PAGE_LOCKS = '''' + '' +

    ''CASE CONVERT(VARCHAR, i.allow_page_locks) '' +

    ''WHEN 1 THEN ''''ON'''' '' +

    ''ELSE ''''OFF'''' '' +

    ''END + '' +

    CASE @version

    WHEN ''2005'' THEN ''''

    ELSE

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

    '''''' DATA_COMPRESSION = '''' + '' +

    ''( '' +

    ''SELECT '' +

    ''CASE '' +

    ''WHEN MIN(p.data_compression_desc) = MAX(p.data_compression_desc) THEN MAX(p.data_compression_desc) '' +

    ''ELSE ''''[PARTITIONS USE MULTIPLE COMPRESSION TYPES]'''' '' +

    ''END '' +

    ''FROM sys.partitions AS p '' +

    ''WHERE '' +

    ''p.object_id = i.object_id '' +

    ''AND p.index_id = i.index_id '' +

    '') ''

    END + ''+ @crlf + '' +

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

    ''''''ON '''' + ds.data_space + '''';'''' + '' +

    ''@crlf + @crlf COLLATE database_default AS [-- Create Candidate Keys] '' +

    ''FROM sys.indexes AS i '' +

    ''LEFT OUTER JOIN sys.key_constraints AS k ON '' +

    ''k.parent_object_id = i.object_id '' +

    ''AND k.unique_index_id = i.index_id '' +

    ''CROSS APPLY '' +

    ''( '' +

    ''SELECT '' +

    ''''''( '''' + @crlf + '' +

    ''STUFF '' +

    ''( '' +

    ''( '' +

    ''SELECT '' +

    ''( '' +

    ''SELECT '' +

    '''''','''' + @crlf + '''' '''' + QUOTENAME(c.name) AS [text()] '' +

    ''FROM sys.index_columns AS ic '' +

    ''JOIN sys.columns AS c ON '' +

    ''c.object_id = ic.object_id '' +

    ''AND c.column_id = ic.column_id '' +

    ''WHERE '' +

    ''ic.object_id = i.object_id '' +

    ''AND ic.index_id = i.index_id '' +

    ''AND ic.key_ordinal > 0 '' +

    ''ORDER BY '' +

    ''ic.key_ordinal '' +

    ''FOR XML PATH(''''''''), TYPE '' +

    '').value(''''.'''', ''''VARCHAR(MAX)'''') '' +

    ''), '' +

    ''1, '' +

    ''3, '' +

    '''''''''' '' +

    '') + @crlf + '' +

    '''''')'''' '' +

    '') AS kc (key_columns) '' +

    ''CROSS APPLY '' +

    ''( '' +

    ''SELECT '' +

    ''QUOTENAME(d.name) + '' +

    ''CASE d.type '' +

    ''WHEN ''''PS'''' THEN '' +

    ''+ '' +

    ''''''('''' + '' +

    ''( '' +

    ''SELECT '' +

    ''QUOTENAME(c.name) '' +

    ''FROM sys.index_columns AS ic '' +

    ''JOIN sys.columns AS c ON '' +

    ''c.object_id = ic.object_id '' +

    ''AND c.column_id = ic.column_id '' +

    ''WHERE '' +

    ''ic.object_id = i.object_id '' +

    ''AND ic.index_id = i.index_id '' +

    ''AND ic.partition_ordinal = 1 '' +

    '') + '' +

    '''''')'''' '' +

    ''ELSE '''''''' '' +

    ''END '' +

    ''FROM sys.data_spaces AS d '' +

    ''WHERE '' +

    ''d.data_space_id = i.data_space_id '' +

    '') AS ds (data_space) '' +

    ''WHERE '' +

    ''i.object_id = @object_id '' +

    ''AND i.is_unique = 1 '' +

    --filtered and hypothetical indexes cannot be candidate keys

    CASE @version

    WHEN ''2008'' THEN ''AND i.has_filter = 0 ''

    ELSE ''''

    END +

    ''AND i.is_hypothetical = 0 '' +

    ''AND i.is_disabled = 0 '' +

    ''ORDER BY '' +

    ''i.index_id ''

    EXEC sp_executesql

    @sql,

    N''@object_id INT, @crlf CHAR(2)'',

    @object_id, @crlf

    SELECT

    ''ALTER TABLE '' +

    QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + ''.'' +

    QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + @crlf +

    CASE fk.is_not_trusted

    WHEN 0 THEN ''WITH CHECK ''

    ELSE ''WITH NOCHECK ''

    END +

    ''ADD '' +

    CASE fk.is_system_named

    WHEN 0 THEN ''CONSTRAINT '' + QUOTENAME(name) + @crlf

    ELSE ''''

    END +

    ''FOREIGN KEY '' + @crlf +

    ''( '' + @crlf +

    STUFF

    (

    (

    SELECT

    (

    SELECT

    '','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()]

    FROM sys.foreign_key_columns AS fc

    JOIN sys.columns AS c ON

    c.object_id = fc.parent_object_id

    AND c.column_id = fc.parent_column_id

    WHERE

    fc.constraint_object_id = fk.object_id

    ORDER BY

    fc.constraint_column_id

    FOR XML PATH(''''), TYPE

    ).value(''.'', ''VARCHAR(MAX)'')

    ),

    1,

    3,

    ''''

    ) + @crlf +

    '') '' +

    ''REFERENCES '' +

    QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + ''.'' +

    QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + @crlf +

    ''( '' + @crlf +

    STUFF

    (

    (

    SELECT

    (

    SELECT

    '','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()]

    FROM sys.foreign_key_columns AS fc

    JOIN sys.columns AS c ON

    c.object_id = fc.referenced_object_id

    AND c.column_id = fc.referenced_column_id

    WHERE

    fc.constraint_object_id = fk.object_id

    ORDER BY

    fc.constraint_column_id

    FOR XML PATH(''''), TYPE

    ).value(''.'', ''VARCHAR(MAX)'')

    ),

    1,

    3,

    ''''

    ) + @crlf +

    '');'' +

    @crlf + @crlf COLLATE database_default AS [-- Create Referencing FKs]

    FROM sys.foreign_keys AS fk

    WHERE

    referenced_object_id = @object_id

    AND is_disabled = 0

    ORDER BY

    key_index_id' + @CrLf + 'END')

    SET @bigsql = 'USE [' + @DBName + ']; EXEC (''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''')'

    EXEC (@bigsql)

    -- Now, with the 2 new stored procedures in place (ScriptCreateTableKeys and ScriptDropTablekeys), iterate through all of the user tables in the target database

    -- and generate appropriate CREATE / DROP / ALTER TABLE statements to drop and recreate PK's and FK's.

    DECLARE @MyTableCursor AS CURSOR

    DECLARE @TableName nvarchar(255)

    DECLARE @CreateScripts TABLE (ID INT IDENTITY(1,1), SQLStatement NVARCHAR(MAX))

    DECLARE @DropScripts TABLE (ID INT IDENTITY(1,1), SQLStatement NVARCHAR(MAX))

    SET @sql = 'SELECT name FROM sys.tables WHERE [type] = ''U'' and name <> ''sysdiagrams'' ORDER BY name'

    SET @bigsql = 'USE [' + @DBName + ']; SET @cursor = CURSOR FORWARD_ONLY STATIC FOR ' + @sql + ' OPEN @cursor;'

    EXEC sys.sp_executesql @bigsql, N'@cursor cursor output', @MyTableCursor output

    FETCH NEXT FROM @MyTableCursor INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = 'USE [' + @DBName + ']; EXEC ScriptCreateTableKeys ''' + @TableName + ''''

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    SET @sql = 'USE [' + @DBName + ']; EXEC ScriptDropTableKeys ''' + @TableName + ''''

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    FETCH NEXT FROM @MyTableCursor INTO @TableName

    END

    CLOSE @MyTableCursor

    DEALLOCATE @MyTableCursor

    -- Now script out statements to recreate check constraints

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER TABLE ['' + OBJECT_SCHEMA_NAME(so.parent_obj) + ''].'' + QuoteName(OBJECT_NAME(so.parent_obj)) + '' ADD CONSTRAINT '' + QuoteName(CONSTRAINT_NAME) + '' CHECK '' + CHECK_CLAUSE

    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc

    INNER JOIN sys.sysobjects so ON cc.CONSTRAINT_NAME = so.[name]'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Now script out statements to drop check constraints

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''IF EXISTS (SELECT OBJECT_ID('' + QUOTENAME(CONSTRAINT_NAME, CHAR(39)) + '', '' + CHAR(39) + ''C'' + CHAR(39) + '') FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS) ALTER TABLE ['' + OBJECT_SCHEMA_NAME(so.parent_obj) + ''].'' + QuoteName(OBJECT_NAME(so.parent_obj)) + '' DROP CONSTRAINT '' + QuoteName(CONSTRAINT_NAME)

    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc

    INNER JOIN sys.sysobjects so ON cc.CONSTRAINT_NAME = so.[name]'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Now script out statements to recreate default constraints

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] ADD CONSTRAINT [''+DefCons.name+''] DEFAULT '' + DefCons.definition + '' FOR ['' + Col.name + '']''

    FROM sys.default_constraints AS DefCons

    INNER JOIN sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id

    INNER JOIN sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Now script out statements to drop check constraints

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] DROP CONSTRAINT [''+DefCons.name+'']''

    FROM sys.default_constraints AS DefCons

    INNER JOIN sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id

    INNER JOIN sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    SET @sql = 'USE [' + @DBName + '];

    -- Script out indexes completely, including both PK''s and regular indexes, each clustered or nonclustered.

    -- DOES NOT HANDLE COMPRESSION; that''s ok, since 2008 R2 RTM benchmarking shows it''s faster and results in smaller indexes to insert uncompressed and then compress later

    -- HARDCODES [dbo] schema (i.e. it doesn''t say [JohnDoe].

    , changing that to [dbo].

    -- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

    DECLARE

    @idxTableName SYSNAME,

    @idxTableID INT,

    @idxname SYSNAME,

    @idxid INT,

    @colCount INT,

    @colCountWithoutIncludedColumns INT ,

    @IxColumn SYSNAME,

    @IxFirstColumn BIT,

    @ColumnIDInTable INT,

    @ColumnIDInIndex INT,

    @IsIncludedColumn INT,

    @sIncludeCols NVARCHAR(MAX),

    @sIndexCols NVARCHAR(MAX),

    @sSQL NVARCHAR(MAX),

    @sDSQL NVARCHAR(MAX),

    @sParamSQL NVARCHAR(MAX),

    @sFilterSQL NVARCHAR(MAX),

    @location SYSNAME,

    @IndexCount INT,

    @CurrentIndex INT,

    @CurrentCol INT,

    @Name NVARCHAR(128),

    @IsPrimaryKey TINYINT,

    @Fillfactor INT,

    @FilterDefinition NVARCHAR(MAX),

    @IsClustered BIT -- used solely for putting information into the result table

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexSQL]''))

    DROP TABLE [dbo].[#IndexSQL]

    CREATE TABLE #IndexSQL

    ( TableName NVARCHAR(128) NOT NULL

    ,IndexName NVARCHAR(128) NOT NULL

    ,IsClustered BIT NOT NULL

    ,IsPrimaryKey BIT NOT NULL

    ,IndexCreateSQL NVARCHAR(max) NOT NULL

    ,IndexDropSQL NVARCHAR(MAX) NOT NULL

    )

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexListing]''))

    DROP TABLE [dbo].[#IndexListing]

    CREATE TABLE #IndexListing

    (

    [IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ObjectID] INT NOT NULL,

    [IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [IndexID] INT NOT NULL,

    [IsPrimaryKey] TINYINT NOT NULL,

    [FillFactor] INT,

    [FilterDefinition] NVARCHAR(MAX) NULL

    )

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#ColumnListing]''))

    DROP TABLE [dbo].[#ColumnListing]

    CREATE TABLE #ColumnListing

    (

    [ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [ColumnIDInTable] INT NOT NULL,

    [Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ColumnIDInIndex] INT NOT NULL,

    [IsIncludedColumn] BIT NULL

    )

    INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )

    SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.is_primary_key, si.fill_factor, si.filter_definition

    FROM sys.indexes si

    LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON si.name = tc.CONSTRAINT_NAME AND OBJECT_NAME(si.object_id) = tc.TABLE_NAME

    WHERE OBJECTPROPERTY(si.object_id, ''IsUserTable'') = 1 AND OBJECT_NAME(si.object_id) <> ''sysdiagrams''

    ORDER BY OBJECT_NAME(si.object_id), si.index_id

    SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

    WHILE @CurrentIndex <= @IndexCount

    BEGIN

    SELECT @idxTableName = [TableName],

    @idxTableID = [ObjectID],

    @idxname = [IndexName],

    @idxid = [IndexID],

    @IsPrimaryKey = [IsPrimaryKey],

    @FillFactor = [FILLFACTOR],

    @FilterDefinition = [FilterDefinition]

    FROM #IndexListing

    WHERE [IndexListingID] = @CurrentIndex

    -- So - it is either an index or a constraint

    -- Check if the index is unique

    IF (@IsPrimaryKey = 1)

    BEGIN

    SET @sSQL = ''ALTER TABLE ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + ''] ADD CONSTRAINT ['' + @idxname + ''] PRIMARY KEY ''

    SET @sDSQL = ''ALTER TABLE ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + ''] DROP CONSTRAINT ['' + @idxname + '']''

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 0)

    BEGIN

    SET @sSQL = @sSQL + ''NON''

    SET @IsClustered = 0

    END

    ELSE

    BEGIN

    SET @IsClustered = 1

    END

    SET @sSQL = @sSQL + ''CLUSTERED (''

    END

    ELSE

    BEGIN

    SET @sSQL = ''CREATE ''

    -- Check if the index is unique

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsUnique'') = 1)

    BEGIN

    SET @sSQL = @sSQL + ''UNIQUE ''

    END

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 1)

    BEGIN

    SET @sSQL = @sSQL + ''CLUSTERED ''

    SET @IsClustered = 1

    END

    ELSE

    BEGIN

    SET @IsClustered = 0

    END

    SELECT @sDSQL = ''DROP INDEX ['' + @idxname + ''] ON ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + '']''

    SELECT

    @sSQL = @sSQL + ''INDEX ['' + @idxname + ''] ON ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + ''] ('',

    @colCount = 0

    END

    -- Get the number of cols in the index

    SELECT @colCount = COUNT(*)

    FROM sys.index_columns ic

    INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id

    WHERE ic.object_id = @idxtableid AND index_id = @idxid --AND ic.is_included_column = 0

    SELECT @colCountWithoutIncludedColumns = COUNT(*)

    FROM sys.index_columns ic

    INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id

    WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

    -- Get the file group info

    SELECT @location = f.[name]

    FROM sys.indexes i

    INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

    INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]

    WHERE o.object_id = @idxTableID AND i.index_id = @idxid

    -- Get all columns of the index

    INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )

    SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column

    FROM sys.index_columns ic

    INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id

    WHERE ic.object_id = @idxTableID AND index_id = @idxid

    ORDER BY ic.index_column_id

    IF @@ROWCOUNT > 0

    BEGIN

    SELECT @CurrentCol = 1

    SELECT @IxFirstColumn = 1, @sIncludeCols = '''', @sIndexCols = ''''

    WHILE @CurrentCol <= @ColCount

    BEGIN

    SELECT @ColumnIDInTable = ColumnIDInTable,

    @Name = Name,

    @ColumnIDInIndex = ColumnIDInIndex,

    @IsIncludedColumn = IsIncludedColumn

    FROM #ColumnListing

    WHERE [ColumnListingID] = @CurrentCol

    IF @IsIncludedColumn = 0

    BEGIN

    SET @sIndexCols = @sIndexCols + ''['' + @Name + ''] ''

    -- Check the sort order of the index cols ????????

    IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,''IsDescending'')) = 0

    BEGIN

    SET @sIndexCols = @sIndexCols + '' ASC ''

    END

    ELSE

    BEGIN

    SET @sIndexCols = @sIndexCols + '' DESC ''

    END

    IF @CurrentCol < @colCountWithoutIncludedColumns

    BEGIN

    SET @sIndexCols = @sIndexCols + '', ''

    END

    END

    ELSE

    BEGIN

    -- Check for any include columns

    IF LEN(@sIncludeCols) > 0

    BEGIN

    SET @sIncludeCols = @sIncludeCols + '',''

    END

    SET @sIncludeCols = @sIncludeCols + ''['' + @Name + '']''

    END

    SET @CurrentCol = @CurrentCol + 1

    END

    TRUNCATE TABLE #ColumnListing

    --append to the result

    IF LEN(@sIncludeCols) > 0

    SET @sIndexCols = @sSQL + @sIndexCols + '') '' + '' INCLUDE ( '' + @sIncludeCols + '' ) ''

    ELSE

    SET @sIndexCols = @sSQL + @sIndexCols + '') ''

    -- Add filtering

    IF @FilterDefinition IS NOT NULL

    SET @sFilterSQL = '' WHERE '' + @FilterDefinition + '' ''

    ELSE

    SET @sFilterSQL = ''''

    -- Build the options

    SET @sParamSQL = ''WITH ( PAD_INDEX = ''

    IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPadIndex'') = 1

    SET @sParamSQL = @sParamSQL + ''ON,''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF,''

    SET @sParamSQL = @sParamSQL + '' ALLOW_PAGE_LOCKS = ''

    IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPageLockDisallowed'') = 0

    SET @sParamSQL = @sParamSQL + ''ON,''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF,''

    SET @sParamSQL = @sParamSQL + '' ALLOW_ROW_LOCKS = ''

    IF INDEXPROPERTY(@idxTableID, @idxname, ''IsRowLockDisallowed'') = 0

    SET @sParamSQL = @sParamSQL + ''ON,''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF,''

    SET @sParamSQL = @sParamSQL + '' STATISTICS_NORECOMPUTE = ''

    -- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsStatistics'') = 1)

    SET @sParamSQL = @sParamSQL + ''ON''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF''

    -- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2

    IF ISNULL( @FillFactor, 90 ) <> 0

    SET @sParamSQL = @sParamSQL + '' ,FILLFACTOR = '' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) -- + '')''

    /*

    IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn''t valid for PK''s

    BEGIN

    --SET @sParamSQL = @sParamSQL + '' ) ''

    END

    ELSE

    BEGIN

    SET @sParamSQL = @sParamSQL + '' ,DROP_EXISTING = ON ) ''

    END

    */

    SET @sSQL = ''IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = '' + CHAR(39) + @idxname + CHAR(39) + '' AND object_id = OBJECT_ID('' + CHAR(39) + OBJECT_SCHEMA_NAME(@idxTableID) + ''.'' + @idxTableName + CHAR(39) + '')) '' + @sIndexCols + @sFilterSQL + @sParamSQL

    -- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement

    SET @sSQL = @sSQL + '') ON ['' + @location + '']''

    --PRINT @sIndexCols + CHAR(13)

    INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, IndexDropSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @sDSQL)

    END

    SET @CurrentIndex = @CurrentIndex + 1

    END

    SELECT IndexCreateSQL FROM #IndexSQL

    DROP TABLE #IndexSQL'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    DECLARE @sqltemp NVARCHAR(MAX)

    SET @sqltemp = REPLACE(@sql, 'SELECT IndexCreateSQL FROM #IndexSQL', 'SELECT IndexDropSQL FROM #IndexSQL')

    -- Drop default constraints

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] DROP CONSTRAINT [''+DefCons.name+'']''

    FROM sys.default_constraints AS DefCons

    INNER JOIN sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id

    INNER JOIN sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    SET @sql = 'USE [' + @DBName + '];

    -- Script out indexes completely, including both PK''s and regular indexes, each clustered or nonclustered.

    -- DOES NOT HANDLE COMPRESSION; that''s ok, since 2008 R2 RTM benchmarking shows it''s faster and results in smaller indexes to insert uncompressed and then compress later

    -- HARDCODES [dbo] schema (i.e. it doesn''t say [JohnDoe].

    , changing that to [dbo].

    -- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

    DECLARE

    @idxTableName SYSNAME,

    @idxTableID INT,

    @idxname SYSNAME,

    @idxid INT,

    @colCount INT,

    @IxColumn SYSNAME,

    @IxFirstColumn BIT,

    @ColumnIDInTable INT,

    @ColumnIDInIndex INT,

    @IsIncludedColumn INT,

    @sIncludeCols NVARCHAR(MAX),

    @sIndexCols NVARCHAR(MAX),

    @sSQL NVARCHAR(MAX),

    @sDSQL NVARCHAR(MAX),

    @sParamSQL NVARCHAR(MAX),

    @sFilterSQL NVARCHAR(MAX),

    @location SYSNAME,

    @IndexCount INT,

    @CurrentIndex INT,

    @CurrentCol INT,

    @Name VARCHAR(128),

    @IsPrimaryKey TINYINT,

    @Fillfactor INT,

    @FilterDefinition NVARCHAR(MAX),

    @IsClustered BIT -- used solely for putting information into the result table

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexSQL]''))

    DROP TABLE [dbo].[#IndexSQL]

    CREATE TABLE #IndexSQL

    ( TableName NVARCHAR(128) NOT NULL

    ,IndexName NVARCHAR(128) NOT NULL

    ,IsClustered BIT NOT NULL

    ,IsPrimaryKey BIT NOT NULL

    ,IndexCreateSQL NVARCHAR(max) NOT NULL

    ,IndexDropSQL NVARCHAR(MAX) NOT NULL

    )

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexListing]''))

    DROP TABLE [dbo].[#IndexListing]

    CREATE TABLE #IndexListing

    (

    [IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ObjectID] INT NOT NULL,

    [IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [IndexID] INT NOT NULL,

    [IsPrimaryKey] TINYINT NOT NULL,

    [FillFactor] INT,

    [FilterDefinition] NVARCHAR(MAX) NULL

    )

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#ColumnListing]''))

    DROP TABLE [dbo].[#ColumnListing]

    CREATE TABLE #ColumnListing

    (

    [ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [ColumnIDInTable] INT NOT NULL,

    [Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ColumnIDInIndex] INT NOT NULL,

    [IsIncludedColumn] BIT NULL

    )

    INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )

    SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.is_primary_key, si.fill_factor, si.filter_definition

    FROM sys.indexes si

    LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON si.name = tc.CONSTRAINT_NAME AND OBJECT_NAME(si.object_id) = tc.TABLE_NAME

    WHERE OBJECTPROPERTY(si.object_id, ''IsUserTable'') = 1 AND OBJECT_NAME(si.object_id) <> ''sysdiagrams''

    ORDER BY OBJECT_NAME(si.object_id), si.index_id

    SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

    WHILE @CurrentIndex <= @IndexCount

    BEGIN

    SELECT @idxTableName = [TableName],

    @idxTableID = [ObjectID],

    @idxname = [IndexName],

    @idxid = [IndexID],

    @IsPrimaryKey = [IsPrimaryKey],

    @FillFactor = [FILLFACTOR],

    @FilterDefinition = [FilterDefinition]

    FROM #IndexListing

    WHERE [IndexListingID] = @CurrentIndex

    -- So - it is either an index or a constraint

    -- Check if the index is unique

    IF (@IsPrimaryKey = 1)

    BEGIN

    SET @sSQL = ''ALTER TABLE ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + ''] ADD CONSTRAINT ['' + @idxname + ''] PRIMARY KEY ''

    SET @sDSQL = ''IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = '' + CHAR(39) + @idxname + CHAR(39) + '' AND object_id = OBJECT_ID('' + CHAR(39) + OBJECT_SCHEMA_NAME(@idxTableID) + ''.'' + @idxTableName + CHAR(39) + '')) ALTER TABLE ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + ''] DROP CONSTRAINT ['' + @idxname + '']''

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 0)

    BEGIN

    SET @sSQL = @sSQL + ''NON''

    SET @IsClustered = 0

    END

    ELSE

    BEGIN

    SET @IsClustered = 1

    END

    SET @sSQL = @sSQL + ''CLUSTERED (''

    END

    ELSE

    BEGIN

    SET @sSQL = ''CREATE ''

    -- Check if the index is unique

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsUnique'') = 1)

    BEGIN

    SET @sSQL = @sSQL + ''UNIQUE ''

    END

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 1)

    BEGIN

    SET @sSQL = @sSQL + ''CLUSTERED ''

    SET @IsClustered = 1

    END

    ELSE

    BEGIN

    SET @IsClustered = 0

    END

    SELECT @sDSQL = ''IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = '' + CHAR(39) + @idxname + CHAR(39) + '' AND object_id = OBJECT_ID('' + CHAR(39) + OBJECT_SCHEMA_NAME(@idxTableID) + ''.'' + @idxTableName + CHAR(39) + '')) DROP INDEX ['' + @idxname + ''] ON ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + '']''

    SELECT

    @sSQL = @sSQL + ''INDEX ['' + @idxname + ''] ON ['' + OBJECT_SCHEMA_NAME(@idxTableID) + ''].['' + @idxTableName + ''] ('',

    @colCount = 0

    END

    -- Get the number of cols in the index

    SELECT @colCount = COUNT(*)

    FROM sys.index_columns ic

    INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id

    WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

    -- Get the file group info

    SELECT @location = f.[name]

    FROM sys.indexes i

    INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

    INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]

    WHERE o.object_id = @idxTableID AND i.index_id = @idxid

    -- Get all columns of the index

    INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )

    SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column

    FROM sys.index_columns ic

    INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id

    WHERE ic.object_id = @idxTableID AND index_id = @idxid

    ORDER BY ic.index_column_id

    IF @@ROWCOUNT > 0

    BEGIN

    SELECT @CurrentCol = 1

    SELECT @IxFirstColumn = 1, @sIncludeCols = '''', @sIndexCols = ''''

    WHILE @CurrentCol <= @ColCount

    BEGIN

    SELECT @ColumnIDInTable = ColumnIDInTable,

    @Name = Name,

    @ColumnIDInIndex = ColumnIDInIndex,

    @IsIncludedColumn = IsIncludedColumn

    FROM #ColumnListing

    WHERE [ColumnListingID] = @CurrentCol

    IF @IsIncludedColumn = 0

    BEGIN

    SET @sIndexCols = @sIndexCols + ''['' + @Name + ''] ''

    -- Check the sort order of the index cols ????????

    IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,''IsDescending'')) = 0

    BEGIN

    SET @sIndexCols = @sIndexCols + '' ASC ''

    END

    ELSE

    BEGIN

    SET @sIndexCols = @sIndexCols + '' DESC ''

    END

    IF @CurrentCol < @colCount

    BEGIN

    SET @sIndexCols = @sIndexCols + '', ''

    END

    END

    ELSE

    BEGIN

    -- Check for any include columns

    IF LEN(@sIncludeCols) > 0

    BEGIN

    SET @sIncludeCols = @sIncludeCols + '',''

    END

    SET @sIncludeCols = @sIncludeCols + ''['' + @IxColumn + '']''

    END

    SET @CurrentCol = @CurrentCol + 1

    END

    TRUNCATE TABLE #ColumnListing

    --append to the result

    IF LEN(@sIncludeCols) > 0

    SET @sIndexCols = @sSQL + @sIndexCols + '') '' + '' INCLUDE ( '' + @sIncludeCols + '' ) ''

    ELSE

    SET @sIndexCols = @sSQL + @sIndexCols + '') ''

    -- Add filtering

    IF @FilterDefinition IS NOT NULL

    SET @sFilterSQL = '' WHERE '' + @FilterDefinition + '' ''

    ELSE

    SET @sFilterSQL = ''''

    -- Build the options

    SET @sParamSQL = ''WITH ( PAD_INDEX = ''

    IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPadIndex'') = 1

    SET @sParamSQL = @sParamSQL + ''ON,''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF,''

    SET @sParamSQL = @sParamSQL + '' ALLOW_PAGE_LOCKS = ''

    IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPageLockDisallowed'') = 0

    SET @sParamSQL = @sParamSQL + ''ON,''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF,''

    SET @sParamSQL = @sParamSQL + '' ALLOW_ROW_LOCKS = ''

    IF INDEXPROPERTY(@idxTableID, @idxname, ''IsRowLockDisallowed'') = 0

    SET @sParamSQL = @sParamSQL + ''ON,''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF,''

    SET @sParamSQL = @sParamSQL + '' STATISTICS_NORECOMPUTE = ''

    -- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.

    IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsStatistics'') = 1)

    SET @sParamSQL = @sParamSQL + ''ON''

    ELSE

    SET @sParamSQL = @sParamSQL + ''OFF''

    -- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2

    IF ISNULL( @FillFactor, 90 ) <> 0

    SET @sParamSQL = @sParamSQL + '' ,FILLFACTOR = '' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) -- + '')''

    /*

    IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn''t valid for PK''s

    BEGIN

    --SET @sParamSQL = @sParamSQL + '' ) ''

    END

    ELSE

    BEGIN

    SET @sParamSQL = @sParamSQL + '' ,DROP_EXISTING = ON ) ''

    END

    */

    SET @sSQL = @sIndexCols + @sFilterSQL + @sParamSQL

    -- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement

    SET @sSQL = @sSQL + '' ON ['' + @location + '']''

    --PRINT @sIndexCols + CHAR(13)

    INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, IndexDropSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @sDSQL)

    END

    SET @CurrentIndex = @CurrentIndex + 1

    END

    SELECT IndexDropSQL FROM #IndexSQL

    DROP TABLE #IndexSQL'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Drop check constraints

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''IF EXISTS (SELECT OBJECT_ID('' + QUOTENAME(DefCons.name, CHAR(39)) + '', '' + CHAR(39) + ''C'' + CHAR(39) + '') FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS) ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] DROP CONSTRAINT [''+DefCons.name+'']''

    FROM sys.default_constraints AS DefCons

    INNER JOIN sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id

    INNER JOIN sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Recreate UDF's

    SET @sql = 'USE [' + @DBName + '];

    SELECT m.definition

    FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''FN'', ''IF'', ''TF'')'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Add computed columns

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER TABLE ['' + OBJECT_NAME(c.object_id) + ''] ADD ['' + c.name + ''] AS '' + c.definition

    FROM sys.computed_columns c'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Drop computed columns

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER TABLE ['' + OBJECT_NAME(c.object_id) + ''] DROP COLUMN ['' + c.name + '']''

    FROM sys.computed_columns c'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Script collation change at column level for every (N)CHAR, (N)VARCHAR and (N)TEXT columns

    SET @sql = 'USE [' + @DBName + '];

    DECLARE @TableName NVARCHAR(255),

    @SchemaID NVARCHAR(255),

    @CollationName NVARCHAR(255),

    @ColumnName NVARCHAR(255),

    @DataType NVARCHAR(255),

    @CharacterMaxLen NVARCHAR(255),

    @IsNullable NVARCHAR(255),

    @SQLText NVARCHAR(4000)

    DECLARE @Results TABLE (SQLStatement NVARCHAR(4000))

    DECLARE MyTableCursor Cursor FOR

    SELECT name, schema_id FROM sys.tables WHERE [type] = ''U'' and name <> ''sysdiagrams'' ORDER BY name

    OPEN MyTableCursor

    SET @CollationName = ''' + @NewCollation + '''

    FETCH NEXT FROM MyTableCursor INTO @TableName, @SchemaID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE MyColumnCursor Cursor

    FOR

    SELECT c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS c

    WHERE c.TABLE_NAME = @TableName AND (c.DATA_TYPE LIKE ''%char%'' OR c.DATA_TYPE LIKE ''%text%'') AND c.COLLATION_NAME <> @CollationName

    AND NOT EXISTS (SELECT co.column_id FROM sys.computed_columns co WHERE co.object_id = OBJECT_ID(@TableName) AND co.name = c.COLUMN_NAME)

    ORDER BY c.ORDINAL_POSITION

    Open MyColumnCursor

    FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @SQLText = ''ALTER TABLE ['' + schema_name(@SchemaID) + ''].['' + @TableName + ''] ALTER COLUMN ['' + @ColumnName + ''] '' + @DataType + CASE WHEN @DataType <> ''text'' THEN ''('' + CASE WHEN @CharacterMaxLen = -1 THEN ''MAX'' ELSE @CharacterMaxLen END + '')'' ELSE '''' END + '' COLLATE '' + @CollationName + '' '' + CASE WHEN @IsNullable = ''NO'' THEN ''NOT NULL'' ELSE ''NULL'' END

    INSERT INTO @Results SELECT @SQLText

    FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable

    END

    CLOSE MyColumnCursor

    DEALLOCATE MyColumnCursor

    FETCH NEXT FROM MyTableCursor INTO @TableName, @SchemaID

    END

    CLOSE MyTableCursor

    DEALLOCATE MyTableCursor

    SELECT * FROM @Results'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Drop UDF's

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''IF EXISTS (SELECT o.name FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''''FN'''', ''''IF'''', ''''TF'''') AND o.name = '' + QUOTENAME(o.NAME, CHAR(39)) + '') DROP FUNCTION ['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.NAME + '']''

    FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''FN'', ''IF'', ''TF'')'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Drop views

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''IF EXISTS (SELECT o.name FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''''V'''') AND o.name = '' + QUOTENAME(o.NAME, CHAR(39)) + '') DROP VIEW ['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.NAME + '']''

    FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''V'')'

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Change collation at database level

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    ALTER DATABASE [' + @DBName + '] COLLATE ' + @NewCollation + ';

    ALTER DATABASE [' + @DBName + '] SET MULTI_USER'''

    INSERT INTO @DropScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Recreate views

    SET @sql = 'USE [' + @DBName + '];

    SELECT m.definition

    FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''V'')'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    -- Recreate permissions

    SET @sql = 'USE [' + @DBName + '];

    SELECT ''GRANT '' + dp.permission_name collate ' + @NewCollation + '

    + '' ON '' + s.name + ''.'' + o.name + '' TO ['' + dpr.name + '']''

    FROM sys.database_permissions AS dp

    INNER JOIN sys.objects AS o ON dp.major_id=o.object_id

    INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id

    INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id

    WHERE dpr.name NOT IN (''guest'')'

    INSERT INTO @CreateScripts (SQLStatement)

    EXEC sp_executesql @sql

    IF @JustPrintStatements = 1

    BEGIN

    -- Just return recordset of statements that need to be executed to change collation, drop statements first followed by creation statements

    SELECT SQLStatement FROM @DropScripts GROUP BY SQLStatement ORDER BY MIN(ID)

    SELECT SQLStatement FROM @CreateScripts GROUP BY SQLStatement ORDER BY MIN(ID)

    END

    ELSE

    BEGIN

    -- Execute SQL statements in dependency order

    DECLARE SQLStatements CURSOR FOR

    SELECT SQLStatement FROM @DropScripts GROUP BY SQLStatement ORDER BY MIN(ID)

    OPEN SQLStatements

    FETCH NEXT FROM SQLStatements INTO @sql

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @bigsql = 'USE [' + @dbname + ']; EXEC (' + CHAR(39) + REPLACE(@sql, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39) + ')'

    IF @DebugPrintSQL = 1 PRINT @bigsql

    EXEC sp_executesql @bigsql

    FETCH NEXT FROM SQLStatements INTO @sql

    END

    CLOSE SQLStatements

    DEALLOCATE SQLStatements

    DECLARE SQLStatements CURSOR FOR

    SELECT SQLStatement FROM @CreateScripts GROUP BY SQLStatement ORDER BY MIN(ID)

    OPEN SQLStatements

    FETCH NEXT FROM SQLStatements INTO @sql

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @bigsql = 'USE [' + @dbname + ']; EXEC (' + CHAR(39) + REPLACE(@sql, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39) + ')'

    IF @DebugPrintSQL = 1 PRINT @bigsql

    EXEC (@bigsql)

    FETCH NEXT FROM SQLStatements INTO @sql

    END

    CLOSE SQLStatements

    DEALLOCATE SQLStatements

    END

    -- Tidy up - remove stored procedures created

    SET @sql = REPLACE('IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptDropTableKeys'') DROP PROCEDURE ScriptDropTableKeys', CHAR(39), CHAR(39)+CHAR(39))

    SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + @sql + '''';

    EXEC (@bigsql)

    SET @sql = 'IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptCreateTableKeys'') DROP PROCEDURE ScriptCreateTableKeys'

    SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''''

    EXEC (@bigsql)

    END

  • SQLmansell

    SSC Enthusiast

    Points: 105

    I like the script but it is unfortunate that it doesn't pick up the schema name too, anything other than dbo results in "Cannot find the object because it does not exist or you do not have permissions."

    Has anyone successfully adjusted the Stored Procedure to include [Schema].[Table] so that it supports multiple schemas?

    Andrew Mansell
    sqlmansell.com Useful everyday hints and tips for SQL Server
    Twitter: @sqlmansell

  • scratt

    Grasshopper

    Points: 11

    Andrew,

    I did update the procedure to change collation of other schema as well. I also made other improvements to the procedure, like using quotename() consistently and optimized the order of execution for SQL Server 2014. I commented the major changes inline. I hope this helps you as much as it did me!

    Thanks,

    Matthew

  • SQLmansell

    SSC Enthusiast

    Points: 105

    Thanks Scratt, that's exactly what I had in mind. Nice work and thanks to the original author too.

    Andrew Mansell
    sqlmansell.com Useful everyday hints and tips for SQL Server
    Twitter: @sqlmansell

Viewing 8 posts - 1 through 8 (of 8 total)

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