T-SQL TO DROP/CREATE IDENTITY SETTING ON ALL TABLES


/*
---------------------------------------------------------------------------------------------------------
PRINTS T-SQL TO DROP/CREATE IDENTITY SETTING ON ALL TABLES

BACKUP BEFORE RUNNING THE GENERATED SQL! - DROPS AND RECREATES EVERYTHING YOU SELECT.
ALSO PLEASE READ THE BELOW - THIS IS A SIMPLIFIED PURPOSE BUILT SCRIPT - YOU WILL NEED TO MODIFY TO YOUR REQUIREMENTS.

THERE IS NO WAY OF REMOVING THE IDENTITY(START, SEED) SETTING ON TABLES -
AND AS IDENTITY INSERT ON/OFF DOES NOT DO THE JOB FOR ALL ORMs (LIKE LINQ FOR EXAMPLE) WE'RE STUCK WITH THE
SQL SERVER MANAGEMENT IDE SOLUTION: CREATE TEMP TABLES - WITH/WITHOUT THE IDENTITY SETTING, MOVE THE DATA, DROP ORIGINAL AND RENAME TEMP TO ORG.

THIS IS A SIMPLIFIED VERSION THAT DOES NOT TAKE INTO CONSIDERATION FKs OR PKs - SCRIPTS TO DROP AND RECREATE THESE ARE AVAILABLE ELSEWHERE
ALSO IT ASSUMES THE PK IDENTITY IS AN INT COLUMN NAMED Id!

SQL2005 AND ABOVE ONLY (SYS VIEWS)

CONFIG:
@ADD_IDENTITY => set to true to ADD this, set to false to REMOVE this.

---------------------------------------------------------------------------------------------------------
*/

DECLARE @ADD_IDENTITY BIT
SET @ADD_IDENTITY = 0 --!!!!! SEE ABOVE!

DECLARE @TABLENAME NVARCHAR(250)
DECLARE @TABLESCHEMA NVARCHAR(250)
DECLARE @TSQL NVARCHAR(MAX)
DECLARE @COLS NVARCHAR(MAX)

DECLARE DBO_TABLE_CURSOR CURSOR

READ_ONLY
FOR SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
    WHERE
            TABLE_NAME NOT LIKE 'Version%'
        AND TABLE_NAME NOT LIKE 'sys%'
        AND TABLE_SCHEMA = 'dbo'
        AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY TABLE_NAME

OPEN DBO_TABLE_CURSOR
FETCH NEXT FROM DBO_TABLE_CURSOR INTO @TABLENAME, @TABLESCHEMA
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
        SET @TSQL = ''
        
        --Get rid of default constraints:
        SELECT @TSQL = @TSQL + '    
        ALTER TABLE dbo.' + @TABLENAME +'
            DROP CONSTRAINT ' + DF.[NAME] + '
        GO' FROM
            SYS.DEFAULT_CONSTRAINTS DF
                INNER JOIN SYS.ALL_COLUMNS AC
                    ON        DF.PARENT_OBJECT_ID = AC.OBJECT_ID
                        AND DF.PARENT_COLUMN_ID = AC.COLUMN_ID
        WHERE OBJECT_NAME(PARENT_OBJECT_ID) = @TABLENAME

        --Create temp table:
        SET @TSQL = @TSQL + '
        
        CREATE TABLE dbo.Tmp_' + @TABLENAME + '
        (
        '        
        SELECT @TSQL = @TSQL + '    [' + COLUMN_NAME + '] ' + UPPER(DATA_TYPE)
            + ISNULL(('(' + CONVERT(NVARCHAR,CHARACTER_MAXIMUM_LENGTH) + ') '), ' ') --Bits and Timestamps have null in all three, something added to null, is null - so we ISNULL check the whole evalutation, and add a space for bits and timestamps.
            + CONVERT(NVARCHAR, REPLACE(REPLACE(IS_NULLABLE, 'YES', 'NULL'), 'NO', 'NOT NULL'))            
            + ISNULL(' CONSTRAINT [DF_' + @TABLENAME + '_' + COLUMN_NAME + '] DEFAULT (' + CONVERT(NVARCHAR, COLUMN_DEFAULT) + ')', '')
            + ',
        '
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
                TABLE_NAME = @TABLENAME
            AND TABLE_SCHEMA = @TABLESCHEMA
        ORDER BY ORDINAL_POSITION ASC

        --Remove trailing comma:
        SET @TSQL = SUBSTRING(@TSQL, 1, LEN(@TSQL) -5) --remove trailing tabs, linebreak and finally the comma
        
        SET @TSQL = @TSQL + '    
        ) ON [PRIMARY]
        GO'
        
        --So Microsoft seem to have hacked the old NVARCHAR(MAX) - the CHARACTER_MAXIMUM_LENGTH is a int column - so they've added -1 here
        SET @TSQL = REPLACE(@TSQL, '(-1)', '(MAX)')

        --Finally - are we adding or removing the IDENTITY setting?
        IF @ADD_IDENTITY = 1
        BEGIN
            SET @TSQL = REPLACE(@TSQL, '[Id] INT NOT NULL,', '[Id] INT NOT NULL IDENTITY (1,1),')
        END

        PRINT @TSQL -- Half way there! Got temp tables, now let's move the data, drop the original, and rename the temp to replace the original... what could possibly go wrong?
        
        --RESET @TSQL:
        SET @TSQL = '
        IF EXISTS(SELECT * FROM dbo.' + @TABLENAME + ')
        BEGIN '
        IF @ADD_IDENTITY = 1--If so we need to set "IDENTITY_INSERT ON" on the temp tables.
        BEGIN
        SET @TSQL = @TSQL + '    
            SET IDENTITY_INSERT dbo.Tmp_' + @TABLENAME + ' ON'
        END

        --Now get all the column names:
        SET @COLS = ''
        SELECT @COLS = @COLS + '[' + COLUMN_NAME + '],'
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
                TABLE_NAME = @TABLENAME
            AND TABLE_SCHEMA = @TABLESCHEMA
            AND DATA_TYPE 'timestamp'
        ORDER BY ORDINAL_POSITION ASC
    
        --Remove trailing comma:
        SET @COLS = SUBSTRING(@COLS, 1, LEN(@COLS) -1)
        SET @TSQL = @TSQL + '
            INSERT INTO dbo.Tmp_' + @TABLENAME + ' (' + @COLS + ')
                SELECT ' + @COLS + '
            FROM dbo.' + @TABLENAME + ' WITH (HOLDLOCK TABLOCKX)
        '        

        IF @ADD_IDENTITY = 1 --If we're adding this, then we have switched IDENTITY_INSERT ON - now switch this back off (only one table at any one time in each DB)
        BEGIN
        SET @TSQL = @TSQL + '    
            SET IDENTITY_INSERT dbo.Tmp_' + @TABLENAME + ' OFF'
        END
        
        SET @TSQL = @TSQL + '
        END'

        SELECT @TSQL = @TSQL + '
        DROP TABLE dbo.' + @TABLENAME + '
        GO'
        SELECT @TSQL = @TSQL + '
        EXECUTE sp_rename N''dbo.Tmp_' + @TABLENAME +''', N''' + @TABLENAME +''', ''OBJECT''
        GO'

        PRINT @TSQL
        
END
FETCH NEXT FROM DBO_TABLE_CURSOR INTO @TABLENAME, @TABLESCHEMA
END

CLOSE DBO_TABLE_CURSOR
DEALLOCATE DBO_TABLE_CURSOR

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: