Changing all columns of a particular data type to another in SQL Server

• Your app is a big hit and you want to change int to bigint so you can accomodate a quintillion more records (Congratulations, but you should look into sharding your tables)
• You thought uniqueidentifiers would be great for indexing and peppered them all across your db
• You thought using SQLServer MONEY was a damn good idea
• You think converting varchar to nvarchar is all it takes to sell your product in China

Apart from these lame examples, there are many valid reasons for you to change all columns of a particular datatype into another. Here is a handy snippet that enumerates a list of columns in SQLServer of a particular datatype.

The example lists columns whose datatype is datetime

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],   
        T.[name] AS [table_name], C.[name] AS [column_name],   
        DTY.[name] AS system_data_type, C.[is_nullable]         
FROM sys.[tables] AS T   
  INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]  
 INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id]   
WHERE T.[is_ms_shipped] = 0  AND DTY.name = 'datetime'
ORDER BY T.[name], C.[column_id]

Generating the update code
A simple addition to the above code is all it takes to generate the update statements.

   'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + ' alter column '  + C.[name] + ' smalldatetime ' + case when C.[is_nullable] = 0 then ' not null ' else ' null ' end

Add this to the code above and you get -

SELECT 'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + ' alter column '  + C.[name] + ' smalldatetime ' + case when C.[is_nullable] = 0 then ' 

not null ' else ' null ' end   
FROM sys.[tables] AS T   
  INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]  
 INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id]   
WHERE T.[is_ms_shipped] = 0  AND DTY.name = 'datetime'
ORDER BY T.[name], C.[column_id]

The above code generates alter statements that convert all datetime columns into smalldatetime and displays it as the query results.