SQL Server: Only Database and Object Collation Change

Well this gonna be a boring and very lengthy blog. But I have prepared it after researching with the geeks' docs. I finally made my database with another collation. Previously I thoght that only unique and primary key will need to be recreated but the thing is nonclustered and non unique index also need to be changed. Any way go through my steps and suggests if I missed any thing. At the end I have given all the sources I used.

Summary:

The existing Database connection can be changed using the ALTER Database command provided in SQL Server. To change the collation settings

  • Restrict the Database access to Single User Mode
  • Change the Database collation using the following syntax.

    ALTER DATABASE dbname COLLATE [Replace Actual Collation]
    GO

This will only change the collation of the Database and not the collation of the Database objects (Tables) if any exists already. We need to explicitly change the collation of the character (VARCHAR / NVARCHAR) columns for each table. To change the collation settings for columns

  • Take the backup of all the Indexes and constraints of the tables with Character columns in the Database
  • Drop all the Indexes and constraints (Primary Key, Foreign Key, Defaults etc)



  • Change the Collation setting for the table columns. The below script can be used to generate the script that identifies the character columns and replace it with the new collation in the Database
Command Set:


  1. USE GUI to take the DB in SINGLE USER Mode
  2. Change the DB Collation and Check.
ALTER DATABASE COLLATION_TEST COLLATE  Latin1_General_CI_AS
Go
SELECT SERVERPROPERTY('Collation') AS 'Collation'
GO

Output:
Latin1_General_CI_AS


  1. Script for change collation of all columns and name it ChangeCollation.sql


SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name + 
    CASE systypes.NAME
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE SYSCOLUMNS.length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,SYSCOLUMNS.length)
    END) + ') ' 
    END
    + ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
    WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
    AND SYSOBJECTS.TYPE = 'U'
    AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
    AND SYSCOLUMNS.COLLATION IS NOT NULL
    AND NOT ( sysobjects.NAME LIKE 'sys%' )
    AND NOT ( SYSTYPES.name LIKE 'sys%' )
    GO
 
  1. Script to make all Index and Keys and execute the procedure like [do this in a text pad. Collect the table list from above script. Eliminate duplicate and run below SP.]
exec  ScriptCreateTableKeys Table1
exec  ScriptCreateTableKeys Table2
….

  1. Save the output of all the above query  as CreateKeysAndIndexes.sql
