sp_AlterColumn


I have finally decided to share my latest version of sp_AlterColumn. As the name implies, this is a procedure which 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).


Download helper objects and sp_AlterColumn.
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;