SQL Server ALTER COLUMN with sqlcmdcli
It alters a column with dependencies in your SQL Server database!
How many times have you had to change the data type of a column that other SQL Server database objects depend on?
If you had this need, you will faced the error message 5074 which signals the impossibility of changing the data type and properties of a column due to the presence of linked objects such as Indexes, Constraints, Statistics and so on.
You will find dozens of posts on the net describing the error message 5074 but few provide a solution other than manually deleting linked objects before running the ALTER COLUMN command.
The error message 5074 appears like this:
Msg 5074, Level 16, State 1, Line 1135The object 'Object Name' is dependent on column 'Column Name'.
The error message 5074 will be followed by error message 4922 as in this example:
Msg 4922, Level 16, State 9, Line 1135ALTER TABLE ALTER COLUMN 'Column Name' 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.
I faced several times this issue, so I decided to create a 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 fully integrated in the command line application sqlcmdcli.
sqlcmdcli is a command-line interface for ad hoc, interactive execution of commands on SQL Server that allows you to perform specific operations including changing the data type of a column with dependencies within a SQL Server database. The specific command altercolumn 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
Example
If you want to change the data type of the Revision column of the Production.Document table in the AdventureWorks2017 database, from nchar(5) to nvarchar(10) with the following TSQL command:
ALTER TABLE Production.Document ALTER COLUMN Revision NVARCHAR(10) NOT NULLYou will receive the following error messages:
Msg 5074, Level 16, State 1, Line 1136The index 'IX_Document_FileName_Revision' is dependent on column 'Revision'.Msg 4922, Level 16, State 9, Line 1136ALTER TABLE ALTER COLUMN Revision failed because one or more objects access this column.
The sqlcmdcli altercolumn command will perform the operation without errors, below is an example of the command line:
sqlcmdcli.exe altercolumn -servername:SSS -databasename:DDD -username:UU -password:PP -schemaname:Production -tablename:Document -columnname:Revision -datatype:nvarchar(10)The latest version of sqlcmdcli is available for download here:
The available commands of sqlcmdcli are documented on the project’s wiki page here:
Summary
According to the conversion rules between data types described in this article and shown in the following picture, the sqlcmdcli allows you to easily modify the data type of a column or its name, try it!
Enjoy the sqlcmdcli!
