Skip to content

Instantly share code, notes, and snippets.

@Phil-Factor
Created March 4, 2019 18:10
PowerShell script that saves every SQL Server table in a database to an Extended JSON file
set-psdebug -strict
$ErrorActionPreference = "stop" #
$MS = 'Microsoft.SQLServer'
$My = "$MS.Management.Smo"
$Errors = @()
#You need to fill this lot in
$SourceServer = 'MyServer'
$Username = 'MyUserID'
$Database = 'TheDatabase'
$OutputPath = "MyPathToTheDirectoryForTheOoutoutFiles"
#Load SMO assemblies and so on
Import-Module sqlserver -DisableNameChecking -ErrorAction silentlycontinue -ErrorVariable +Errors
#Does the output path exist?
if (-not (Test-Path -PathType Container $OutputPath))
{
# we create the directory if it doesn't already exist
New-Item -ItemType Directory -Force -Path $OutputPath `
-ErrorAction silentlycontinue -ErrorVariable +Errors;
}
<# make sure all the connections are servicable #>
if ($Errors.Count -eq 0)
{
$conn = new-object "$MS.Management.Common.ServerConnection"
$conn.ServerInstance = $SourceServer
if ($username -ieq '')
{
$conn.LoginSecure = $true;
}
else
{
<# This is more elaborate a process than you might expect because we can't assume that we can use Windows authentication, because of Azure, remote servers outside the
domain, and other such complications. We can't ever keep passwords for SQL Server
authentication as part of the static script data. At this stage, we ask for passwords
if they aren't known, and otherwise store them as secure strings on file in the user
area, protected by the workstation security.
#>
#create a connection object to manage credentials
$encryptedPasswordFile = "$env:USERPROFILE\$($Username)-$SourceServer.txt"
# test to see if we know about the password un a secure string stored in the user area
if (Test-Path -path $encryptedPasswordFile -PathType leaf)
{
#has already got this set for this login so fetch it
$encrypted = Get-Content $encryptedPasswordFile | ConvertTo-SecureString
$Credentials = New-Object System.Management.Automation.PsCredential($Username, $encrypted)
}
else #then we have to ask the user for it
{
#hasn't got this set for this login
$Credentials = get-credential -Credential $Username
$Credentials.Password | ConvertFrom-SecureString |
Set-Content "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt"
}
$conn.LoginSecure = $false;
$conn.Login = $Credentials.UserName;
$conn.SecurePassword = $Credentials.Password;
}
}
if ($errors.Count -eq 0)
#if there were errors, then it gives up at this stage and reports the errors.
{
$DataSourceInstanceObject = new-object ("$My.Server") $conn
if ($DataSourceInstanceObject.Version -eq $null)
{ Throw "Can't find the instance $SourceServer" };
$DataDatabaseObject = $DataSourceInstanceObject.Databases[$Database]
if ($DataDatabaseObject.name -ne $Database)
{
Throw "Can't find the database '$Database' in instance $SourceServer"
};
$procedure = @'
CREATE OR ALTER PROCEDURE #SaveExtendedJsonDataFromTable
/**
Summary: >
This gets the Extended JSON data from a table. You can specify
it either by the database.schema.table 'tablespec, or do it
individually.
Author: phil factor
Date: 04/02/2019
Examples: >
USE pubs
DECLARE @Json NVARCHAR(MAX)
EXECUTE #SaveExtendedJsonDataFromTable
@database='pubs',
@Schema ='dbo',
@table= 'jobs',
@JSONData=@json OUTPUT
PRINT @Json
Returns: >
The JSON data
**/
(@database sysname = NULL, @Schema sysname = NULL, @table sysname = NULL,
@tableSpec sysname, @jsonData NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @Data NVARCHAR(MAX);
IF Coalesce(@table, @Tablespec) IS NULL
OR Coalesce(@Schema, @Tablespec) IS NULL
RAISERROR('{"error":"must have the table details"}', 16, 1);
IF @table IS NULL SELECT @table = ParseName(@Tablespec, 1);
IF @Schema IS NULL SELECT @Schema = ParseName(@Tablespec, 2);
IF @database IS NULL
SELECT @database = Coalesce(ParseName(@Tablespec, 3), Db_Name());
IF @table IS NULL OR @Schema IS NULL OR @database IS NULL
RAISERROR('{"error":"must have the table details"}', 16, 1);
DECLARE @SourceCode NVARCHAR(255) =
(
SELECT 'SELECT * FROM ' + QuoteName(@database) + '.'
+ QuoteName(@Schema) + '.' + QuoteName(@table)
);
DECLARE @a_unique_key bit
DECLARE @HowManyUniqueKeys INT
SELECT @HowManyUniqueKeys= Sum(Convert(INT,is_part_of_unique_key))
FROM sys.dm_exec_describe_first_result_set(@SourceCode, NULL, 1)
SELECT @a_unique_key= CASE WHEN @HowManyUniqueKeys = 1 THEN 1 ELSE 0 END
DECLARE @params NVARCHAR(MAX);
SELECT @params =
String_Agg(
CASE WHEN system_type_id IN
( 35, -- text
99, -- ntext
98, -- sql_variant
167, -- varchar
231, -- nvarchar
239, -- nchar
175, -- char
36, -- uniqueidentifier
59, -- real
62, -- float
104, -- bit
241
) -- xml
THEN
CASE WHEN @a_unique_key=1 AND is_part_of_unique_key =1 THEN name+ ' as "_id", '
ELSE '' END +QuoteName(name)
WHEN user_type_id IN (128, 129, 130) THEN
'convert(nvarchar(100),' + name + ') as "' + name + '"'
--hierarchyid (128) geometry (130) and geography types (129) can be coerced.
--------binary
WHEN system_type_id IN
( 165, -- varbinary
173-- binary
)
--THEN name + ' as "' + name + '.$binary.hex", ''80'' as "' + name + '.$binary.subType"'
THEN name --I gave up. Extended json binary form is just awful
WHEN system_type_id = 34 THEN --image
'convert(varbinary(max),' + name + ') as "' + name + '"'
WHEN system_type_id IN (35) THEN --35 is text
'convert(varchar(max),' + name + ') as "' + name + '"'
WHEN system_type_id IN (99) THEN --ntext
'convert(nvarchar(max),' + name + ') as "' + name + '"'
--------numberInt
WHEN system_type_id IN
( 48, -- tinyint
56, -- int
52 -- smallint
)
THEN
CASE WHEN @a_unique_key=1 AND is_part_of_unique_key =1 THEN name+ ' as "_id", '
ELSE '' END +
'convert(varchar(50),' + name + ') as "' + name + '.$numberInt"'
--------numberLong
WHEN system_type_id = 127 -- bigint
THEN
CASE WHEN @a_unique_key=1 AND is_part_of_unique_key =1 THEN name+ ' as "_id", '
ELSE '' END +
'convert(varchar(50),' + name + ') as "' + name + '.$numberLong"'
--------date
WHEN system_type_id IN
( 40, -- date
41, -- time
42, -- datetime2
43, -- datetimeoffset
58, -- smalldatetime
61, -- datetime
189
) -- timestamp
THEN
'convert(datetimeoffset,convert(datetime2(0),' + name + ')) as "' + name + '.$date"'
-------numberDecimal
WHEN system_type_id IN
( 106, -- decimal
108, -- numeric
122, -- smallmoney
60
) -- money
THEN
'convert(varchar(50),' + name + ') as "' + name + '.$numberDecimal"'
ELSE QuoteName(name)
END,
', '
)
FROM sys.dm_exec_describe_first_result_set(@SourceCode, NULL, 1);
DECLARE @expression NVARCHAR(max) =
'
USE ' + @database + '
SELECT @TheData=(SELECT ' + @params + ' FROM ' + QuoteName(@database) + '.'
+ QuoteName(@Schema) + '.' + QuoteName(@table)
+ ' FOR JSON PATH)';
PRINT @Expression
EXECUTE sp_executesql @expression, N'@TheData nvarchar(max) output',
@TheData = @jsonData OUTPUT;
END;
'@ <# we create a new connection just so we can be certain of being able to use
the same connection that has the temporary stored procedure on it. Normally
we just share the server's connection but this isn't safe and it is possib;e
to lose the connection just by doing an SMO action #>
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $conn.ConnectionString
$SqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()
try
{
$sqlCommand.CommandText = $procedure
$sqlCommand.ExecuteScalar()
}
catch [System.Exception]
{
$_.Exception.InnerException.Errors | foreach{ write-error "$_.message" } #parameters
}
}
if ($errors.Count -eq 0)
#if there were errors, then it gives up at this stage and reports the errors.
{
$DataDatabaseObject.Tables | Select Schema, Name |
foreach {
$filename = "$($_.schema)_$($_.Name)" -replace '[\\\/\:\.]', '-'
$Tablename = "[$($_.schema)].[$($_.Name)]"
$result = $_.Name
#execute this script, using the existing connection
$params =@"
DECLARE @Json NVARCHAR(MAX)
EXECUTE #SaveExtendedJsonDataFromTable @TableSpec='$tablename',@JSONData=@json OUTPUT
Select @json
"@
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = $params
try
{
$sqlCommand.ExecuteScalar() | Out-file -FilePath "$OutputPath\$filename" -Encoding utf8 -Force
}
catch [System.Exception]
{
$_.Exception.InnerException.Errors | foreach{ write-error $_.message } #parameters
}
}
}
$SqlConnection.Close()
if ($Errors.count -gt 0) #if we couldn't import something
{
$Errors | foreach{
write-warning "There was an error '$($_)'"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment