Блог

Мы обновляем многочисленные хранимые процедуры, которые касаются почти каждой транзакции в нашей базе данных. Мне интересно, есть ли какой-нибудь инструмент, с помощью которого мы могли бы запустить две хранимые процедуры в нескольких базах данных и сравнить наборы результатов не только по тому, совпадают ли данные, но и сравнить, имеют ли возвращаемые данные одинаковые атрибуты (поле возвращает 1, и это значение равно нулю).Целое число для одной хранимой процедуры, но это бит в другой).

В настоящее время я вхожу в удаленную базу данных, запускаю хранимые процедуры и вставляю результаты в Excel. Затем я запускаю макрос, чтобы проверить, совпадают ли данные.

Я хотел бы найти инструмент, который я мог бы настроить для входа в систему, запуска хранимых процедур и сравнения без того, чтобы я делал это вручную.

Вы можете сделать это вручную несколькими способами, используя некоторые встроенные функции SQL.

1. Вывести оба сохраненных процесса в таблицы

2 — Запустите a UNION ALL внутри ловушки ошибок.

Это сразу сообщит вам, если столбцы или типы данных не совпадают.

3. Запустите EXCEPT запрос, сравнивающий A с B, а затем B с A

Это сообщит вам, если фактические данные в наборах результатов не совпадают.

Вы могли бы обернуть все это в некоторый динамический SQL, чтобы автоматизировать его для работы с большим набором процедур, если вам нужно.

Я столкнулся с аналогичной проблемой и необходимостью оптимизировать некоторые большие процедуры и хочу сравнить старые и новые для некоторых входных наборов. Идея похожа на то, на что уже дан ответ, но я подумал, что уже написанная процедура будет лучше.

Вышеупомянутая версия обновлена для работы с Sql2012, которая требует указания точной схемы возвращаемого результирующего набора, но ее можно легко адаптировать к Sql2008 (там нет НАБОРОВ РЕЗУЛЬТАТОВ).

Вот код:

 -- 
