All geek questions in one place

How to change columns from one data type to third-party using a script

I have to change for a large number of 30+ tables one column data type from Intto BigInt. My problem is that a specific column is also for one table PK, and for others - FK. Therefore, I cannot change because I received an error message.

I need an algorithm, or some kind of script. But I can’t understand the steps or approach: should I use a toxor or just a temporary table from which you can delete the record and always get the first ...

What am I supposed to do? Take the first table from the stack, check if the PK or FK column refers if it omits the links (PK, FK, etc.), and then change the column type and then recreate Pk and FK? Should I keep what I doped? I am very confused ...

Any clues?

+4
source share
2 answers

Ok, this will be a beast script, but I'm working on the same issue right now. This script still works, but should help you with what you need to do.

My current code uses the given table and field, feel free to edit;

DECLARE @TableToEdit varchar(255); SET @TableToEdit = 'Entity'
DECLARE @MasterField sysname; SET @MasterField = 'Entity_Identifier'

First you need to reset all foreign keys;

IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
    DROP TABLE #ForeignKeys

CREATE TABLE #ForeignKeys
(
    PKTABLE_QUALIFIER nvarchar(255) NULL
    ,PKTABLE_OWNER nvarchar(255) NULL
    ,PKTABLE_NAME nvarchar(255) NULL
    ,PKCOLUMN_NAME varchar(255) NULL
    ,FKTABLE_QUALIFIER nvarchar(255) NULL
    ,FKTABLE_OWNER nvarchar(255) NULL
    ,FKTABLE_NAME nvarchar(255) NULL
    ,FKCOLUMN_NAME nvarchar(255) NULL
    ,KEY_SQL int NULL
    ,UPDATE_RULE int NULL
    ,DELETE_RULE int NULL
    ,FK_NAME nvarchar(255) NULL
    ,PK_NAME nvarchar(255) NULL
    ,DEFERRABILITY int NULL
)


/* find all tables that have an Entity_Identifier field that not already an int */
IF OBJECT_ID('tempdb..#MasterTables') IS NOT NULL DROP TABLE #MasterTables
CREATE TABLE #MasterTables (MasterTableName sysname, MasterTableSQL nvarchar(max))
INSERT INTO #MasterTables (MasterTableName, MasterTableSQL)
SELECT DISTINCT
o.name TableName
,'INSERT INTO #ForeignKeys EXEC sp_fkeys ' + o.name MasterTableSQL
FROM sys.objects o
JOIN sys.columns c
    ON o.object_id = c.object_id
JOIN sys.types t
    ON c.user_type_id = t.user_type_id
WHERE o.type = 'u'
--AND t.name NOT IN ('int','bigint')
--AND c.name LIKE '%Entity_Identifier%'
AND c.name LIKE '%' + @MasterField + '%'



/* Let find all Foreign Keys based upon a table that has an Entity_Identifier field that needs to be converted */

DECLARE @execspfkeys nvarchar(max)

DECLARE spfkeyscursor CURSOR LOCAL FOR
    SELECT MasterTableSQL FROM #MasterTables 

OPEN spfkeyscursor

FETCH NEXT FROM spfkeyscursor INTO @execspfkeys

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @execspfkeys

    FETCH NEXT FROM spfkeyscursor INTO @execspfkeys
END

CLOSE spfkeyscursor
DEALLOCATE spfkeyscursor



/* Ok, let get the foreign key definitions from all relevant tables */

IF OBJECT_ID('tempdb..#FKScripts') IS NOT NULL
    DROP TABLE #FKScripts
CREATE TABLE #FKScripts
(
    FKName nvarchar(255)
    ,FKTableName nvarchar(255)
    ,FKSchema nvarchar(10)
    ,FKDatabase nvarchar(255)
    ,PKName nvarchar(255)
    ,PKTableName nvarchar(255)
    ,PKSchema nvarchar(10)
    ,PKDatabase nvarchar(255)
    ,FKDisableScript nvarchar(max)
    ,FKRebuildScript nvarchar(max)
    ,FKCheckScript nvarchar(max)
)

