sp_executeSQL for each record of a table

Question

I have the following problem:

I need to analyze the differences between two databases, based on the existing tables and columns within these tables. My idea is to take the name of the columns inside the INFORMATION_SCHEMA of each database and based on each table, insert a record in a temporary table like this

--CREATE VARS FROM DATABASES AND TSQL
DECLARE @C AS VARCHAR(50), @F AS VARCHAR(50), @S AS VARCHAR(MAX);

--SET DB'S
SET @C = ''; --GOOD DB
SET @F = ''; --BAD DB

--GET @C TABLE NAMES AND INSERT IN #C_TABLES
SELECT UPPER(TABLE_NAME) AS TABELA INTO #C_TABLES FROM @C.INFORMATION_SCHEMA.TABLES;

--GET @F TABLE NAMES AND INSERT IN #F_TABLES
SELECT UPPER(TABLE_NAME) AS TABELA INTO #F_TABLES FROM @F.INFORMATION_SCHEMA.TABLES;    

My problem is I don’t know how to use one sp_executeSQL searching the columns for each existing record in the tables I created earlier #C_TABLES and #F_TABLES...

Someone there can give me a light please?

Answer 1

"Manual" comparison of the structure

No need for dynamic SQL, at least in case you know the database names.

To identify tables that exist in one and do not exist in the other, or vice versa, use the following query:

-- check tables
SELECT T1.TABLE_NAME 'DB1 TABLE', T2.TABLE_NAME 'DB2 TABLE'
FROM BANCO_A.INFORMATION_SCHEMA.TABLES T1 
FULL JOIN BANCO_B.INFORMATION_SCHEMA.TABLES T2 
    ON T1.TABLE_NAME = T2.TABLE_NAME
ORDER BY ISNULL(T1.TABLE_NAME, T2.TABLE_NAME)

To compare fields and types, you can use this other:

-- check tables and columns
SELECT DB1.TABLE_NAME 'DB1 TABLE', DB1.COLUMN_NAME 'DB1 COLUMN', DB1.DATA_TYPE 'DB1 TYPE',
    DB2.TABLE_NAME 'DB2 TABLE', DB2.COLUMN_NAME 'DB1 COLUMN', DB2.DATA_TYPE 'DB2 TYPE'
FROM (
    SELECT T1.TABLE_NAME, C1.COLUMN_NAME, C1.DATA_TYPE
    FROM BANCO_A.INFORMATION_SCHEMA.TABLES T1 
    JOIN BANCO_A.INFORMATION_SCHEMA.COLUMNS C1 
        ON C1.TABLE_NAME = T1.TABLE_NAME
    ) DB1
FULL JOIN (
    SELECT T2.TABLE_NAME, C2.COLUMN_NAME, C2.DATA_TYPE
    FROM BANCO_B.INFORMATION_SCHEMA.TABLES T2 
    JOIN BANCO_B.INFORMATION_SCHEMA.COLUMNS C2 
        ON C2.TABLE_NAME = T2.TABLE_NAME
    ) DB2
    ON DB1.TABLE_NAME = DB2.TABLE_NAME
    AND DB1.COLUMN_NAME = DB2.COLUMN_NAME
ORDER BY ISNULL(DB1.TABLE_NAME, DB2.TABLE_NAME), ISNULL(DB1.COLUMN_NAME, DB2.COLUMN_NAME)

What these queries basically do is use the FULL JOIN to and cross the structure of two tables, displaying what has in one and does not have in the other.

It would be possible to add some clauses WHERE to filter only fields or tables that are different.

To define which bases you want to compare, just change the prefix in the snippets BANCO_A.INFORMATION_SCHEMA and BANCO_B.INFORMATION_SCHEMA.

Using Dynamic SQL

If, however, you want to do a generic routine and really need to perform dynamic queries, use variables of type NVARCHAR and assemble your query first.

The command SP_SQLEXEC has problems with values like VARCHAR and also does not accept that you do direct concatenation in the passage of arguments. I don’t know if this applies to all versions of SQL Server, but it is usually the one that causes the most difficulties with these commands.

The following example executes the first query above, which compares the tables of two banks:

-- check tables
DECLARE 
    @BANCO1 NVARCHAR(100) = 'BANCO_A',
    @BANCO2 NVARCHAR(100) = 'BANCO_B',
    @SQL NVARCHAR(2000)

SET @SQL = N'SELECT T1.TABLE_NAME ''DB1 TABLE'', T2.TABLE_NAME ''DB2 TABLE''
    FROM ' + @BANCO1 + '.INFORMATION_SCHEMA.TABLES T1 
    FULL JOIN ' + @BANCO2 + '.INFORMATION_SCHEMA.TABLES T2 
        ON T1.TABLE_NAME = T2.TABLE_NAME
    ORDER BY ISNULL(T1.TABLE_NAME, T2.TABLE_NAME)';

EXEC sp_sqlexec @SQL