SQL Server: Changing Multiple Column Data Types In a Database

One of the projects that I have been working on is migrating an old SQL Server to SQL Server 2014. When I say old I'm talking SQL Server 2000. Does anyone remember DTS packages? Yeah, we got 'em here. Part of the conversion is to use SSIS to do the data transformations that the DTS packages did. An issue that needed to be corrected was that in one of the databases, some of the columns in the tables were of type Float and should have been type Decimal with six digits after the decimal. I figured there had to be a better way than looking at each table and altering each of them one at a time. Manually. Fortunately there was.

 I ended up using the system views SQL Server provides to expose metadata. There are three collections in system views; Information Schema Views, Replication Views, Data-tier Application Views (Transact-SQL). More detailed information can be found at msdn.microsoft.com.

First, I wanted to see how many columns needed to be changed. I knew the column names had either "share" or "unit" in them.


SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE (c.name LIKE '%Share%'
or c.name LIKE '%Units%')

 This was the result of the query:

image001

I knew that the columns that were to be changed had the data type of "float". I added an additional join.


SELECT c.name AS ColName,ty.Name 'Data type', t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN
    sys.types ty ON c.user_type_id = ty.user_type_id
WHERE (c.name LIKE '%Share%'
or c.name LIKE '%Units%')
and ty.name = 'float'

 This is what the query brought back;

image003

From this query I wanted to build my ALTER Table statements to change all of the columns to their correct data type.


Select 'ALTER TABLE [dbo].['  + t.name + '] ALTER COLUMN ['  + c.name + '] DECIMAL(28,6);'
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN
    sys.types ty ON c.user_type_id = ty.user_type_id
WHERE (c.name LIKE '%Share%'
or c.name LIKE '%Units%')
and ty.name = 'float'

 Here are the ALTER statements that were returned:

image004

Finally, I copied the ALTER statements and ran them in a query. Worked like a charm! There may be better ways to do this but its a one and done deal plus it was pretty quick and easy to do. Feel free to leave any comments or suggestions below.

Author: Christian Apostolu
Christian Apostolu's picture
About:

Owner/founder of Crush The Soul, software developer, amateur athlete, frustrated musician. 

"Once we accept our limits, we go beyond them." - Albert Einstein

 

Category: