SQL Script to Compare Columns and Data

Here’s what the script does step by step:

1. Declares a variable @TableName and assigns it a value.
2. Declares a variable @SQL to hold dynamic SQL.
3. Generates dynamic SQL to query column names and data types of the specified table.
4. Executes the dynamic SQL using sp_executesql, passing the @TableName variable as a parameter.

The script uses a cursor to iterate over the columns of the specified table in the INFORMATION_SCHEMA.COLUMNS view and inserts the column names and data types into a temporary table #ComparisonResults. Finally, it selects the comparison results and drops the temporary table.

Make sure to replace 'YourTableName' with the actual name of your table.

DECLARE @TableName NVARCHAR(100) = ‘YourTableName’; — Change to your table name

— Generate dynamic SQL to get column names and data types of the specified table
DECLARE @SQL NVARCHAR(MAX) = ‘
DECLARE @ColumnName NVARCHAR(100);
DECLARE @DataType NVARCHAR(100);
DECLARE @Query NVARCHAR(MAX);

— Temporary table to store results
CREATE TABLE #ComparisonResults (
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
DataType NVARCHAR(100)
);

— Get column names and data types of the specified table
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;

OPEN column_cursor;

FETCH NEXT FROM column_cursor INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query =
”INSERT INTO #ComparisonResults (TableName, ColumnName, DataType)
VALUES (””” + @TableName + ”””’, ””’ + @ColumnName + ”””, ””’ + @DataType + ”””);”;

EXEC sp_executesql @Query;

FETCH NEXT FROM column_cursor INTO @ColumnName, @DataType;

END

CLOSE column_cursor;
DEALLOCATE column_cursor;

— Select comparison results
SELECT *
FROM #ComparisonResults;

— Drop temporary table
DROP TABLE #ComparisonResults;
‘;

DECLARE @TableName1 NVARCHAR(100) = ‘Table1’; — Change to your first table name
DECLARE @TableName2 NVARCHAR(100) = ‘Table2’; — Change to your second table name

— Generate dynamic SQL to compare columns and row data
DECLARE @SQL NVARCHAR(MAX) = ‘
— Create temporary tables to store data from each table
CREATE TABLE #Table1Data (
RowID INT IDENTITY(1,1),
‘ + (SELECT STUFF((SELECT ‘, ‘ + COLUMN_NAME + ‘ ‘ + DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName1
FOR XML PATH(”)), 1, 2, ”)) + ‘
);

CREATE TABLE #Table2Data (
RowID INT IDENTITY(1,1),
‘ + (SELECT STUFF((SELECT ‘, ‘ + COLUMN_NAME + ‘ ‘ + DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName2
FOR XML PATH(”)), 1, 2, ”)) + ‘
);

— Insert data from first table
INSERT INTO #Table1Data (‘ + (SELECT STUFF((SELECT ‘, ‘ + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName1
FOR XML PATH(”)), 1, 2, ”)) + ‘)
SELECT ‘ + (SELECT STUFF((SELECT ‘, ‘ + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName1
FOR XML PATH(”)), 1, 2, ”)) + ‘
FROM ‘ + @TableName1 + ‘;

— Insert data from second table
INSERT INTO #Table2Data (‘ + (SELECT STUFF((SELECT ‘, ‘ + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName2
FOR XML PATH(”)), 1, 2, ”)) + ‘)
SELECT ‘ + (SELECT STUFF((SELECT ‘, ‘ + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName2
FOR XML PATH(”)), 1, 2, ”)) + ‘
FROM ‘ + @TableName2 + ‘;

— Compare columns and row data
SELECT
T1.RowID AS Table1_RowID,
T2.RowID AS Table2_RowID,
‘ + (SELECT STUFF((SELECT ‘,
CASE WHEN C1.COLUMN_NAME IS NULL THEN ” ELSE ‘CASE WHEN T1.’ + C1.COLUMN_NAME + ‘ = T2.’ + C2.COLUMN_NAME + ‘ THEN ”Match” ELSE ”Mismatch” END AS ‘ + C1.COLUMN_NAME END
FROM INFORMATION_SCHEMA.COLUMNS C1
LEFT JOIN INFORMATION_SCHEMA.COLUMNS C2 ON C1.COLUMN_NAME = C2.COLUMN_NAME
WHERE C1.TABLE_NAME = @TableName1
FOR XML PATH(”)), 1, 2, ”)) + ‘
FROM #Table1Data T1
FULL OUTER JOIN #Table2Data T2 ON T1.RowID = T2.RowID;

— Drop temporary tables
DROP TABLE #Table1Data;
DROP TABLE #Table2Data;
‘;

— Execute dynamic SQL
EXEC sp_executesql @SQL, N’@TableName1 NVARCHAR(100), @TableName2 NVARCHAR(100)’, @TableName1, @TableName2;