- 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
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.
3 comments:
genius
Awesome! Exactly what I needed
Indeed GENIUS
Post a Comment