Yup - but use at own risk:
USE [databasename]
BEGIN TRANSACTION
CREATE TABLE #references (
row_id int IDENTITY(1, 1),
ref_table_schema VARCHAR(MAX),
ref_table VARCHAR(MAX),
ref_column VARCHAR(MAX)
)
CREATE TABLE #indexes (
row_id int IDENTITY(1, 1),
table_schema NVARCHAR(255),
table_name NVARCHAR(MAX),
table_columns NVARCHAR(MAX),
table_included_columns NVARCHAR(MAX),
idx_name NVARCHAR(255),
idx_type_desc NVARCHAR(MAX),
idx_is_unique BIT,
idx_ignore_dup_key BIT,
idx_is_primary_key BIT,
idx_is_unique_constraint BIT,
idx_fill_factor TINYINT,
idx_is_padded BIT,
idx_is_disabled BIT,
idx_allow_row_locks BIT,
idx_allow_page_locks BIT,
idx_has_filter BIT,
idx_filter_definition NVARCHAR(MAX)
)
CREATE TABLE #foreignKeys (
row_id int IDENTITY(1, 1),
table_schema NVARCHAR(255),
table_name NVARCHAR(MAX),
table_columns NVARCHAR(MAX),
table_included_columns NVARCHAR(MAX),
fk_schema NVARCHAR(max),
fk_name NVARCHAR(MAX),
fk_сheck_сlause NVARCHAR(MAX)
)
DECLARE
@TargetTableSchema NVARCHAR(255),
@TargetTable NVARCHAR(max),
@TargetColumn NVARCHAR(max),
@TargetColumnType NVARCHAR(max),
@RefTableSchema NVARCHAR(MAX),
@RefTable NVARCHAR(max),
@RefColumn NVARCHAR(max),
@Id INT,
@Schema NVARCHAR(MAX),
@Name NVARCHAR(MAX),
@Columns NVARCHAR(MAX),
@IncludedColumns NVARCHAR(max),
@UseCheck NVARCHAR(max),
@CheckClause NVARCHAR(max),
@Unique NVARCHAR(255),
@IsUnique BIT,
@TypeDesc NVARCHAR(max),
@IgnoreDupKey BIT,
@IsPrimaryKey BIT,
@IsUniqueConstraint BIT,
@FillFactor TINYINT,
@IsPadded BIT,
@IsDisabled BIT,
@AllowRowLocks BIT,
@AllowPageLocks BIT,
@HasFilter BIT,
@FilterDefinition NVARCHAR(max),
@Query NVARCHAR(max),
@NumberRecords INT,
@RowCount INT
-- CONSTANTS
SET @TargetTableSchema = 'dbo'
SET @TargetColumnType = 'NVARCHAR(100) NOT NULL'
SET @TargetTable='EcomProducts'
SET @TargetColumn='ProductID'
-- CHANGE DEPENDEND COLUMNS
-- GET ALL REFERENCES BY COLUMN NAME
INSERT INTO #references (ref_table_schema, ref_table, ref_column)
SELECT
SCHEMA_NAME(schema_id) AS ref_table_schema,
t.name AS ref_table,
c.name AS ref_column
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%' + @TargetColumn
--AND t.name <> @TargetTable
ORDER BY ref_table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
-- ITERATE TEMP TABLE
PRINT 'START CHANGING REFERENCES...'
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT
@RefTableSchema = ref_table_schema,
@RefTable = ref_table,
@RefColumn = ref_column
FROM #references
WHERE row_id = @RowCount
--DEBUG
PRINT ''
PRINT 'ROW #' + CAST(@RowCount AS NVARCHAR(255))
PRINT 'SCHEMA: ' + @RefTableSchema
PRINT 'TABLE: ' + @RefTable
PRINT 'COLUMN: ' + @RefColumn
PRINT ''
-- CREATE BACKUP FOR INDEX
DECLARE indexcursor CURSOR FOR
SELECT i.index_id,
i.name,
i.is_unique,
i.is_unique_constraint,
i.type_desc,
i.ignore_dup_key,
i.is_primary_key,
i.fill_factor,
i.is_padded,
i.is_disabled,
i.allow_row_locks,
i.allow_page_locks,
i.has_filter,
i.filter_definition
FROM sys.indexes as i
WHERE object_id = object_id('[' + @RefTableSchema + '].[' + @RefTable + ']')
OPEN indexcursor;
FETCH NEXT FROM indexcursor
INTO @Id, @Name, @IsUnique, @IsUniqueConstraint, @TypeDesc, @IgnoreDupKey, @IsPrimaryKey, @FillFactor, @IsPadded, @IsDisabled, @AllowRowLocks, @AllowPageLocks, @HasFilter, @FilterDefinition;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT '|---------------------------------------|'
PRINT '|START SEARCHING AND REMOVING INDEXES...|'
PRINT '|---------------------------------------|'
PRINT ''
SET @Columns = ''
SET @IncludedColumns = ''
SELECT @Columns = @Columns + '[' + c.name + '] ' + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END + ',' from sys.index_columns ic
INNER JOIN sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
WHERE index_id = @Id and ic.object_id = object_id('[' + @TargetTableSchema + '].[' + @RefTable + ']') and key_ordinal > 0
ORDER BY index_column_id
SELECT @IncludedColumns = @IncludedColumns + '[' + c.name + '],' from sys.index_columns ic
INNER JOIN sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
WHERE index_id = @Id and ic.object_id = object_id('[' + @TargetTableSchema + '].[' + @RefTable + ']') and key_ordinal = 0
ORDER BY index_column_id
--DEBUG
PRINT 'ID: ' + CAST(@Id AS NVARCHAR(255))
PRINT 'INDEX: ' + @Name
PRINT 'INDEX FOR COLUMN: ' + @RefColumn + ' IN TABLE: ' + @RefTable
PRINT 'INDEX COLUMNS: ' + @Columns
PRINT 'INDEX INCLUDED COLUMNS: ' + @IncludedColumns
PRINT 'INDEX IS UNIQUE: ' + CAST(@IsUnique AS CHAR)
PRINT 'INDEX IS UNIQUE CONSTRAINT: ' + CAST(@IsUniqueConstraint AS CHAR)
PRINT 'INDEX IS UNIQUE CONSTRAINT: ' + CAST(@IsUniqueConstraint AS CHAR)
PRINT 'INDEX TYPE DESC: ' + @TypeDesc
PRINT 'INDEX IGNORE DUP KEY: ' + CAST(@IgnoreDupKey AS CHAR)
PRINT 'INDEX IS PRIMARY KEY: ' + CAST(@IsPrimaryKey AS CHAR)
PRINT 'INDEX FILL FACTOR: ' + CAST(@FillFactor AS NVARCHAR(255))
PRINT 'INDEX IS PADDED: ' + CAST(@IsPadded AS CHAR)
PRINT 'INDEX ALLOW ROW LOCKS: ' + CAST(@AllowRowLocks AS CHAR)
PRINT 'INDEX ALLOW PAGE LOCKS: ' + CAST(@AllowPageLocks AS CHAR)
PRINT 'INDEX HAS FILTER: ' + CAST(@HasFilter AS CHAR)
PRINT 'INDEX FILTER DEFINITION: ' + @FilterDefinition
-- CHECK IF REF COLUMN IS WITHING INDEX
If @Columns LIKE '%' + @RefColumn + '%'
BEGIN
PRINT 'REMOVING INDEX...'
IF LEN(@Columns) > 0
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)
IF LEN(@IncludedColumns) > 0
SET @IncludedColumns = LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1)
INSERT INTO #indexes (
table_schema,
table_name,
table_columns,
table_included_columns,
idx_name,
idx_type_desc,
idx_is_unique,
idx_ignore_dup_key,
idx_is_primary_key,
idx_is_unique_constraint,
idx_fill_factor,
idx_is_padded,
idx_is_disabled,
idx_allow_row_locks,
idx_allow_page_locks,
idx_has_filter,
idx_filter_definition)
VALUES (
@RefTableSchema,
@RefTable,
@Columns,
@IncludedColumns,
@Name,
@TypeDesc,
@IsUnique,
@IgnoreDupKey,
@IsPrimaryKey,
@IsUniqueConstraint,
@FillFactor,
@IsPadded,
@IsDisabled,
@AllowRowLocks,
@AllowPageLocks,
@HasFilter,
@FilterDefinition)
IF @IsPrimaryKey = 1
SET @Query = 'ALTER TABLE [' + @RefTableSchema + '].[' + @RefTable + '] DROP CONSTRAINT [' + @Name + ']'
ELSE
SET @Query = 'DROP INDEX [' + @Name + '] ON [' + @RefTableSchema + '].[' + @RefTable + ']'
PRINT 'QUERY: ' + @Query
EXEC(@Query);
END
FETCH NEXT FROM indexcursor INTO @Id, @Name, @IsUnique, @IsUniqueConstraint, @TypeDesc, @IgnoreDupKey, @IsPrimaryKey, @FillFactor, @IsPadded, @IsDisabled, @AllowRowLocks, @AllowPageLocks, @HasFilter, @FilterDefinition;
END;
CLOSE indexcursor;
DEALLOCATE indexcursor;
-- CREATE BACKUP FOR CONSTRAINTS
DECLARE constraintcursor CURSOR FOR
SELECT tu.CONSTRAINT_NAME, cc.CHECK_CLAUSE, tu.CONSTRAINT_SCHEMA from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE tu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON cc.CONSTRAINT_SCHEMA = tu.TABLE_SCHEMA AND cc.CONSTRAINT_NAME = tu.CONSTRAINT_NAME
WHERE tu.TABLE_SCHEMA = @TargetTableSchema AND tu.TABLE_NAME = @RefTable AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
OPEN constraintcursor;
FETCH NEXT FROM constraintcursor INTO @Name, @CheckClause, @Schema;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT '|--------------------------------------------|'
PRINT '|START SEARCHING AND REMOVING FOREIGN KEYS...|'
PRINT '|--------------------------------------------|'
PRINT ''
SET @Columns = ''
SET @IncludedColumns = ''
SELECT @Columns = @Columns + '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @RefTable and TABLE_SCHEMA = @TargetTableSchema AND CONSTRAINT_SCHEMA = @Schema AND CONSTRAINT_NAME= @Name
ORDER BY ORDINAL_POSITION
SELECT @IncludedColumns = @IncludedColumns + '[' + c.name + '],' from sys.foreign_key_columns AS fk
INNER JOIN sys.columns AS c on c.column_id = fk.constraint_column_id and c.object_id = fk.referenced_object_id
WHERE fk.constraint_object_id = object_id('[' + @Schema +'].[' + @Name +']') AND fk.parent_object_id = object_id('[' + @TargetTableSchema +'].[' + @RefTable + ']')
ORDER BY fk.constraint_column_id
--DEBUG
PRINT 'FOREIGN KEY: ' + @Name
PRINT 'FOREIGN KEY FOR COLUMN: ' + @RefColumn + ' IN TABLE: ' + @RefTable
PRINT 'FOREIGN KEY COLUMNS: ' + @Columns
PRINT 'FOREIGN KEY INCLUDED COLUMNS: ' + @IncludedColumns
If @Columns LIKE '%' + @RefColumn + '%' AND @IncludedColumns LIKE '%' + @TargetColumn + '%'
BEGIN
PRINT 'REMOVING CONSTRAINT...'
IF LEN(@Columns) > 0
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)
IF LEN(@IncludedColumns) > 0
SET @IncludedColumns = LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1)
INSERT INTO #foreignKeys(table_schema, table_name, table_columns, table_included_columns, fk_schema, fk_name, fk_сheck_сlause) VALUES (@RefTableSchema, @RefTable, @Columns, @IncludedColumns, @Schema, @Name, @CheckClause)
SET @Query = 'ALTER TABLE [' + @TargetTableSchema + '].[' + @RefTable + '] DROP CONSTRAINT [' + @Name + ']'
PRINT 'QUERY: ' + @Query
EXEC(@Query);
END
FETCH NEXT FROM constraintcursor INTO @Name, @CheckClause, @Schema;
END;
CLOSE constraintcursor;
DEALLOCATE constraintcursor;
-- CHANGE REF COLUMN
PRINT ''
PRINT '|------------------------------------|'
PRINT '|START CHANGING TYPE OF REF COLUMN...|'
PRINT '|------------------------------------|'
PRINT ''
BEGIN
SET @Query = 'ALTER TABLE [' + @RefTableSchema + '].[' + @RefTable + '] ALTER COLUMN [' + @RefColumn + '] ' + @TargetColumnType
PRINT 'QUERY: ' + @Query
EXEC(@Query);
SET @RowCount = @RowCount + 1
END
END
-- CHANGE COLUMN
BEGIN
PRINT ''
PRINT '|---------------------------------------|'
PRINT '|START CHANGING TYPE OF TARGET COLUMN...|'
PRINT '|---------------------------------------|'
PRINT ''
SET @Query = 'ALTER TABLE [' + @TargetTableSchema + '].[' + @TargetTable + '] ALTER COLUMN [' + @TargetColumn + '] ' + @TargetColumnType
PRINT 'QUERY: ' + @Query
EXEC(@Query)
END
--RESTORE INDEXES
PRINT ''
PRINT '|--------------------------|'
PRINT '|START RESTORING INDEXES...|'
PRINT '|--------------------------|'
PRINT ''
DECLARE @Options NVARCHAR(MAX)
SELECT @NumberRecords = COUNT(*) FROM #indexes
SET @RowCount = 1
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT
@Schema = table_schema,
@RefTable = table_name,
@Columns = table_columns,
@IncludedColumns = table_included_columns,
@Name = idx_name,
@IsUniqueConstraint = idx_is_unique_constraint,
@IsUnique = idx_is_unique,
@FilterDefinition = idx_filter_definition,
@TypeDesc = idx_type_desc,
@IsPadded = idx_is_padded,
@IgnoreDupKey = idx_ignore_dup_key,
@AllowPageLocks = idx_allow_page_locks,
@AllowRowLocks = idx_allow_row_locks,
@IsPrimaryKey = idx_is_primary_key
FROM #indexes
WHERE row_id = @RowCount
PRINT ''
PRINT 'ROW #' + CAST(@RowCount AS NVARCHAR(255))
PRINT 'Schema: ' + @Schema
PRINT 'Ref table: ' + @RefTable
PRINT 'Columns: ' + @Columns
PRINT 'Included columns: ' + @IncludedColumns
PRINT 'Name: ' + @Name
PRINT 'Is primary key: ' + CAST(@IsPrimaryKey AS CHAR)
PRINT 'Is unique constraint: ' + CAST(@IsUniqueConstraint AS CHAR)
PRINT 'Is unique: ' + CAST(@IsUnique AS CHAR)
PRINT 'Filter definition: ' + @FilterDefinition
SET @Unique = CASE WHEN @IsUnique = 1 THEN ' UNIQUE ' ELSE '' END
IF LEN(@IncludedColumns) > 0
SET @IncludedColumns = ' INCLUDE (' + @IncludedColumns + ')'
IF @FilterDefinition IS NULL
SET @FilterDefinition = ''
ELSE
SET @FilterDefinition = ' WHERE ' + @FilterDefinition + ' '
SET @Options = 'WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF'
IF @IsPrimaryKey = 0
BEGIN
SET @Query = 'CREATE ' + @Unique + ' ' + @TypeDesc + ' INDEX [' + @Name + '] ON [' + @Schema + '].[' + @RefTable + '] (' + @Columns + ')' + @IncludedColumns + @FilterDefinition
SET @Options = @Options + ', DROP_EXISTING = OFF'
END
ELSE
BEGIN
SET @Query = 'ALTER TABLE [' + @Schema + '].[' + @RefTable + '] ADD CONSTRAINT [' + @Name + '] PRIMARY KEY ' + @TypeDesc + ' (' + @Columns + ')'
IF @IgnoreDupKey = 0
SET @Options = @Options + ', IGNORE_DUP_KEY = OFF'
ELSE
SET @Options = @Options + ', IGNORE_DUP_KEY = ON'
END
IF @IsPadded = 0
SET @Options = @Options + ', PAD_INDEX = OFF'
ELSE
SET @Options = @Options + ', PAD_INDEX = ON'
IF @AllowRowLocks = 0
SET @Options = @Options + ', ALLOW_ROW_LOCKS = OFF'
ELSE
SET @Options = @Options + ', ALLOW_ROW_LOCKS = ON'
IF @AllowPageLocks = 0
SET @Options = @Options + ', ALLOW_PAGE_LOCKS = OFF'
ELSE
SET @Options = @Options + ', ALLOW_PAGE_LOCKS = ON'
SET @Options = @Options + ') ON [PRIMARY]'
SET @Query = @Query + ' ' + @Options
PRINT 'QUERY: ' + @Query
BEGIN
EXEC(@Query)
END
SET @RowCount = @RowCount + 1
END
--RESTORE CONSTRAINTS
PRINT ''
PRINT '|-------------------------------|'
PRINT '|START RESTORING FOREIGN KEYS...|'
PRINT '|-------------------------------|'
PRINT ''
SELECT @NumberRecords = COUNT(*) FROM #foreignKeys
SET @RowCount = 1
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT
@RefTableSchema = table_schema,
@RefTable = table_name,
@Columns = table_columns,
@IncludedColumns = table_included_columns,
@Schema = fk_schema,
@Name = fk_name,
@CheckClause = fk_сheck_сlause
FROM #foreignKeys
WHERE row_id = @RowCount
PRINT ''
PRINT 'ROW #' + CAST(@RowCount AS NVARCHAR(255))
PRINT 'Ref table schema: ' + @RefTableSchema
PRINT 'Ref table: ' + @RefTable
PRINT 'Columns: ' + @Columns
PRINT 'FK schema: ' + @Schema
PRINT 'FK name: ' + @Name
PRINT 'FK check clause' + @CheckClause
IF @CheckClause IS NULL
SET @CheckClause = 'NOCHECK'
SET @Query = 'ALTER TABLE [' + @RefTableSchema + '].[' + @RefTable + '] WITH ' + @CheckClause + ' ADD CONSTRAINT [' + @Name + '] FOREIGN KEY (' + @Columns + ') REFERENCES [' + @TargetTableSchema + '].[' + @TargetTable + ']('+@IncludedColumns+')'
PRINT 'QUERY: ' + @Query
BEGIN
EXEC(@Query)
END
SET @Query = 'ALTER TABLE [' + @RefTableSchema + '].[' + @RefTable + '] ' + @CheckClause + ' CONSTRAINT [' + @Name + ']'
PRINT 'QUERY: ' + @Query
BEGIN
EXEC(@Query)
END
SET @RowCount = @RowCount + 1
END
-- REMOVE TEMP TABLES
BEGIN
DROP TABLE #references
DROP TABLE #indexes
DROP TABLE #foreignKeys
END
COMMIT TRANSACTION
This is a general update script for upcoming DW 8.5.
/Snedker
