How to Change All SQL Columns of One Datatype into Another

How to Change All Sql Columns of One DataType into Another

Here's a working script that uses INFORMATION_SCHEMA.COLUMNS to find all of the *varchar(max) columns and converts them to varchar(255):

declare @schema nvarchar(255)
declare @table nvarchar(255)
declare @col nvarchar(255)
declare @dtype nvarchar(255)
declare @sql nvarchar(max)

declare maxcols cursor for
select
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on
    c.TABLE_CATALOG = t.TABLE_CATALOG
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.TABLE_NAME = t.TABLE_NAME
    and t.TABLE_TYPE = 'BASE TABLE'
where
    c.DATA_TYPE like '%varchar'
    and c.CHARACTER_MAXIMUM_LENGTH = -1

open maxcols

fetch next from maxcols into @schema, @table, @col, @dtype

while @@FETCH_STATUS = 0
begin
    set @sql = 'alter table [' + @schema + '].[' + @table + 
        '] alter column [' + @col + '] ' + @dtype + '(255)'
    exec sp_executesql @sql

    fetch next from maxcols into @schema, @table, @col, @dtype
end

close maxcols
deallocate maxcols

This is about the only use of cursors that I ever condone, but it's a good one. Essentially, it finds all of the *varchar(max), builds the alter statement, and then executes it using sp_executesql.

Enjoy!

Single SQL Query to update datatypes of all columns in a table at one shot

There is no one single "magic" bullet to do this - it's an operation that's rather unusual, so it's not supported natively.

What you can do is iterate over the columns of your table from the system catalog views, create such an ALTER statement on the fly, and also execute it - something like this:

DECLARE AlterTableCursor CURSOR FAST_FORWARD 
FOR
    SELECT 
        AlterCmd = 'ALTER TABLE dbo.YourTableNameHere ALTER COLUMN ' + name + ' VARCHAR(255) NULL'
    FROM 
        sys.columns
    WHERE 
        object_id = OBJECT_ID('dbo.YourTableNameHere')

DECLARE @AlterTableCmd NVARCHAR(200)

OPEN AlterTableCursor

FETCH NEXT FROM AlterTableCursor INTO @AlterTableCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@AlterTableCmd)

    FETCH NEXT FROM AlterTableCursor INTO @AlterTableCmd
END

CLOSE AlterTableCursor
DEALLOCATE AlterTableCursor

Replace the YourTableNameHere with your actual table name - and you should be good to go! Test this on a copy of your live data first !

Alter All Columns datatype in SQL Server

The following code will place a list of columns into a temporary table called @cols, loop through that table, generate an alter table alter column statement, and execute it for each column.

If you need to exclude columns, you should include those in the NOT IN predicate of the select from information_schema.columns.

declare @cols table (i int identity, colname varchar(100))
insert into @cols
select column_name
from information_schema.COLUMNS
where TABLE_NAME = 'yourtable'
and COLUMN_NAME not in ('exclude1', 'exclude2')

declare @i int, @maxi int
select @i = 1, @maxi = MAX(i) from @cols

declare @sql nvarchar(max)

while(@i <= @maxi)
begin
    select @sql = 'alter table yourtable alter column ' + colname + ' decimal(18,4) NULL'
    from @cols
    where i = @i

    exec sp_executesql @sql

    select @i = @i + 1
end

Change all columns in table of a certain data type in PostgreSQL 9.6

You can find all of the columns with the data type that you want to change with a statement like:

select column_name, table_name
from information_schema.columns
where data_type='numeric'
    and numeric_precision = 20
    and numeric_scale = 2;

You can iterate over the result with a custom function or with a DO command such as:

do $$
declare
t record;
begin
    for t IN select column_name, table_name
            from information_schema.columns
            where data_type='numeric'
                and numeric_precision = 20
                and numeric_scale = 2;
    loop
        execute 'alter table ' || t.table_name || ' alter column ' || t.column_name || ' type numeric';
    end loop;
end$$;

Also, to remove trailing zeroes, a more general solution is to cast the value to float or double precision and then back to numeric, e.g:

set code = cast(cast(code as double precision) as numeric);

How do you change the datatype of a column in SQL Server?

ALTER TABLE TableName 
ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]

EDIT
As noted NULL/NOT NULL should have been specified, see Rob's answer as well.

How to change data type of column when columns comes from multiple table?

Hit the below query in SSMS and Paste the result.

SELECT 'ALTER TABLE ' + TABLE_NAME+' ALTER COLUMN POSTCODE INT  
GO
'   FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='POSTCODE'

You will see all alters in one with different Table names.

I have added go Statement for every Alter.

And also take a look at SO Help Center to know how to ask a good question.

How to change column from one data type to antother using script

Ok, this is going to be a beast of a script but I'm working through the same issue at the moment. This script is still a work in progress but should help you with what you'll need to do.

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

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

You're going to need to drop all foreign keys first;

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's 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's 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's 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's 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're going to need to drop 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've got full text indexes then you'll need this;

/* 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

/* Let's drop those indexes using a 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

THIS IS WHERE YOU'LL WANT TO DO YOUR ALTER SCRIPT

Once you've done the alter, then start re-applying everything you've dropped;

/* Let's add those full text indexes back in */

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's 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's 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  

How to change the data type of a column without dropping the column with query?

If ALTER COLUMN doesn't work.

It is not unusual for alter column to fail because it cannot make the transformation you desire. In this case, the solution is to create a dummy table TableName_tmp, copy the data over with your specialized transformation in the bulk Insert command, drop the original table, and rename the tmp table to the original table's name. You'll have to drop and recreate the Foreign key constraints and, for performance, you'll probably want to create keys after filling the tmp table.

Sound like a lot of work? Actually, it isn't.

If you are using SQL Server, you can make the SQL Server Management Studio do the work for you!

• Bring up your table structure (right-click on the table column and select "Modify")
• Make all of your changes (if the column transformation is illegal, just add your new column - you'll patch it up in a moment).
• Right-click on the background of the Modify window and select "Generate Change Script." In the window that appears, you can copy the change script to the clipboard.
• Cancel the Modify (you'll want to test your script, after all) and then paste the script into a new query window.
• Modify as necessary (e.g. add your transformation while removing the field from the tmp table declaration) and you now have the script necessary to make your transformation.

---

Related Topics