INSERT INTO #FKScripts (FKName, FKTableName, FKSchema, FKDatabase, PKName, PKTableName, PKSchema, PKDatabase, FKDisableScript, FKRebuildScript, FKCheckScript)
SELECT DISTINCT 
    fk.FK_NAME
    ,fk.FKTABLE_NAME
    ,fk.FKTABLE_OWNER
    ,fk.FKTABLE_QUALIFIER
    ,fk.PK_NAME
    ,fk.PKTABLE_NAME
    ,fk.PKTABLE_OWNER
    ,fk.PKTABLE_QUALIFIER
    ,'ALTER TABLE [' + fk.FKTABLE_OWNER + '].[' + fk.FKTABLE_NAME + '] DROP CONSTRAINT [' + fk.FK_NAME + ']' FKDisableScript
    ,'ALTER TABLE [' + fk.FKTABLE_OWNER + '].[' + fk.FKTABLE_NAME + '] WITH CHECK ADD CONSTRAINT [' + fk.FK_NAME + '] FOREIGN KEY (' + PKList.FieldList +') REFERENCES [dbo].[' + fk.PKTABLE_NAME + '] (' + FKlist.FieldList + ')' FKRebuildScript
    ,'ALTER TABLE [' + fk.FKTABLE_OWNER + '].[' + fk.FKTABLE_NAME + '] CHECK CONSTRAINT [' + fk.FK_NAME + ']' FKCheckScript
