Good books to read…

create table #pks
(
dbname sysname,
tableowner sysname,
tablename sysname,
columnname sysname,
keyseq int,
pkname sysname
)
create table #fks
(
pk_dbname sysname,
pk_tableowner sysname,
pk_tablename sysname,
pk_colname sysname,
fk_dbname sysname,
fk_tableowner sysname,
fk_tablename sysname,
fk_columnname sysname,
keyseq int,
updaterule int,
deleterule int,
fkname sysname,
pkname sysname,
deferrability int
)

create table #recreate_constraints
(
command varchar(512)
)

declare table_cursor cursor for
SELECT databasename,tablename,columnname from dbo.FieldsToChange order by databasename,prioritylevel
open table_cursor
declare @tablename sysname
DECLARE @pkname sysname
declare @pk_column sysname
declare @fkname sysname
declare @fktablename sysname
declare @fkcolumnname sysname
declare @pktablename sysname
declare @pkcolumnname sysname
declare @columnname sysname
declare @databasename sysname
declare @sSQL varchar(256)
FETCH NEXT FROM table_cursor INTO @databasename,@tablename,@columnname

WHILE @@FETCH_STATUS = 0
BEGIN
truncate table #pks
truncate table #fks
SET @pkname = ''
declare @pks_threepart varchar(256)
declare @fks_threepart varchar(256)
SET @pks_threepart = @databasename + '.dbo.sp_pkeys @table_name = ' + @tablename
SET @fks_threepart = @databasename + '.dbo.sp_fkeys @pktable_name = ' + @tablename
insert into #pks exec (@pks_threepart)
insert into #fks exec (@fks_threepart)

DECLARE fk_cursor CURSOR FOR SELECT fkname,fk_tablename,pk_tablename,pk_colname,fk_columnname FROM #fks
OPEN fk_cursor
FETCH NEXT FROM fk_cursor INTO @fkname,@fktablename,@pktablename,@pkcolumnname,@fkcolumnname
WHILE @@FETCH_STATUS = 0
BEGIN
-- DROP CONSTRAINT
if @pkcolumnname = @columnname OR @fkcolumnname = @columnname
BEGIN
SET @sSQL = 'ALTER TABLE ' + @databasename + '.dbo.' + @fktablename + ' DROP CONSTRAINT ' + @fkname
print @sSQL
END
FETCH NEXT FROM fk_cursor INTO @fkname,@fktablename,@pktablename,@pkcolumnname,@fkcolumnname
END
CLOSE fk_cursor

SELECT @pkname = pkname,@pk_column = columnname from #pks
if @pkname <> '' AND @pk_column = @columnname
BEGIN
-- DROP CONSTRAINT
SET @sSQL = 'ALTER TABLE ' + @databasename + '.dbo.' + @tablename + ' DROP CONSTRAINT ' + @pkname
print @sSQL

END

SET @sSQL = 'ALTER TABLE ' + @databasename + '.dbo.' + @tablename + ' ALTER COLUMN ' + @columnname + ' ' + @newdatatype
if @pkname <> '' AND @pk_column = @columnname
BEGIN
SET @sSQL = @sSQL + ' NOT NULL'
END
print @sSQL

IF @pkname <> '' AND @pk_column = @columnname
BEGIN
SET @sSQL = 'ALTER TABLE ' + @databasename + '.dbo.' + @tablename + ' ADD CONSTRAINT ' + @pkname + ' PRIMARY KEY CLUSTERED (' + @pk_column + ')'
print @sSQL
END

OPEN fk_cursor
FETCH NEXT FROM fk_cursor INTO @fkname,@fktablename,@pktablename,@pkcolumnname,@fkcolumnname
WHILE @@FETCH_STATUS = 0
BEGIN
-- ADD CONSTRAINT
if @pkcolumnname = @columnname OR @fkcolumnname = @columnname
BEGIN
SET @sSQL = 'ALTER TABLE ' + @databasename + '.dbo.' + @fktablename + ' ADD CONSTRAINT ' + @fkname + ' FOREIGN KEY (' + @fkcolumnname + ')
REFERENCES ' + @databasename + '.dbo.' + @pktablename + ' (' + @pkcolumnname + ')'
insert #recreate_constraints (command) VALUES (@sSQL)
END
FETCH NEXT FROM fk_cursor INTO @fkname,@fktablename,@pktablename,@pkcolumnname,@fkcolumnname
END
CLOSE fk_cursor

DEALLOCATE fk_cursor
FETCH NEXT FROM table_cursor INTO @databasename,@tablename,@columnname
END

CLOSE table_cursor
DEALLOCATE table_cursor

drop table #pks
drop table #fks

declare recreate_fks cursor for SELECT command FROM #recreate_constraints
open recreate_fks
declare @command1 varchar(512)
fetch next from recreate_fks into @command1
while @@FETCH_STATUS = 0
begin
print @command1
fetch next from recreate_fks into @command1
END