Alter a column with dependencies in your SQL Server database with sp_alter_column!
Available on Github here https://github.com/segovoni/sp_alter_column
Introduction
It could has happened to you to change the data type or the name of a column and be faced with the error message 5074 which indicates that it is impossible to modify the column due to the presence of linked objects such as Primary Keys, Foreign Keys, Indexes, Constraints, Statistics and so on.
This is the error you probably faced on:
Msg 5074, Level 16, State 1, Line 1135 — The object ‘objectname’ is dependent on column ‘columnname’.
Msg 4922, Level 16, State 9, Line 1135 — ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.
Just changing the name is not a trivial operation especially when the column is referenced in others database objects like Views, Indexes, Statistics etc. To rename a column in a table, there is the sp_rename system Stored Procedure, but for changing the data type of the column, if you don’t want to use any third-party tools, you have no other option than to manually write T-SQL code to do that.
How had you solved the problem?
Some of you have probably deleted manually the linked objects, next you have changed the data type of the column, the size where expected or the properties and then you have recreated the previously deleted objects manually. You have been very careful to not change the properties of the objects themselves during DROP and CREATE operations.
I faced several times this issue, so I decided to create a Stored Procedure that is able to compose automatically the appropriate DROP and CREATE commands for each object connected to the column I want to modify. Thus was born the sp_alter_column Stored Procedure which is now available on my GitHub repository here: https://github.com/segovoni/sp_alter_column.
The first version of the sp_alter_column provides six input parameters:
- schemaname represents the name of the schema in which the table exists
- tablename represents the name of the table where the column you want to modify resides
- columnname represents the name of the column you want to change
- columnrename represents the new name that you want to assign to the column (by default, columnrename is assigned equal to columnname)
- datatype represents the new data type to assign to the column
- executionmode represents the execution mode (default Zero)
How the sp_alter_column works
After the input parameters has been checked, sp_alter_column identifies objects that depend on the column you want to modify and, based on the type of the object, it generates the appropriate DROP and CREATE T-SQL commands for the following execution. All the T-SQL commands composed automatically are stored in a temporary table managed by the stored procedure.
sp_alter_column is able to identify and generate, for the identified objects, the DROP/CREATE commands for the following database objects (which may have dependencies with a column):
- Primary keys
- Foreign keys
- Default constraints
- Unique constraints
- Check constraints
- Indexes
- Statistics
- Views
The parameter executionmode defines the execution mode of the Stored Procedure. The value Zero (default, preview mode) indicates that DROP/CREATE commands will not have to be applied, they will be only displayed as output. The sp_alter_column will display only the T-SQL commands needed to change the data type of the column or its name. This execution mode is particularly suitable for becoming familiar with the Stored Procedure or when you want to have more control over the commands that will be executed, leaving to the sp_alter_column only the job of generating them. The value One (executive mode) given to the parameter executionmode indicates to the Stored Procedure that caller wants to apply the T-SQL commands needed to change the data type of the column or its name. Changes will be performed within an explicit transaction; the Commit will be applied at the end of all operations if all of them has been executed successfully. If something goes wrong a Rollback will be executed.
How to debug the sp_alter_column stored procedure
The most important programming languages have debugging tools integrated into the development tool. Debugger usually has a graphic interface that allows you to inspect the variables values and other things at run-time to analyze source code and program flow row-by-row and finally the debugger allows you to manage breakpoints.
Each developer loves debugging tools because they are very useful when a program fails in a calculation or when it runs into an error. Now, think about our Stored Procedure that performs complex operations silently and suppose that it runs into a problem; probably, this question comes to your mind: “Can I debug a Stored Procedure?” and if it is possible, “How can I do that?”
Debugging a stored procedure is possible with Microsoft Visual Studio development tool.
The first step to debug a stored procedure is to write the EXECUTE (EXEC) command that is able to execute the stored procedure taking care to assign to the parameters the values that can reproduce the issue or the case you want to analyze with the support of the debugger.
The second step consists in the execution of the stored procedure through Microsoft Visual Studio.
Open Microsoft Visual Studio and surf into SQL Server Object Explorer, open the database tree, expand “Programmability” folder and try to find out the sp_alter_column into “Stored Procedures” folder. Next, press right click on the Stored Procedure, a context pop-up menu will appear and when you select the item “Debug Procedure…”, a new SQL Query page will be open and it will be ready to debug the Stored Procedure.
The execution pointer will be placed on the first statement of the T-SQL script generated by default from the debugger. Replace the EXEC command with the one prepared previously, taking care to assign the return value of the Stored Procedure to the variable return_value.
Using the “step into” debugger function (F11) you can execute the script step-by-step up to the EXEC statement that will fire the sp_alter_column. If you press step into button (F11) when the execution pointer is on the EXEC statement, the execution pointer will jump into the Stored Procedure, on the first executable statement and the fun can begin!
Summary
According to the conversion rules between data types described in this article and shown in the following picture, the sp_alter_column stored procedure allows you to easily modify the data type of a column or its name, try it!
Report any improvements (there are a couple very obviously which I will do soon) and if you want to contribute to the development on Github, you are very welcome!
Enjoy the sp_alter_column!
