Last active
September 17, 2024 14:03
-
-
Save tcartwright/4c6c07711e255136d1ad697eefc40289 to your computer and use it in GitHub Desktop.
SQL SERVER: Converts sql server datetime and datetime2 columns to datetimeoffset
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Clear-Host | |
| $module = Get-Module tcdbtools | |
| if (!$module) { | |
| Install-Module tcdbtools -Force | |
| } | |
| Import-Module tcdbtools | |
| $module = Get-Module SqlServer | |
| if (!$module) { | |
| Install-Module SqlServer -Force | |
| } | |
| Import-Module SqlServer | |
| #get-command -Module sqlserver | |
| <# *************************************************************************** | |
| **** CHANGE THESE VARIABLES TO POINT AT THE TARGET SERVER / DATABASE | |
| *************************************************************************** #> | |
| $server = "server.fabrikam.com" | |
| $database = "databaseName" | |
| <# Comment out $creds if wanting to use trusted #> | |
| $creds = Get-DBUserCredential -ApplicationName "MyServerCreds" | |
| <# | |
| # Run this line to add user credentials after changing all of the values, or add the credential manually in the Credential Manager | |
| # Do not save password into this script file. :D | |
| Set-DBUserCredential -ApplicationName "MyServerCreds" -UserName "UserName" -Password "Password" | |
| #> | |
| $handler = [Microsoft.Data.SqlClient.SqlInfoMessageEventHandler] { | |
| param($sender, $event) | |
| if ($event.Message -inotmatch "Changed database context to") { | |
| Write-Host $event.Message | |
| } | |
| }; | |
| if ($creds) { | |
| $ServerConnection = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($server, $creds.UserName, $creds.Password) | |
| $ServerConnection.DatabaseName = $database | |
| } else { | |
| $ServerConnection = [Microsoft.SqlServer.Management.Common.ServerConnection]::new() | |
| $ServerConnection.ConnectionString = "data source=$server;initial catalog=$database;trusted_connection=true;encrypt=optional;" | |
| } | |
| $ServerConnection.add_InfoMessage($handler); | |
| $serverInstance = [Microsoft.SqlServer.Management.Smo.Server]::new($ServerConnection) | |
| $db = $serverInstance.Databases[$database] | |
| # this is slow as crap.... much faster to use the query | |
| #$columns = $db.Tables.Columns | |
| #$dateColumns = $columns | | |
| # Where-Object { $_.Datatype.SqlDataType -ieq "datetime" -or $_.Datatype.SqlDataType -ieq "datetime2" } | | |
| # Sort-Object -Property @{Expression={ [string]$_.Parent.Schema }}, @{Expression={ [string]$_.Parent.Name }} | |
| $query = " | |
| SELECT [fn].[schema_name], [fn].[table_name], [c].[name] AS [column_name], [t].[name] AS [type_name] | |
| FROM [sys].[columns] AS [c] | |
| INNER JOIN [sys].[types] AS [t] ON [t].[system_type_id] = [c].[system_type_id] | |
| CROSS APPLY ( | |
| SELECT OBJECT_SCHEMA_NAME([c].[object_id]) AS [schema_name], OBJECT_NAME([c].[object_id]) AS [table_name] | |
| ) AS [fn] | |
| WHERE 1 = 1 | |
| AND [t].[name] IN ('datetime2', 'datetime') | |
| --AND [t].[name] = 'datetimeoffset' | |
| AND OBJECTPROPERTY([c].[object_id], 'IsMSShipped') = 0 | |
| AND OBJECTPROPERTY([c].[object_id], 'IsUserTable') = 1 | |
| ORDER BY [fn].[schema_name], [fn].[table_name], [c].[name]" | |
| $SqlCmdArguments = @{ | |
| ServerInstance = $ServerInstance | |
| Database = $Database | |
| ApplicationName = $MyInvocation.MyCommand.Name | |
| Encrypt = "Optional" | |
| Query = $query | |
| } | |
| if ($creds) { | |
| $SqlCmdArguments.Add("Credential", $creds) | |
| } | |
| $columnsList = Invoke-Sqlcmd @SqlCmdArguments | |
| $Scripter = [Microsoft.SqlServer.Management.Smo.Scripter]::new($serverInstance) | |
| $Scripter.Options.AllowSystemObjects = $False | |
| #$Scripter.Options.IncludeIfNotExists = $true | |
| $Scripter.Options.IncludeHeaders = $False | |
| $Scripter.Options.ScriptSchema = $true | |
| $Scripter.Options.Indexes = $True | |
| $Scripter.Options.WithDependencies = $False | |
| $Scripter.Options.Encoding = [System.Text.Encoding]::UTF8 | |
| # enum the tables so we can use .Item() | |
| # $db.EnumObjects([Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Table) | Out-Null | |
| try { | |
| foreach ($column in $columnsList) { | |
| $table = $db.Tables.Item($column.table_name, $column.schema_name) | |
| $dateColumn = $table.Columns[$column.column_name] | |
| Write-Host "Working on [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)].[$($dateColumn.Name)] ($($dateColumn.DataType.Name))" -ForegroundColor Yellow | |
| $indexes = $dateColumn.Parent.Indexes | Where-Object { $_.IndexedColumns.Name -icontains $dateColumn.Name } | |
| <# Script the drops for the defaults and the indexes tied to this column #> | |
| $Scripter.Options.ScriptDrops = $true | |
| $dropSql = [System.Text.StringBuilder]::new() | |
| if ($dateColumn.DefaultConstraint) { | |
| $dropSql.AppendLine("RAISERROR('DROP DEFAULT CONSTRAINT [$($dateColumn.DefaultConstraint.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null | |
| $dropSql.AppendLine($Scripter.Script($dateColumn.DefaultConstraint)) | Out-Null | |
| } | |
| foreach($index in $indexes) { | |
| $dropSql.AppendLine("RAISERROR('DROP INDEX $($index.IndexType) [$($index.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null | |
| $dropSql.AppendLine($Scripter.Script($index)) | Out-Null | |
| } | |
| <# Script the creates for the defaults and the indexes tied to this column #> | |
| $Scripter.Options.ScriptDrops = $false | |
| $createSql = [System.Text.StringBuilder]::new() | |
| if ($dateColumn.DefaultConstraint) { | |
| $dateColumn.DefaultConstraint.Text = "(SYSDATETIMEOFFSET() AT TIME ZONE 'UTC')" | |
| $createSql.AppendLine("RAISERROR('CREATE DEFAULT CONSTRAINT [$($dateColumn.DefaultConstraint.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null | |
| $createSql.AppendLine($Scripter.Script($dateColumn.DefaultConstraint)) | Out-Null | |
| $dateColumn.Nullable = $false | |
| } | |
| foreach($index in $indexes) { | |
| $createSql.AppendLine("RAISERROR('CREATE INDEX $($index.IndexType) [$($index.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null | |
| $createSql.AppendLine($Scripter.Script($index)) | Out-Null | |
| } | |
| $ServerConnection.BeginTransaction() | |
| $db.ExecuteNonQuery($dropSql.ToString()); | |
| Write-Host "ALTER [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)].[$($dateColumn.Name)] DATETIMEOFFSET" | |
| $dateColumn.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::new("DateTimeOffset") | |
| $dateColumn.DataType.NumericScale = 7 | |
| $dateColumn.Parent.Alter() | |
| $db.ExecuteNonQuery($createSql.ToString()); | |
| $ServerConnection.CommitTransaction() | |
| } | |
| } catch { | |
| # bail out on the first exception and rethrow it so it does not keep spinning | |
| if ($ServerConnection.TransactionDepth -gt 0) { | |
| $ServerConnection.RollBackTransaction() | |
| } | |
| throw | |
| } | |
| Write-Host "DONE" -ForegroundColor Yellow |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment