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
            (
                schema_name,
                table_name,
                column_name,
                datatype_name
            )
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;