/*
Script Table Keys
(C) 2010 Adam Machanic - amachanic@gmail.com
http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx
This script produces a script of all of the candidate keys (primary keys or unique
constraints) as well as referencing foreign keys, for the target table. To use, put
SSMS into "results in text" mode and run the script. The output will be a formatted
script that you can cut and paste to use elsewhere.

Don't forget to configure the maximum text size before using. The default is 256
characters--not enough for many cases.

Tools->Options->Query Results->Results to Text->Maximum number of characters->8192
*/
CREATE PROC [dbo].[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')
    BEGIN
        RAISERROR('This script only supports SQL Server 2005 and 2008', 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
END

  1. Drop index and key script needs to be made by running below query and save it as DropKeysAndIndexes.sql

CREATE PROC [dbo].[ScriptDropTableKeys]
    @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')
    BEGIN
        RAISERROR('This script only supports SQL Server 2005 and 2008', 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

 END


  1. Create a procedure usp_GenerateIndexesScript to generate create script of all non-clustered index.
----- A: First Enable OLE Automation -------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

----- B: Second Create the Proc usp_OLEwritefile. This SP actually write file --- from SQL -–

CREATE PROCEDURE [dbo].[usp_OLEWriteFile] (@FileName varchar(1000), @TextData NVARCHAR(MAX),@FileAction VARCHAR(12)) AS 
BEGIN 
DECLARE @OLEfilesytemobject INT 
DECLARE @OLEResult INT 
DECLARE @FileID INT  
EXECUTE @OLEResult = 
sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT 
IF @OLEResult <> 0  
PRINT 'Error: Scripting.FileSystemObject'   

-- check if file exists 
EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @FileName  

-- if file esists 
IF (@OLEresult=1 AND @FileAction = 'APPEND') OR (@OLEresult=0)  

BEGIN     
IF (@FileAction = 'CREATENEW') 
PRINT 'New file specified, creating...' 
IF (@OLEresult=1 AND @FileAction = 'APPEND')  
PRINT 'File exists, appending...' 

IF (@OLEresult=0 AND @FileAction = 'APPEND')  
PRINT 'File doesnt exist, creating...'  
-- open file 
EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT, 

@FileName, 8, 1 

IF @OLEResult <>0 PRINT 'Error: OpenTextFile' 

-- write Text1 to the file 
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextData 

IF @OLEResult <> 0  

PRINT 'Error : WriteLine' 

ELSE 

PRINT 'Success'  

END 

IF (@OLEresult=1 AND @FileAction = 'CREATENEW') 

PRINT 'File Exists, specify APPEND if this is the desired action' 
EXECUTE @OLEResult = sp_OADestroy @FileID 
EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject  
END 
GO 

----- C: Then Create the Proc usp_GenerateIndexesScript -----

USE [COLLATION_TEST]
GO
/****** Object:  StoredProcedure [dbo].[usp_GenerateIndexesScript]    Script Date: 08/01/2012 12:44:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GenerateIndexesScript] 
( 
     @IncludeFileGroup  bit = 1 
    ,@IncludeDrop   bit = 1 
    ,@destinationparameter  NVARCHAR(1000) = NULL  
) 
AS 
BEGIN
    -- Get all existing indexes, but NOT the primary keys 
    DECLARE Indexes_cursor CURSOR 
        FOR SELECT  
     SC.Name   AS SchemaName 
     , SO.Name  AS TableName 
     , SI.Object_Id  AS TableId 
     , SI.[Name]     AS IndexName 
     , SI.Index_ID   AS IndexId 
     , FG.[Name]     AS FileGroupName 
     , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor 
       FROM sys.indexes SI 
              LEFT JOIN sys.filegroups FG 
                     ON SI.data_space_id = FG.data_space_id 
              INNER JOIN sys.objects SO 
                     ON SI.object_id = SO.object_id 
              INNER JOIN sys.schemas SC 
                     ON SC.schema_id = SO.schema_id
       WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1 
           AND SI.[Name] IS NOT NULL 
           AND SI.is_primary_key = 0 
           AND SI.is_unique_constraint = 0 
           AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0 
           AND FG.[Name]  IS NOT NULL 
           ORDER BY SC.Name , SO.Name ,SI.[Name]     

        DECLARE @SchemaName  sysname 
    DECLARE @TableName  sysname 
    DECLARE @TableId  int 
    DECLARE @IndexName  sysname 
    DECLARE @FileGroupName sysname 
    DECLARE @IndexId  int 
    DECLARE @FillFactor  int
    DECLARE @NewLine nvarchar(4000)      
    SET @NewLine = CHAR(13) + CHAR(10)  
    DECLARE @Tab nvarchar(4000)      
    SET @Tab = Space(4)  
 DECLARE @SQLOutput nvarchar(max)  

 SET @SQLOutput = '' 

    -- Loop through all indexes 
    OPEN Indexes_cursor  
    FETCH NEXT 
     FROM Indexes_cursor 
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor  
    WHILE (@@Fetch_Status = 0) 
        BEGIN  
            DECLARE @sIndexDesc nvarchar(4000) 
            DECLARE @sCreateSql nvarchar(4000) 
            DECLARE @sDropSql  nvarchar(4000)  
            SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @SchemaName + '.' + @TableName 
            SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine 
                          + '            FROM sys.indexes si' + @NewLine 
                          + '            INNER JOIN sys.objects so' + @NewLine 
                          + '                   ON so.object_id = si.object_id' + @NewLine 
                          + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine 
                          + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                          + 'BEGIN' + @NewLine 
                          + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine 
                          + 'END' + @NewLine   

            SET @sCreateSql = 'CREATE ' 
            -- Check if the index is unique 
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1) 
                BEGIN 
                    SET @sCreateSql = @sCreateSql + 'UNIQUE ' 
                END 
            --END IF 
            -- Check if the index is clustered 
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1) 
                BEGIN 
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED ' 
                END 
            --END IF 
            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine  

            -- Get all columns of the index 
            DECLARE IndexColumns_cursor CURSOR 
                FOR SELECT SC.[Name], 
                           IC.[is_included_column], 
                           IC.is_descending_key 
                      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 = @TableId 
                       AND Index_ID = @IndexId 
                     ORDER BY IC.[is_included_column], 
         IC.key_ordinal  
            DECLARE @IxColumn   sysname 
            DECLARE @IxIncl     bit 
            DECLARE @Desc     bit 
            DECLARE @IxIsIncl     bit     SET @IxIsIncl = 0 
            DECLARE @IxFirstColumn  bit     SET @IxFirstColumn = 1  
            -- Loop through all columns of the index and append them to the CREATE statement 

            OPEN IndexColumns_cursor 
            FETCH NEXT 
             FROM IndexColumns_cursor 
             INTO @IxColumn, @IxIncl, @Desc  
            WHILE (@@Fetch_Status = 0) 
                BEGIN 
                    IF (@IxFirstColumn = 1) 
                        BEGIN 
                            SET @IxFirstColumn = 0 
                        END 
                    ELSE 
                        BEGIN 
                            --check to see if it's an included column 
                            IF (@IxIsIncl = 0) AND (@IxIncl = 1) 
                                BEGIN 
                                    SET @IxIsIncl = 1
                                    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine 
                                END 
                            ELSE 
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                END 
                            --END IF
                        END 
                    --END IF
                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']' 
                    -- check if ASC or DESC 
                    IF @IxIsIncl = 0 
                        BEGIN 
                            IF @Desc = 1 
                                BEGIN 
                                    SET @sCreateSql = @sCreateSql + ' DESC' 
                                END 
                            ELSE 
                                BEGIN 
                                    SET @sCreateSql = @sCreateSql + ' ASC' 
                                END 
                            --END IF 
                        END 
                    --END IF 
                    FETCH NEXT 
                     FROM IndexColumns_cursor 
                     INTO @IxColumn, @IxIncl, @Desc 
                END 
            --END WHILE 
            CLOSE IndexColumns_cursor 
            DEALLOCATE IndexColumns_cursor 

            SET @sCreateSql = @sCreateSql + @NewLine + ') ' 
            IF @IncludeFileGroup = 1 
                BEGIN 
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine 
                END 
            ELSE 
                BEGIN 
                    SET @sCreateSql = @sCreateSql + @NewLine 
                END 
            --END IF 
            PRINT '-- ************************************************' 

            PRINT @sIndexDesc 

            PRINT '-- *************************************************'  

  SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine 
        SET @SQLOutput = @SQLOutput +  @sIndexDesc + @NewLine 

        SET @SQLOutput = @SQLOutput +  '-- **********************************************************************' + @NewLine 
  SET @SQLOutput = @SQLOutput + @NewLine 
            IF @IncludeDrop = 1 
                BEGIN 
                    PRINT @sDropSql 
                    PRINT 'GO' 
                    
                   SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine 
                   SET @SQLOutput = @SQLOutput + 'GO' + @NewLine  
                END 
            --END IF 
            PRINT @sCreateSql 
            PRINT 'GO' + @NewLine  + @NewLine 
            SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine 
            SET @SQLOutput = @SQLOutput + 'GO' + @NewLine   

            FETCH NEXT 
             FROM Indexes_cursor 
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor 
        END 
    --END WHILE 
    CLOSE Indexes_cursor 
    DEALLOCATE Indexes_cursor 

     
    -- @SQLOutput contains the output to place in a file 

     
IF LEN(@destinationparameter) > 0 
 BEGIN 
 
 EXEC dbo.usp_OLEwritefile  @FileName = @destinationparameter 
        , @TextData = @SQLOutput 
        , @FileAction = 'CREATENEW' 
 END     
    --PRINT @SQLOutput 
END 
GO
----------------- recreate all non clustered indexes ---------------

EXEC dbo.usp_GenerateIndexesScript  
      @IncludeFileGroup   = 1 
     ,@IncludeDrop   = 1 
     ,@destinationparameter  = 'E:\temp\Collation_Test_20120731\MyNonClusteredIndexes.sql' 



  1. Drop Script of all Non Clustered Index [Don't Fire it Now !!!]

------------------------- Drop the Indexes -------------------------------
-------------------------------------------------------------------------

declare @name sysname
declare @TableName sysname
declare @sql varchar(4096)
declare vCursor cursor for
        select [name], [TableName] from NonClusteredIndexesToDelete order by [name]

open vCursor
fetch NEXT from vCursor into @name, @TableName
while @@FETCH_STATUS = 0
    begin
        set @sql = 'drop index ' + @TableName + '.' + @name
        exec (@sql)
        fetch NEXT from vCursor into @name, @TableName
    End    
close vCursor
deallocate vCursor


  1. Run command in below sequence
  1. DropKeysAndIndexes.sql
  2. Run command on step 8
  3. ChangeCollation.sql
  4. CreateKeysAndIndexes.sql
  5. MyNonClusteredIndexes.sql


Sources:
http://sqlsolace.blogspot.com.au/2010/08/automatically-script-non-clustered.html
http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

Comments

Post a Comment

Popular posts from this blog

Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts: Taking the instance in single user mode and Restoring the Master Database If you need to rebuild the system databases on top of this you can use the below command: "C:\Program Files\Microsoft SQL Server\ 100 \Setup Bootstrap\ SQLServer2008R2 \setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS" here 100 is for SQL Server 2008R2. It can vary case by case. How to take instance to single user mode: Two situation can happen: 1. Standalone Instance  2. Failover Cluster Instance 1. Standalone Instance: Go to SQL Config Manager SQL Service Properties -> Advanced Properties -> Startup Parameters put -m; in the Startup Parameters Apply and Okay - it will ask to restart the SQL Service to take effect. Stop and Start the service from Config Manager or you can stop and serv...
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al...
Read more