sp_executeSQL for each record in a table

Question

sp_executeSQL for each record in a table

Navigation

#1 by (1 votes)

1

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 get the name of the columns inside the INFORMATION_SCHEMA of each bank and based on each table, insert a record into a temporary table so

--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 that I do not know how to use a sp_executeSQL by searching the columns for every existing record in the tables I previously created #C_TABLES and #F_TABLES ...

Can someone please give me a light?

sql sql-server

asked by anonymous 10.02.2015 / 20:53

1 answer

How to use the setX () and setY () methods of the FPDF library. Shared Preferences does not store login using JSON

score 1 · Accepted Answer

"Manual" comparison of the structure

Do not need dynamic SQL, at least in case you know the names of the databases.

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 one:

-- 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 to use FULL JOIN to and cross the structure of two tables, displaying what you have in one and not in the other.

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

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

Using Dynamic SQL

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

The command SP_SQLEXEC has problems with values of type VARCHAR and also does not accept that you do direct concatenation in argument passing. I do not know if this applies to all versions of SQL Server, but it is usually what causes the most difficulties with these commands.

The following example runs 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