Phil-Factor

SET NOCOUNT ON
PRINT 'Adding the descriptions for all tables and columns';

/*The script to make this JSON document is published here.
https://www.red-gate.com/hub/product-learning/flyway/managing-database-documentation-during-flyway-based-development
Flyway will produce this as a routine if you want, using 
 DatabaseBuildAndMigrateTasks.ps1 
 */
DECLARE @JSONTablesAndColumns NVARCHAR(MAX) =
  N'

Phil-Factor

<#This scriptblock executes SQL that produces a report in XML or JSON from the database
#>
$SQLCmdAlias = "$($env:ProgramFiles)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe"

$ExecuteTableSmellReport = {
	Param ($param1) # $ExecuteTableSmellReport - parameter is a hashtable 
	$problems = @()
	@('server', 'database', 'version', 'project') | foreach{
		if ($param1.$_ -eq $null)
		{ write-error "no value for '$($_)'" }

Phil-Factor

<# first we fill in the data object that contains all the data we need, such as the list of databases we want copied and the list of servers we want to copy them to. #>
$Data = @{
  'ServerURL' = 'http://MyCloneServer:14145'; #the HTTP address of the Clone Server
  'ImageDirectoryURL' = '\\TheFileServer\Directory'; #the URL of the image directory
  
  "Original" = @{
    #We will clone from this database. This is the original, maybe a build stocked with data
    'Server' = 'MyBuildServer'; #The SQL Server instance
    'instance' = '\'
    'username' = 'PhilFactor'; #leave blank if windows authentication

Phil-Factor

import-Module sqlserver
<# a list of connection strings
for each of the target databaseinstances on which you'd like to run the code
#>
$ServerAndDatabaseList =
@(
 <# list of connection strings for each of the SQLservers that you need to execute code on #>
  @{ #provide a connection string for the instance
    'ServerConnectionString' = 'Server=MyServer;User Id=PhilFactor;Persist Security Info=False';
         #and a list of databases. Make target the same as source if the database you use it on has the same name

Phil-Factor

DROP PROCEDURE IF exists #SaveMergeStatementFromTable
GO
CREATE PROCEDURE #SaveMergeStatementFromTable 
  /**
Summary: >
  This creates a merge statement, creating a table source from a multi-row 
  VALUES statement, and merging it with the table whose name you provide.
  This MERGE statement can then be executed. Beware
  that this is only really practicable for small tables, because the 
  VALUES statement degrades with scale.

Phil-Factor

$database = 'MyDatabase' #the name of the database we are cloning e,g, AdventureWorks
$Data = @{
  "Database" = '$database';
  #where we have SQL Compare installed. Yours could be a different version
  "Original" = @{
  #We will clone from this database. This is the original, maybe a build stocked with data
    'Server' = 'MyUsefulServer'; #The SQL Server instance
    'Instance' = ''; #The SQL Server instance
    'Database' = "$($Database)"; #The name of the database
    'username' = 'PhilFactor'; #leave blank if windows authentication