FROM #ForeignKeys fk
INNER JOIN
    (
    SELECT DISTINCT
          fk.FK_NAME,
          STUFF((SELECT ','+ fk2.PKCOLUMN_NAME
                  FROM (SELECT FK_NAME, '[' + PKCOLUMN_NAME +']' PKCOLUMN_NAME FROM #ForeignKeys) fk2
                 WHERE FK.FK_NAME = fk2.FK_NAME
              GROUP BY fk2.PKCOLUMN_NAME
               FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') FieldList
    FROM #ForeignKeys fk
    ) PKlist
ON fk.FK_NAME = PKlist.FK_NAME
INNER JOIN
    (
    SELECT DISTINCT
          fk.FK_NAME,
          STUFF((SELECT ','+ fk2.FKCOLUMN_NAME
                  FROM (SELECT FK_NAME, '[' + FKCOLUMN_NAME + ']' FKCOLUMN_NAME FROM #ForeignKeys) fk2
                 WHERE FK.FK_NAME = fk2.FK_NAME
              GROUP BY fk2.FKCOLUMN_NAME
               FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') FieldList
    FROM #ForeignKeys fk
    ) FKlist
ON fk.FK_NAME = FKlist.FK_NAME

DROP TABLE #ForeignKeys



SELECT * FROM #FKScripts


/* OK, let disable these foreign keys, going to have to use a cursor for this (ouch) */

DECLARE @disablesql nvarchar(max)

DECLARE discur CURSOR LOCAL FOR
    SELECT FKDisableScript FROM #FKScripts 

OPEN discur

FETCH NEXT FROM discur INTO @disablesql

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @disablesql

    FETCH NEXT FROM discur INTO @disablesql
END

CLOSE discur
DEALLOCATE discur

/* right, we're finished with the cursor that disables the foreign keys, phew! */


/* check that the constraints are now disabled (for testing) */
SELECT DISTINCT
fkt.*
,CASE WHEN ((C.Status & 0x4000)) = 0 THEN 1 ELSE 0 END [Enabled2]
FROM #FKScripts fkt
INNER JOIN sys.sysobjects o2
    ON o2.name = fkt.FKName
INNER JOIN sys.sysobjects o 
    ON o2.id = o.parent_obj
    AND o.xtype='F'
INNER JOIN sys.sysconstraints c
    ON  o.id = c.constid  

    select * from #FKScripts

Then you will need to remove all indexes;

/* Drop index scripts into a temp table */

IF OBJECT_ID('tempdb..#CreateIndexes') IS NOT NULL DROP TABLE #CreateIndexes
GO

DECLARE @SchemaName varchar(100)
DECLARE @TableName varchar(256)
DECLARE @IndexName varchar(256)
DECLARE @ColumnName varchar(100)
DECLARE @is_unique varchar(100)
DECLARE @IndexTypeDesc varchar(100)
DECLARE @FileGroupName varchar(100)
DECLARE @is_disabled bit
DECLARE @is_primary_key bit
DECLARE @IndexOptions varchar(max)
DECLARE @IndexColumnId int
DECLARE @IsDescendingKey int 
DECLARE @IsIncludedColumn int
DECLARE @TSQLScripCreationIndex varchar(max)
DECLARE @TSQLScripDisableIndex varchar(max)

CREATE TABLE #CreateIndexes (
                            SchemaName varchar(max)
                            ,TableName varchar(max)
                            ,IndexName varchar(max)
                            ,is_unique varchar(max)
                            ,IndexTypeDesc varchar(max)
                            ,is_disabled bit
                            ,is_primary_key bit
                            ,FileGroupName varchar(max)
                            ,DropScript varchar(max)
                            ,TSQLScripCreationIndex varchar(max)
                            ,TSQLScripDisableIndex varchar(max)
                            )

DECLARE CursorIndex CURSOR FOR
 SELECT schema_name(t.schema_id) [schema_name]
 , t.name
 , ix.name
 , CASE WHEN ix.is_unique = 1 THEN 'UNIQUE ' ELSE '' END 
 , ix.type_desc
 , '' IndexOptions -- case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
    --+ case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
    --+ case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
    --+ case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
    --+ case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
    --+ 'SORT_IN_TEMPDB = OFF, FILLFACTOR = ' + CASE WHEN ix.fill_factor = 0 THEN '90' ELSE CAST(ix.fill_factor AS VARCHAR(3))  END AS IndexOptions

 , ix.is_disabled 
 , ix.is_primary_key
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 FROM sys.tables t 
 INNER JOIN sys.indexes ix on t.object_id=ix.object_id
 INNER JOIN #FKScripts fks ON t.name = fks.FKTableName
 WHERE ix.type>0 
    AND t.is_ms_shipped=0 
    AND t.name<>'sysdiagrams' 
    --and t.name = 'Entity'
 ORDER BY schema_name(t.schema_id), t.name, ix.name

OPEN CursorIndex
FETCH NEXT FROM CursorIndex INTO  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @is_primary_key, @FileGroupName

WHILE (@@fetch_status=0)
BEGIN
DECLARE @IndexColumns varchar(max)
DECLARE @IncludedColumns varchar(max)

SET @IndexColumns=''
SET @IncludedColumns=''

DECLARE CursorIndexColumn CURSOR FOR 
SELECT 
col.name
,ixc.is_descending_key
,ixc.is_included_column
FROM sys.tables tb 
INNER JOIN sys.indexes ix 
  ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc 
  ON ix.object_id=ixc.object_id 
  AND ix.index_id= ixc.index_id
INNER JOIN sys.columns col 
  ON ixc.object_id = col.object_id
  AND ixc.column_id = col.column_id
WHERE ix.type>0 
  AND (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  AND schema_name(tb.schema_id) = @SchemaName 
  AND tb.name = @TableName 
  AND ix.name = @IndexName
ORDER BY ixc.index_column_id

 OPEN CursorIndexColumn 
 FETCH NEXT FROM CursorIndexColumn INTO  @ColumnName, @IsDescendingKey, @IsIncludedColumn

 WHILE (@@fetch_status=0)
 BEGIN
  IF @IsIncludedColumn=0 
   SET @IndexColumns=@IndexColumns + @ColumnName  + CASE WHEN @IsDescendingKey=1 THEN ' DESC, ' ELSE  ' ASC, ' END
  ELSE 
   SET @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 

  FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
 END

 CLOSE CursorIndexColumn
 DEALLOCATE CursorIndexColumn

 SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 SET @IncludedColumns = CASE WHEN len(@IncludedColumns) >0 THEN substring(@IncludedColumns, 1, len(@IncludedColumns)-1) ELSE '' END
 --  print @IndexColumns
 --  print @IncludedColumns

 SET @TSQLScripCreationIndex =''
 SET @TSQLScripDisableIndex =''
 SET @TSQLScripCreationIndex=
 CASE    WHEN @is_primary_key = 1 THEN 'ALTER TABLE ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY CLUSTERED (' + @IndexColumns + ' )' + @IndexOptions + ' ON ' + QUOTENAME(@FileGroupName) + ';'  
         ELSE 'CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ('+@IndexColumns+') '
 + CASE WHEN len(@IncludedColumns)>0 THEN 'INCLUDE (' + @IncludedColumns+ ')' ELSE '' END + CHAR(13) --+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  
  END

 --if @is_disabled=1 
  SET  @TSQLScripDisableIndex=  CASE WHEN @IndexTypeDesc = 'CLUSTERED' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@IndexName)
                                        ELSE 'DROP INDEX  ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + CHAR(13) END

 PRINT @TSQLScripCreationIndex
 PRINT @TSQLScripDisableIndex

 INSERT INTO #CreateIndexes (SchemaName, TableName, IndexName, is_unique, IndexTypeDesc, is_disabled, is_primary_key ,FileGroupName, TSQLScripCreationIndex, TSQLScripDisableIndex)
 SELECT @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @is_disabled, @is_primary_key, @FileGroupName, @TSQLScripCreationIndex, @TSQLScripDisableIndex

 FETCH NEXT FROM CursorIndex INTO  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @is_primary_key ,@FileGroupName

END
CLOSE CursorIndex
DEALLOCATE CursorIndex

SELECT * FROM #CreateIndexes ORDER BY TableName, IndexName

If you have full text indexes, you will need the following:

/* NEED TO DISABLE FULL TEXT INDEXES ON ANY OF THESE TABLES  - AAARRRGGGGGHHHHHH!!!! */

IF OBJECT_ID('tempdb..#FullTextCreateScripts') IS NOT NULL DROP TABLE #FullTextCreateScripts
CREATE TABLE #FullTextCreateScripts (TableName sysname, ColumnName sysname, FullTextCreateScript nvarchar(max))
INSERT INTO #FullTextCreateScripts (TableName, ColumnName, FullTextCreateScript)
SELECT 
o.name TableName
,c.name ColumnName
,'ADD FULLTEXT INDEX ON ' + o.name + 'ADD (' + c.name + ')' FullTextCreateScript
FROM sys.fulltext_index_columns fic
LEFT JOIN sys.objects o
    ON o.object_id = fic.object_id
LEFT JOIN sys.columns c
    ON c.object_id = fic.object_id
    AND c.column_id = fic.column_id
INNER JOIN #FKScripts fks
    ON o.name = fks.FKTableName


SELECT * FROM #FullTextCreateScripts


IF OBJECT_ID('tempdb..#FullTextDropScripts') IS NOT NULL DROP TABLE #FullTextDropScripts
CREATE TABLE #FullTextDropScripts (TableName sysname, FullTextDropScript nvarchar(max))
INSERT INTO #FullTextDropScripts (TableName, FullTextDropScript)
SELECT DISTINCT
o.name TableName
,'DROP FULLTEXT INDEX ON ' + o.name FullTextDropScript
FROM sys.fulltext_index_columns fic
LEFT JOIN sys.objects o
    ON o.object_id = fic.object_id
INNER JOIN #FKScripts fks
    ON o.name = fks.FKTableName


SELECT * FROM #FullTextDropScripts

/* Another cursor, this one drops our relevant full text indexes */

DECLARE @dropfulltextSQL nvarchar(max)

DECLARE dropfulltextcursor CURSOR LOCAL FOR
    SELECT FullTextDropScript FROM #FullTextDropScripts 

OPEN dropfulltextcursor

FETCH NEXT FROM dropfulltextcursor INTO @dropfulltextSQL

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @dropfulltextSQL

    FETCH NEXT FROM dropfulltextcursor INTO @dropfulltextSQL
END

CLOSE dropfulltextcursor
DEALLOCATE dropfulltextcursor

/ * Drop these indices with the cursor * /

DECLARE @dropindexes nvarchar(max)

DECLARE dropindex CURSOR LOCAL FOR
    SELECT TSQLScripDisableIndex FROM #CreateIndexes 

OPEN dropindex

FETCH NEXT FROM dropindex INTO @dropindexes

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @dropindexes

    FETCH NEXT FROM dropindex INTO @dropindexes
END

CLOSE dropindex
DEALLOCATE dropindex

, SCRIPT

, , , ,

/* */

DECLARE @addfulltextSQL nvarchar(max)

DECLARE createfulltextcursor CURSOR LOCAL FOR
    SELECT FullTextCreateScript FROM #FullTextCreateScripts 

OPEN createfulltextcursor

FETCH NEXT FROM createfulltextcursor INTO @addfulltextSQL

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @addfulltextSQL

    FETCH NEXT FROM createfulltextcursor INTO @addfulltextSQL
END

CLOSE createfulltextcursor
DEALLOCATE createfulltextcursor


/* Rebuild those indexes */


DECLARE @createindexes nvarchar(max)

DECLARE createindexes CURSOR LOCAL FOR
    SELECT TSQLScripCreationIndex FROM #CreateIndexes 

OPEN createindexes

FETCH NEXT FROM createindexes INTO @createindexes

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @createindexes

    FETCH NEXT FROM createindexes INTO @createindexes
END

CLOSE createindexes
DEALLOCATE createindexes




/* Now let re-enable those FK constraints */

DECLARE @enablesql nvarchar(max)
DECLARE @checksql nvarchar(max)

DECLARE enacur CURSOR LOCAL FOR
    SELECT FKRebuildScript, FKCheckScript FROM #FKScripts 

OPEN enacur

FETCH NEXT FROM enacur INTO @enablesql, @checksql

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC sp_executesql @enablesql
    EXEC sp_executesql @checksql

    FETCH NEXT FROM enacur INTO @enablesql, @checkSQL
END

CLOSE enacur
DEALLOCATE enacur





/* Let check that the constraints are now enabled again (for testing) */
SELECT DISTINCT
fkt.*
,CASE WHEN ((C.Status & 0x4000)) = 0 THEN 1 ELSE 0 END [Enabled3]
FROM #FKScripts fkt
INNER JOIN sys.sysobjects o2
    ON o2.name = fkt.PKName
INNER JOIN sys.sysobjects o 
    ON o2.id = o.parent_obj
    AND o.xtype='F'
INNER JOIN sys.sysconstraints c
    ON  o.id = c.constid
+2

- , , BigInt, .

ALTER TABLE Table1 ADD COLUMN NewPKColumn BigInt;
UPDATE Table1 SET NewPkColumn = CurrentPKColumn;

, BigInt, BigInt, .

ALTER TABLE Table2 ADD COLUMN NewPKColumn_FK BigInt;
UPDATE Table2 SET NewPKColumn_FK = Current_FK_Column;
(repeat this for all tables...)

, PK PK (, , , PK - ), FK.

ALTER TABLE Table2 DROP CONSTRAINT FK_Tbl1;
ALTER TABLE Table1 DROP COLUMN CurrentPKColumn;
ALTER TABLE Table2 ADD CONSTRAINT FK_Tbl1 REFERENCES Table1(NewPKColumn);

, , , PK, .

, , , , .

+3

Source: https://habr.com/ru/post/1652165/

More articles:


All Articles