-- Description: compares the results returned by two stored procedures. Comparison is performed using a 'loopback' linked server and using openquery, so the final query
--              must obey openquery limitations. It returns all rows that are within the first result set and not within the second and viceversa. If all result sets are 
--              empty, results are equivalent (order does not matter)
--
-- PARAMS:
--      @Procedure1FullName: procedure 1 full name (i.e. database.schema.proc_name)
--      @Params1Str: procedure 1 params as string (e.g. @param1 = value1, @param2 = 'value2)'
--      @Procedure2FullName: procedure 2 full name
--      @Params2Str: procedure 2 params as string
--      @ResultSetStr: result set column specification (it is required for usage of procedure in SQL 2012 )
--      @LoopBackServerName: loopback (same server) linked server name - required to use openquery on the same server (and database)
--      @Debug: outputs debug info
--
-- CHANGE LOG:
-- Author: alexandru.dragan
-- Date: 20140811
-- Description: added ResultSetStr param as Sql2012 cannot handle execution of procedures without knowing the result set structure
--
--
-- =============================================
ALTER PROCEDURE [dbo].[uspCompareProcedureResults]
(
    @Procedure1FullName VARCHAR(255),
    @Params1Str VARCHAR(MAX),
    @Procedure2FullName VARCHAR(255),
    @Params2Str VARCHAR(MAX),
    @ResultSetStr VARCHAR(MAX),
    @LoopBackServerName VARCHAR(255) = 'loopback',
    @ForceShowDetails BIT = 0,
    @Debug BIT = 0
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = ''
    DECLARE @InputStr NVARCHAR(MAX)

    -- escaping string parameters
    SET @Params1Str = REPLACE(@Params1Str, '''', '''''')
    SET @Params2Str = REPLACE(@Params2Str, '''', '''''')

    SET @InputStr = @Procedure1FullName   '('   @Params1Str   ')'

    SET @SQL = '
        DECLARE @StartTime datetime;
        DECLARE @Diff1 BIGINT;
        DECLARE @Diff2 BIGINT;

        -- executing and measuring time for the first procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R1
        FROM OPENQUERY('   @LoopBackServerName   ', ''set fmtonly off exec '   @Procedure1FullName   ' '   @Params1Str   ' WITH RESULT SETS (( '    @ResultSetStr   '))'');
        SET @Diff1 = DATEDIFF(ms, @StartTime, GETDATE());

        -- executing and measuring time for the second procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R2
        FROM OPENQUERY('   @LoopBackServerName   ', ''set fmtonly off exec '   @Procedure2FullName   ' '   @Params2Str   ' WITH RESULT SETS (( '    @ResultSetStr   '))'');
        SET @Diff2 = DATEDIFF(ms, @StartTime, GETDATE());

        -- changing all float columns to decimal to ensure correct comparison
        DECLARE @InnerSQL NVARCHAR(MAX) = N''''

        select @InnerSQL  = ''alter table #R1 alter column ''   QUOTENAME(COLUMN_NAME)   '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R1[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        SET @InnerSQL = N'''';
        select @InnerSQL  = ''alter table #R2 alter column ''   QUOTENAME(COLUMN_NAME)   '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R2[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        -- creating temporary tables to hold result sets differences
        SELECT ''R1  R2'' AS [R1  R2], * INTO #R12 
        FROM #R1 
        WHERE 1 = 0

        SELECT ''R2  R1'' AS [R2  R1], * INTO #R21
        FROM #R1 
        WHERE 1 = 0

        -- inserting data
        INSERT INTO #R12 
        SELECT ''R1  R2'' AS [R1  R2], * FROM #R1 
        EXCEPT
        SELECT ''R1  R2'' AS [R1  R2], * FROM #R2;

        INSERT INTO #R21
        SELECT ''R2  R1'' AS [R2  R1], * FROM #R2 
        EXCEPT
        SELECT ''R2  R1'' AS [R2  R1], * FROM #R1;

        -- difference flag
        DECLARE @IsDiff BIT = 0
        IF EXISTS (SELECT 1 FROM #R12) OR EXISTS (SELECT 1 FROM #R21)
            SET @IsDiff = 1

        SELECT '''   @InputStr   ''' AS '''   LEFT(@InputStr, 128)   ''', @IsDiff AS ''Diff results'', ''R1'' AS [R1], @Diff1 AS ''Duration1 [ms]'', @Diff2 AS ''Duration2 [ms]'';

        -- showing details if a difference exists or details must be output
        if (@IsDiff  = 1 OR '   CAST(@ForceShowDetails AS VARCHAR)   ' = 1)
        BEGIN
            SELECT ''Results for first procedure'' AS ''Results for first procedure'', * FROM #R1;
            SELECT ''Results for second procedure'' AS ''Results from the second procedure'', * FROM #R2;
            SELECT * FROM #R12
            SELECT * FROM #R21
        END
    '

    if (@Debug = 1)
    BEGIN
        PRINT '@SQL = '   @SQL
        PRINT 'SQL len = '   CAST(LEN(@SQL) AS VARCHAR(MAX))
    END

    EXEC (@SQL)
END
  

Ограничения / отмеченные / известные проблемы:

1. требуется, чтобы связанный сервер указывал на один и тот же экземпляр или базу данных
2. процедура может (и должна) возвращать только один набор результатов
3. все числа с плавающей запятой преобразуются в десятичные дроби (цифры с фиксированной запятой), чтобы избежать почти равных различий между числами с плавающей запятой

Не уверен, действительно ли вы имеете в виду «в нескольких базах данных» или «на нескольких серверах». Если это сервер, вам нужно добавить связанные серверы.

В приведенной ниже процедуре будет сравниваться выходной набор результатов из 2 хранимых процедур или 2 инструкций. Не нужно знать схему набора результатов, но 2 входных оператора должны иметь идентичные схемы. Он вернет 0 строк, если выходные данные будут одинаковыми. Это решение использует команду openrowset в SQL Server. Вот несколько примеров использования хранимых процедур

     -- Sample Usage, works for both Queries and Stored Procs    
    DECLARE @SQL_SP1 VARCHAR(MAX)
    DECLARE @SQL_SP2 VARCHAR(MAX)

    -- Compare results of 2 Stored Procs
    SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
    SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
    EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2

    -- Compare just 2 SQL Statements
    SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
    SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
    EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
  

Для SP требуются следующие предварительные условия, поскольку он использует openrowset, который может быть не идеальным для производственной среды.

    -- Code uses openrowset so needs some special permissions
   EXEC sp_configure 'show advanced options', 1
   EXEC sp_configure 'ad hoc distributed queries', 1
   EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
  

Вот код для хранимой процедуры.

 ==================================================================================
    --== SUMMARY utlCompareStatementResults 
    --==    Compares output of 2 queries or stored procs
    --==    - requires sp_configure 'show advanced options', 1
    --==    - requires sp_configure 'ad hoc distributed queries', 1
    --==    - maybe requires EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
    --==    - requires the RecordSet Output to have Unique ColumnNames (no duplicate columns)
    --==    - requires references in straight SQL to be fully qualified [dbname].[schema].[objects] but not within an SP
    --==    - requires references SP call to be fully qualifed [dbname].[schema].[spname] but not objects with the SP
    --== OUTPUT
    --==    Differences are returned 
    --==    If there is no recordset returned, then theres no differences
    --==    However if you are comparing 2 empty recordsets, it doesn't mean anything
    --== USAGE
    --==   DECLARE @SQL_SP1 VARCHAR(MAX)
    --==   DECLARE @SQL_SP2 VARCHAR(MAX)
    --==   -- Compare just 2 SQL Statements
    --==   SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
    --==   SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
    --==   EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
    --==
    --==   -- Compare results of 2 Stored Procs
    --==   SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
    --==   SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
    --==   EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
    --==================================================================================
    CREATE PROCEDURE utlCompareStatementResults
       @SQL_SP1 VARCHAR(MAX),
       @SQL_SP2 VARCHAR(MAX)
    AS
    BEGIN
        DECLARE @TABLE1 VARCHAR(200)
        DECLARE @TABLE2 VARCHAR(200)
        DECLARE @SQL_OPENROWSET VARCHAR(MAX) 
        DECLARE @CONNECTION VARCHAR(100)

        SET @CONNECTION = 'server=' @@SERVERNAME ';Trusted_Connection=yes'

        SET @SQL_SP1 = REPLACE(@SQL_SP1, '''','''''')
        SET @SQL_SP2 = REPLACE(@SQL_SP2, '''','''''')

        SET @TABLE1 = '#'   SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)
        SET @TABLE2 = '#'   SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)

        SET @SQL_OPENROWSET =
        'SELECT * '   ' '  
        'INTO '   @TABLE1   ' '  
        'FROM OPENROWSET(''SQLNCLI'', '   ''''   @CONNECTION   ''''  
                        ','''   @SQL_SP1  '''); '  
        'SELECT * '   ' '  
        'INTO '   @TABLE2   ' '  
        'FROM OPENROWSET(''SQLNCLI'', '   ''''   @CONNECTION   ''''  
                        ','''   @SQL_SP2  '''); '  
        '(SELECT * FROM '   @TABLE1   ' EXCEPT SELECT * FROM '   @TABLE2   ') '   
        ' UNION ALL '  
        '(SELECT * FROM '   @TABLE2   ' EXCEPT SELECT * FROM '   @TABLE1   '); '  
        'DROP TABLE '   @TABLE1   '; '  
        'DROP TABLE '   @TABLE2   '; '
        PRINT @SQL_OPENROWSET
        EXEC (@SQL_OPENROWSET)
        PRINT 'DifferenceCount: '   CONVERT(VARCHAR(100), @@ROWCOUNT)
    END