shaneis

SELECT Pivot1.ColumnName,
	   Pivot1.[dbo.DifferenceTable01],
	   Pivot1.[dbo.DifferenceTable02],
	   CASE WHEN [dbo.DifferenceTable01] = 1 AND [dbo.DifferenceTable02] = 1
			THEN 'Both'
			WHEN [dbo.DifferenceTable01] = 1 AND [dbo.DifferenceTable02] IS NULL
			THEN 'Table 1 only'
			WHEN [dbo.DifferenceTable01] IS NULL AND [dbo.DifferenceTable02] = 1
			THEN 'Table 2 only'
			ELSE 'Eh...this should not really happen'

shaneis

# Load the assemblies to work for the latest version of SQL Server on the machine
$AddTypeValue = Get-ChildItem -Path C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\ |
  Sort-Object -Property LastWriteTimeUtc |
  Select-Object -ExpandProperty Name -First 1;

[string]$AssemblyValue = "Microsoft.SqlServer.Smo, Version=$($AddTypeValue.Substring(0, 8)), culture=neutral, PublicKeyToken=$($AddTypeValue.Substring($AddTypeValue.LastIndexOf('__') + 2))";

Add-Type -AssemblyName $AssemblyValue;

shaneis

SET STATISTICS IO, TIME ON;
GO

SELECT Pivot1.ColumnName,
	   Pivot1.[dbo.TableColumnDifference01],
	   Pivot1.[dbo.TableColumnDifference02],
	   CASE WHEN [dbo.TableColumnDifference01] = 1 AND [dbo.TableColumnDifference02] = 1
			THEN 'Both'
			WHEN [dbo.TableColumnDifference01] = 1 AND [dbo.TableColumnDifference02] IS NULL
			THEN 'Table 1 only'

shaneis

SET STATISTICS IO, TIME ON;

DECLARE @table1 nvarchar(50) = 'dbo.TableColumnDifference01',
        @table2 nvarchar(50) = 'dbo.TableColumnDifference02';

SET @table1 = PARSENAME(@table1, 1);
SET @table2 = PARSENAME(@table2, 1);

SELECT      *
FROM

shaneis

$Table01Param = @{
    ServerInstance = 'localhost\SQLServer2K16'
    Database       = 'Pantheon'
    Query          = "SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.TableColumnDifference01', N'U')"
}

$Table02Param = @{
    ServerInstance = 'localhost\SQLServer2K16'
    Database       = 'Pantheon'
    Query          = "SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.TableColumnDifference02', N'U')"

shaneis

USE Pantheon;
GO

DROP TABLE IF EXISTS dbo.GotAnyChange;

CREATE TABLE dbo.GotAnyChange
     (
         GotAnyChangeID int IDENTITY(1, 1),
	 Column1 int,
         Column2 char(1),

shaneis

USE Pantheon;
GO

DECLARE @Id int = 1

-- ChangeLog data store
-- Yes, I know that they're all varchars...
DECLARE @ChangeLogTemp table
      (
          ColumnName varchar(100),

shaneis

USE Pantheon;
GO

DECLARE @ChangeLogAsXml xml,
		@Id int = 1;

DROP TABLE IF EXISTS #OutputTableNew;
CREATE TABLE #OutputTableNew
     (
         GotAnyChangeID int         NOT NULL,

shaneis

USE Pantheon;
GO

DROP TABLE IF EXISTS #OutputTableDynamic;
GO
CREATE TABLE #OutputTableDynamic
     (
         GotAnyChangeID int         NOT NULL,
         OldColumn1     int         NOT NULL,
         NewColumn1     int         NOT NULL,

shaneis

$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'

$InvSQLParams = @{
    ServerInstance = 'localhost\SQLSERVER2K16'
    Database       = 'Pantheon'
    Query          = $NewLineQuery
}

Invoke-Sqlcmd @InvSQLParams | 
    Select-Object -ExpandProperty Notes