Alter a column’s datatype in your database

Ideally we should set the datatype for the columns in our database correctly from the start of the project. However over time, you may find yourself facing a requirement to change the data type for one of the columns in your database.   If your negotiation skills fail to convince the stakeholder party otherwise, well I guess you need to change it.  To change the datatype for a column in your database use the following as a guide.  For example to change the column called ColumnName from varchar(7) null to varchar(10) not null …


ALTER TABLE dbo.TableName ALTER COLUMN ColumnName varchar(10) not null

But wait, there’s more !  To determine any other varchar(7) columns that could potentially be impacted you could issue a query against your database like the following, then apply similar code above to change those columns …


SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE = 'varchar' and CHARACTER_MAXIMUM_LENGTH = 7

But wait, there’s still more !  If this particular column is involved in any SSIS packages you will need to refactor those packages.   If your SSIS package is stored in the MSDB database you may have some luck performing a SSIS Package Object Search, otherwise there are various 3rd party tools you can use to scan through your SSIS package and allow object searching.  I touched on this in the blog post PopBI DTS SSIS Object Search – there are many tools to facilitate this.

For any cube impacts you can install and then utilise the BIDS helper (Data Type Discrepancy Check).

For subtle data type changes such as the above example, your Reporting Services reports may still run without  error, however you may need to adjust the width of a column for example.

Ok now you can have the steak knives …

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s