I have finally decided to share my latest version of sp_AlterColumn. As the name implies, this is a procedure that helps developers and DBAs to alter columns in many different ways.

The problem sp_AlterColumn solves is when you need to change a datatype (for example INT to BIGINT) and there is a lot of foreign keys, indexes, computed columns, check constraints, legacy data type rules to mention a few, that prevent you from doing a single ALTER COLUMN.

sp_AlterColumn will find all graphs, ie all connected columns using foreign keys. It will find all problematic objects and ultimately create a number of T-SQL statements that are processed in the correct order to do your changes.

sp_AlterColumn will also automatically change deprecated datatypes (image, text and ntext) to modern datatypes such as varbinary(max), varchar(max) and nvarchar(max).

The files are available on Github. Latest version is v5.0.0

Please email atac@sqltopia.com if you have suggestions for improvements or bug fixes.

Here is an example of how you set a configuration for sp_AlterColumn.

INSERT      dbo.atac_configuration
SELECT sch.name AS schema_name,
tbl.name AS table_name,
col.name AS column_name,
N'bigint' AS datatype_name
FROM sys.columns AS col
INNER JOIN sys.types AS typ ON typ.user_type_id = col.user_type_id
AND typ.name IN (N'tinyint', N'smallint', N'int', N'bigint')
INNER JOIN sys.tables AS tbl ON tbl.object_id = col.object_id
INNER JOIN sys.schemas AS sch ON sch.schema_id = tbl.schema_id
WHERE col.name = N'refid';

EXEC dbo.sp_AlterColumn;