Переливаем таблицы БД между средами: быстро и без боли на примере MS SQL

SET NOCOUNT OFF;  DECLARE @DB       VARCHAR(250) = QUOTENAME(DB_NAME()),         @DBMaster VARCHAR(255) = 'БД-мастер',                       @ERROR    VARCHAR(MAX);    DECLARE @HistoryLimited           bit = 1,           @table_name               nvarchar(255),           @is_identity              int = 0,           @stm                      nvarchar(max) = '',           @cols                     nvarchar(max) = '',           @IsNOTInsert              bit,           @schema_name              nvarchar(255),           @col_name_identity        nvarchar(255),           @referencing_object       nvarchar(255),           @referenced_object        nvarchar(255),           @constraint_name          nvarchar(255),           @referencing_columns      nvarchar(max),           @referenced_columns       nvarchar(max),           @rules                    nvarchar(max),           @key_cols                 nvarchar(max),           @StartMoment              DATETIME2,           @FinishMoment             DATETIME2,           @delete_referential_action INT,           @update_referential_action INT,           @max_row_insert           INT = 100000,           @isClearTableFKs          BIT = 1,           @RowCount                 BIGINT = 1,           @WhileDelCount            INT = 0;           ;    DECLARE @cnt TABLE (cnt BIGINT NOT NULL);    DROP TABLE IF EXISTS #tbl_res;    CREATE TABLE #tbl_res (                            SchName       NVARCHAR(255) NOT NULL,                            TblName       NVARCHAR(255) NOT NULL,                            StartMoment   DATETIME2     NOT NULL,                            FinishMoment  DATETIME2     NOT NULL,                            Cnt           BIGINT        NOT NULL,                            ErrorMsg      NVARCHAR(MAX) NULL                          );

DECLARE r_cursor_trigg_off CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT FORMATMESSAGE('ALTER TABLE [%s].[%s] DISABLE TRIGGER [%s];'      + CHAR(13)      , SCHEMA_NAME(b.[schema_id])      , OBJECT_NAME(t.parent_id)      , t.[Name]) AS stm         FROM sys.triggers          t         LEFT JOIN sys.tables  b ON b.object_id = t.parent_id         WHERE t.is_disabled = 0           AND t.type_desc = 'SQL_TRIGGER'           AND OBJECT_NAME(t.parent_id) IS NOT NULL         ORDER BY SCHEMA_NAME(b.[schema_id]) ASC,                  OBJECT_NAME(t.parent_id) ASC;      OPEN r_cursor_trigg_off;     FETCH NEXT FROM r_cursor_trigg_off     INTO @stm;      WHILE @@FETCH_STATUS = 0     BEGIN       PRINT @stm;        BEGIN TRY         BEGIN TRANSACTION;           EXEC sys.sp_executesql @stmt = @stm;         COMMIT;       END TRY       BEGIN CATCH         SET @ERROR = ERROR_MESSAGE();         PRINT @ERROR;         IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION       END CATCH              FETCH NEXT FROM r_cursor_trigg_off       INTO @stm;     END      CLOSE r_cursor_trigg_off;     DEALLOCATE r_cursor_trigg_off;      SET @stm = '';      SELECT @stm += FORMATMESSAGE('DISABLE TRIGGER [%s] ON DATABASE;'                  + CHAR(13), t.[Name])     FROM sys.triggers          t     LEFT JOIN sys.tables  b ON b.[object_id] = t.parent_id        WHERE t.is_disabled = 0          AND t.[type_desc] = 'SQL_TRIGGER'          AND OBJECT_NAME(t.parent_id) IS NULL;      PRINT @stm;      BEGIN TRY       BEGIN TRANSACTION;         EXEC sys.sp_executesql @stmt = @stm;       COMMIT;     END TRY     BEGIN CATCH       SET @ERROR = ERROR_MESSAGE();       PRINT @ERROR;       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION     END CATCH

DROP TABLE IF EXISTS #tbls; CREATE TABLE #tbls (                     [name]      NVARCHAR(255) NOT NULL,                     sch_name    NVARCHAR(255) NOT NULL,                     IsNOTInsert BIT           NOT NULL                    );  INSERT INTO #tbls (                     [name],                     sch_name,                     IsNOTInsert                   )   SELECT t.[name],          SCHEMA_NAME(t.[schema_id]) AS sch_name,          --задается правило, по которому определяем          --нужно ли после очистки наполнять данными таблицу или нет          --по умолчанию нужно (0-да, 1-нет)          0 AS IsNOTInsert       FROM sys.tables AS t       --в фильтре задаем какие таблицы брать в расчет       --(в нашем случае какие не брать в расчёт)       WHERE t.[name] NOT LIKE 'unused%'         AND t.[name] NOT LIKE 'removed%'         AND t.[name] NOT LIKE 'migrated%'         AND t.[name] NOT LIKE 'migration%'         AND t.[name] NOT LIKE 'sysdiag%'         AND t.[name] NOT LIKE 'test%'         AND t.[name] NOT LIKE 'tmp%'         AND t.[name] NOT LIKE '%_cache'         AND t.[name] NOT IN ('FKs');

IF NOT EXISTS (SELECT 1 FROM sys.tables AS t                          WHERE t.[name]= 'FKs'                            AND t.[schema_id] = SCHEMA_ID('dbo'))     BEGIN       CREATE TABLE dbo.FKs (                           referencing_object      NVARCHAR(255) NOT NULL,                           constraint_column_id    INT NOT NULL,                           referencing_column_name NVARCHAR(255) NOT NULL,                           referenced_object       NVARCHAR(255) NOT NULL,                           referenced_column_name  NVARCHAR(255) NOT NULL,                           constraint_name         NVARCHAR(255) NOT NULL,                           delete_referential_action INT NOT NULL,                           update_referential_action INT NOT NULL                         );     END     ELSE IF (@isClearTableFKs = 1)     BEGIN       TRUNCATE TABLE dbo.FKs;     END      INSERT INTO dbo.FKs (                   referencing_object,                   constraint_column_id,                   referencing_column_name,                   referenced_object,                   referenced_column_name,                   constraint_name,                   delete_referential_action,                   update_referential_action                  )     SELECT CONCAT('[', SCHEMA_NAME(P.[schema_id]), '].['             , OBJECT_NAME(FK.parent_object_id), ']') AS referencing_object,     FK.constraint_column_id,     CONCAT('['     , COL_NAME(FK.parent_object_id, FK.parent_column_id)            , ']') AS referencing_column_name,     CONCAT('['     , SCHEMA_NAME(R.[schema_id]), '].['     , OBJECT_NAME(FK.referenced_object_id)         , ']') AS referenced_object,     CONCAT('['            , COL_NAME(FK.referenced_object_id, FK.referenced_column_id)            , ']') AS referenced_column_name,     CONCAT('['            , OBJECT_NAME(FK.constraint_object_id)            , ']') AS constraint_name,     FKK.delete_referential_action,     FKK.update_referential_action     FROM sys.foreign_key_columns AS FK     INNER JOIN sys.foreign_keys AS FKK ON FKK.[object_id]                                                  = FK.constraint_object_id     INNER JOIN sys.tables AS P ON P.[object_id] = FK.parent_object_id     INNER JOIN sys.tables AS R ON R.[object_id] = FK.referenced_object_id     WHERE NOT EXISTS (SELECT 1 FROM dbo.FKs AS t0                       WHERE t0.constraint_name = CONCAT('['                             , OBJECT_NAME(FK.constraint_object_id), ']'));      DELETE FROM trg     FROM dbo.FKs AS trg     WHERE NOT EXISTS (                       SELECT 1                       FROM #tbls AS src                       WHERE trg.referencing_object = CONCAT('['                                     , src.sch_name, '].[', src.[name], ']')                          OR trg.referenced_object  = CONCAT('['                                     , src.sch_name, '].[', src.[name], ']')                      )      DECLARE r_cursor_fk_drop CURSOR LOCAL FAST_FORWARD READ_ONLY FOR         SELECT t.referencing_object,                t.referenced_object,                t.constraint_name         FROM dbo.FKs AS t         WHERE EXISTS (SELECT 1 FROM sys.foreign_key_columns AS FK                        WHERE t.constraint_name = CONCAT('['                                     , OBJECT_NAME(FK.constraint_object_id)                                                   , ']'))         GROUP BY t.referencing_object,                  t.referenced_object,                  t.constraint_name;      OPEN r_cursor_fk_drop;     FETCH NEXT FROM r_cursor_fk_drop     INTO @referencing_object,          @referenced_object,          @constraint_name      WHILE @@FETCH_STATUS = 0     BEGIN         SET @stm = CONCAT('ALTER TABLE ', @referencing_object                         , ' DROP CONSTRAINT ', @constraint_name, ';');          PRINT @stm;          BEGIN TRY           BEGIN TRANSACTION;             EXEC sys.sp_executesql @stmt = @stm;           COMMIT;         END TRY         BEGIN CATCH           SET @ERROR = ERROR_MESSAGE();           PRINT @ERROR;           IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION         END CATCH          FETCH NEXT FROM r_cursor_fk_drop         INTO @referencing_object,              @referenced_object,              @constraint_name;     END      CLOSE r_cursor_fk_drop;     DEALLOCATE r_cursor_fk_drop;

DECLARE r_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT t.[name],        t.sch_name,        t.IsNOTInsert   FROM #tbls AS t   ORDER BY t.[name] ASC;  OPEN r_cursor; FETCH NEXT FROM r_cursor INTO @table_name,      @schema_name,      @IsNOTInsert;  WHILE @@FETCH_STATUS = 0 BEGIN   SET @cols = '';   SET @is_identity = 0;   SET @col_name_identity = NULL;    SET @stm = CONCAT('TRUNCATE TABLE ', @DB                     , '.[', @schema_name, '].[', @table_name, ']');    PRINT @stm;    BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH    IF (@IsNOTInsert = 0)   BEGIN       SELECT @cols        = @cols + CASE WHEN @cols = ''                                THEN c.[name] ELSE ',' + c.name END,              @is_identity = @is_identity + c.is_identity,              @col_name_identity = CASE WHEN (c.is_identity = 1)                                         THEN c.[name]                                         ELSE @col_name_identity END         FROM sys.tables t,              sys.columns c         WHERE t.[object_id] = c.[object_id]           AND t.[name]      = @table_name           AND c.is_computed = 0;        SET @stm = '';        IF @is_identity > 0 SET @stm = CONCAT('SET IDENTITY_INSERT '             , @DB, '.[', @schema_name, '].[', @table_name, '] ON');        SET @stm = CONCAT(@stm, ' INSERT INTO ', @DB                   , '.[', @schema_name, '].[', @table_name                   , '](', @cols, ') SELECT ', @cols                   , ' FROM [',@DBMaster,'].['                   , @schema_name, '].[', @table_name, '] WITH(NOLOCK) ');        --здесь можно задать ограничение на наполнение данными       IF @HistoryLimited = 1       BEGIN         IF @table_name LIKE '%History'             SET @stm = CONCAT(@stm         , ' WHERE ChangeDateTime > DATEADD   (month, -1, SYSDATETIME()) ');       END        IF @is_identity > 0 SET @stm = CONCAT(@stm          , ' SET IDENTITY_INSERT ', @DB          , '.[', @schema_name, '].[', @table_name, '] OFF');              IF @is_identity > 0 SET @stm = CONCAT(@stm          , ' DBCC CHECKIDENT ("', @table_name, '")');        SET @StartMoment = SYSDATETIME();       SET @ERROR = NULL;        PRINT @stm;        BEGIN TRY         BEGIN TRANSACTION;           EXEC sys.sp_executesql @stmt = @stm;         COMMIT;       END TRY       BEGIN CATCH         SET @ERROR = ERROR_MESSAGE();         PRINT @ERROR;         IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION       END CATCH        SET @FinishMoment = SYSDATETIME();        SET @stm = CONCAT('SELECT COUNT_BIG(*) FROM '           , '[', @schema_name, '].[', @table_name, '] WITH (NOLOCK);');        DELETE FROM @cnt;        INSERT INTO @cnt (cnt)       EXEC sys.sp_executesql @stmt = @stm;        INSERT INTO #tbl_res (                 SchName,                 TblName,                 StartMoment,                 FinishMoment,                 Cnt,                 ErrorMsg                )       SELECT @schema_name,              @table_name,              @StartMoment,              @FinishMoment,              COALESCE((SELECT SUM(cnt) FROM @cnt), 0) AS Cnt,              @ERROR;   END    FETCH NEXT FROM r_cursor   INTO @table_name,        @schema_name,        @IsNOTInsert; END CLOSE r_cursor; DEALLOCATE r_cursor;      

WHILE (@RowCount > 0) BEGIN   SET @RowCount = 0;   SET @WhileDelCount += 1;    DECLARE r_cursor_fk_corr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR   SELECT t.referencing_object,          t.referenced_object,          t.constraint_name,          STRING_AGG (CONCAT('(trg.', t.referencing_column_name          , '=src.', t.referenced_column_name, ')'), ' AND ')           WITHIN GROUP (ORDER BY t.constraint_column_id ASC) AS rules,          STRING_AGG (CONCAT('(trg.', t.referencing_column_name          , ' IS NOT NULL)'), ' AND ')           WITHIN GROUP (ORDER BY t.constraint_column_id ASC) AS key_cols   FROM dbo.FKs AS t   GROUP BY t.referencing_object,            t.referenced_object,            t.constraint_name;  OPEN r_cursor_fk_corr; FETCH NEXT FROM r_cursor_fk_corr INTO @referencing_object    , @referenced_object    , @constraint_name    , @rules    , @key_cols;  WHILE @@FETCH_STATUS = 0 BEGIN     SET @stm = CONCAT('DELETE FROM trg FROM ', @referencing_object     ,' AS trg WHERE ', @key_cols     , ' AND NOT EXISTS (SELECT 1 FROM ', @referenced_object,     ' AS src WITH (NOLOCK) WHERE ', @rules, ');');      PRINT @stm;      BEGIN TRY       BEGIN TRANSACTION;         EXEC sys.sp_executesql @stmt = @stm;          SET @RowCount += @@ROWCOUNT;       COMMIT;     END TRY     BEGIN CATCH       SET @ERROR = ERROR_MESSAGE();       PRINT @ERROR;       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION     END CATCH      FETCH NEXT FROM r_cursor_fk_corr     INTO @referencing_object        , @referenced_object        , @constraint_name        , @rules        , @key_cols; END    CLOSE r_cursor_fk_corr;   DEALLOCATE r_cursor_fk_corr; END  PRINT CONCAT('WHILE DELETE COUNT: ', @WhileDelCount);

DECLARE r_cursor_stat CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT CONCAT('UPDATE STATISTICS ', @DB, '.[' , t.sch_name, '].[', t.[name], '] WITH FULLSCAN;') AS stm FROM #tbls AS t;  OPEN r_cursor_stat; FETCH NEXT FROM r_cursor_stat INTO @stm;  WHILE @@FETCH_STATUS = 0 BEGIN   PRINT @stm;    BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH    FETCH NEXT FROM r_cursor_stat   INTO @stm END  CLOSE r_cursor_stat; DEALLOCATE r_cursor_stat;

DECLARE r_cursor_trigg_on CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT FORMATMESSAGE('ALTER TABLE [%s].[%s] ENABLE TRIGGER [%s];'    + CHAR(13), SCHEMA_NAME(b.[schema_id])   , OBJECT_NAME(t.parent_id), t.[Name]) AS stm FROM sys.triggers          t LEFT JOIN sys.tables  b ON b.[object_id] = t.parent_id WHERE t.is_disabled = 1   AND t.[type_desc] = 'SQL_TRIGGER'   AND OBJECT_NAME(t.parent_id) IS NOT NULL  OPEN r_cursor_trigg_on; FETCH NEXT FROM r_cursor_trigg_on INTO @stm;  WHILE @@FETCH_STATUS = 0 BEGIN   PRINT @stm;    BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH    FETCH NEXT FROM r_cursor_trigg_on   INTO @stm; END  CLOSE r_cursor_trigg_on; DEALLOCATE r_cursor_trigg_on;  SET @stm = '';  SELECT @stm += FORMATMESSAGE('ENABLE TRIGGER [%s] ON DATABASE;'    + CHAR(13), t.[Name])   FROM sys.triggers          t   WHERE t.is_disabled = 0     AND t.[type_desc] = 'SQL_TRIGGER'     AND OBJECT_NAME(t.parent_id) IS NULL;  PRINT @stm;  BEGIN TRY   BEGIN TRANSACTION;     EXEC sys.sp_executesql @stmt = @stm;   COMMIT; END TRY BEGIN CATCH   SET @ERROR = ERROR_MESSAGE();   PRINT @ERROR;   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION END CATCH

DECLARE r_cursor_fk_recover CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT t.referencing_object,        t.referenced_object,        t.constraint_name,        STRING_AGG (t.referencing_column_name, ',')         WITHIN GROUP (ORDER BY t.constraint_column_id ASC)          AS referencing_columns,        STRING_AGG (t.referenced_column_name, ',')         WITHIN GROUP (ORDER BY t.constraint_column_id ASC)          AS referenced_columns,        t.delete_referential_action,        t.update_referential_action FROM dbo.FKs AS t WHERE NOT EXISTS (SELECT 1 FROM sys.foreign_key_columns AS FK  WHERE t.constraint_name = CONCAT('[', OBJECT_NAME(FK.constraint_object_id)                                  , ']')) GROUP BY t.referencing_object,          t.referenced_object,          t.constraint_name,          t.delete_referential_action,          t.update_referential_action;  OPEN r_cursor_fk_recover; FETCH NEXT FROM r_cursor_fk_recover INTO @referencing_object    , @referenced_object    , @constraint_name    , @referencing_columns    , @referenced_columns    , @delete_referential_action    , @update_referential_action;  WHILE @@FETCH_STATUS = 0 BEGIN     SET @stm = CONCAT('ALTER TABLE ', @referencing_object     ,' WITH CHECK ADD  CONSTRAINT ', @constraint_name,     ' FOREIGN KEY(', @referencing_columns, ') REFERENCES '     , @referenced_object, ' (', @referenced_columns, ') '     , CASE         WHEN @delete_referential_action = 1           THEN 'ON DELETE CASCADE '         WHEN @delete_referential_action = 2           THEN 'ON DELETE SET NULL '         ELSE ''       END     , CASE         WHEN @update_referential_action = 1           THEN 'ON UPDATE CASCADE '         WHEN @update_referential_action = 2           THEN 'ON UPDATE SET NULL '         ELSE ''       END     , '; '     , 'ALTER TABLE ', @referencing_object, ' CHECK CONSTRAINT '     , @constraint_name, '; ');      PRINT @stm;      BEGIN TRY       BEGIN TRANSACTION;         EXEC sys.sp_executesql @stmt = @stm;       COMMIT;     END TRY     BEGIN CATCH       SET @ERROR = ERROR_MESSAGE();       PRINT @ERROR;       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION     END CATCH      FETCH NEXT FROM r_cursor_fk_recover     INTO @referencing_object        , @referenced_object        , @constraint_name        , @referencing_columns        , @referenced_columns        , @delete_referential_action        , @update_referential_action; END  CLOSE r_cursor_fk_recover; DEALLOCATE r_cursor_fk_recover;

SET NOCOUNT OFF;  DECLARE @DB       VARCHAR(250) = QUOTENAME(DB_NAME()),         @DBMaster VARCHAR(255) = 'БД-мастер',                 @ERROR    VARCHAR(MAX);  DECLARE @HistoryLimited            bit = 1,         @table_name                nvarchar(255),         @is_identity               int = 0,         @stm                       nvarchar(max) = '',         @cols                      nvarchar(max) = '',         @IsNOTInsert               bit,         @schema_name               nvarchar(255),         @col_name_identity         nvarchar(255),         @referencing_object        nvarchar(255),         @referenced_object         nvarchar(255),         @constraint_name           nvarchar(255),         @referencing_columns       nvarchar(max),         @referenced_columns        nvarchar(max),         @rules                     nvarchar(max),         @key_cols                  nvarchar(max),         @StartMoment               DATETIME2,         @FinishMoment              DATETIME2,         @delete_referential_action INT,         @update_referential_action INT,         @max_row_insert            INT = 100000,         @isClearTableFKs           BIT = 1,         @RowCount                  BIGINT = 1,         @WhileDelCount             INT = 0;         ;  DECLARE @cnt TABLE (cnt BIGINT NOT NULL);  DROP TABLE IF EXISTS #tbl_res;  CREATE TABLE #tbl_res (                          SchName       NVARCHAR(255) NOT NULL,                          TblName       NVARCHAR(255) NOT NULL,                          StartMoment   DATETIME2     NOT NULL,                          FinishMoment  DATETIME2     NOT NULL,                          Cnt           BIGINT        NOT NULL,                          ErrorMsg      NVARCHAR(MAX) NULL                        );  EXEC sys.sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";  DECLARE r_cursor_trigg_off CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT FORMATMESSAGE('ALTER TABLE [%s].[%s] DISABLE TRIGGER [%s];'    + CHAR(13), SCHEMA_NAME(b.[schema_id]), OBJECT_NAME(t.parent_id)   , t.[Name]) AS stm FROM sys.triggers          t LEFT JOIN sys.tables  b ON b.object_id = t.parent_id WHERE t.is_disabled = 0   AND t.type_desc = 'SQL_TRIGGER'   AND OBJECT_NAME(t.parent_id) IS NOT NULL ORDER BY SCHEMA_NAME(b.[schema_id]) ASC,          OBJECT_NAME(t.parent_id) ASC;  OPEN r_cursor_trigg_off; FETCH NEXT FROM r_cursor_trigg_off INTO @stm;  WHILE @@FETCH_STATUS = 0 BEGIN   PRINT @stm;    BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH      FETCH NEXT FROM r_cursor_trigg_off   INTO @stm; END  CLOSE r_cursor_trigg_off; DEALLOCATE r_cursor_trigg_off;  SET @stm = '';  SELECT @stm += FORMATMESSAGE('DISABLE TRIGGER [%s] ON DATABASE;'   + CHAR(13), t.[Name]) FROM sys.triggers          t LEFT JOIN sys.tables  b ON b.[object_id] = t.parent_id    WHERE t.is_disabled = 0      AND t.[type_desc] = 'SQL_TRIGGER'      AND OBJECT_NAME(t.parent_id) IS NULL;  PRINT @stm;  BEGIN TRY   BEGIN TRANSACTION;     EXEC sys.sp_executesql @stmt = @stm;   COMMIT; END TRY BEGIN CATCH   SET @ERROR = ERROR_MESSAGE();   PRINT @ERROR;   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION END CATCH  DROP TABLE IF EXISTS #tbls; CREATE TABLE #tbls (                     [name]      NVARCHAR(255) NOT NULL,                     sch_name    NVARCHAR(255) NOT NULL,                     IsNOTInsert BIT           NOT NULL                    );  INSERT INTO #tbls (                     [name],                     sch_name,                     IsNOTInsert                   )   SELECT t.[name],          SCHEMA_NAME(t.[schema_id]) AS sch_name,          --задаётся правило, по которому определяем           --нужно ли после очистки наполнять данными таблицу или нет          --по умолчанию нужно (0-да, 1-нет)          0 AS IsNOTInsert       FROM sys.tables AS t       --в фильтре задаем какие таблицы брать в расчет       --(в нашем случае какие не брать в расчет)       WHERE t.[name] NOT LIKE 'unused%'         AND t.[name] NOT LIKE 'removed%'         AND t.[name] NOT LIKE 'migrated%'         AND t.[name] NOT LIKE 'migration%'         AND t.[name] NOT LIKE 'sysdiag%'         AND t.[name] NOT LIKE 'test%'         AND t.[name] NOT LIKE 'tmp%'         AND t.[name] NOT LIKE '%_cache'         AND t.[name] NOT IN ('FKs');  IF NOT EXISTS (SELECT 1 FROM sys.tables AS t   WHERE t.[name]= 'FKs' AND t.[schema_id] = SCHEMA_ID('dbo')) BEGIN   CREATE TABLE dbo.FKs (                       referencing_object      NVARCHAR(255) NOT NULL,                       constraint_column_id    INT NOT NULL,                       referencing_column_name NVARCHAR(255) NOT NULL,                       referenced_object       NVARCHAR(255) NOT NULL,                       referenced_column_name  NVARCHAR(255) NOT NULL,                       constraint_name         NVARCHAR(255) NOT NULL,                       delete_referential_action INT NOT NULL,                       update_referential_action INT NOT NULL                     ); END ELSE IF (@isClearTableFKs = 1) BEGIN   TRUNCATE TABLE dbo.FKs; END  INSERT INTO dbo.FKs (               referencing_object,               constraint_column_id,               referencing_column_name,               referenced_object,               referenced_column_name,               constraint_name,               delete_referential_action,               update_referential_action              ) SELECT CONCAT('[', SCHEMA_NAME(P.[schema_id]), '].['   , OBJECT_NAME(FK.parent_object_id), ']') AS referencing_object,   FK.constraint_column_id, CONCAT('[', COL_NAME(FK.parent_object_id   , FK.parent_column_id), ']') AS referencing_column_name, CONCAT('[', SCHEMA_NAME(R.[schema_id]), '].['   , OBJECT_NAME(FK.referenced_object_id), ']') AS referenced_object, CONCAT('[', COL_NAME(FK.referenced_object_id   , FK.referenced_column_id), ']') AS referenced_column_name, CONCAT('[', OBJECT_NAME(FK.constraint_object_id)   , ']') AS constraint_name, FKK.delete_referential_action, FKK.update_referential_action FROM sys.foreign_key_columns AS FK INNER JOIN sys.foreign_keys AS FKK ON FKK.[object_id]                                        = FK.constraint_object_id INNER JOIN sys.tables AS P ON P.[object_id] = FK.parent_object_id INNER JOIN sys.tables AS R ON R.[object_id] = FK.referenced_object_id WHERE NOT EXISTS (SELECT 1 FROM dbo.FKs AS t0    WHERE t0.constraint_name = CONCAT('['                             , OBJECT_NAME(FK.constraint_object_id), ']'));    DELETE FROM trg FROM dbo.FKs AS trg WHERE NOT EXISTS (                   SELECT 1                   FROM #tbls AS src                   WHERE trg.referencing_object = CONCAT('[', src.sch_name                           , '].[', src.[name], ']')                      OR trg.referenced_object  = CONCAT('[', src.sch_name                           , '].[', src.[name], ']')                  )  DECLARE r_cursor_fk_drop CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT t.referencing_object,        t.referenced_object,        t.constraint_name FROM dbo.FKs AS t WHERE EXISTS (SELECT 1 FROM sys.foreign_key_columns AS FK    WHERE t.constraint_name = CONCAT('['                             , OBJECT_NAME(FK.constraint_object_id), ']')) GROUP BY t.referencing_object,          t.referenced_object,          t.constraint_name;  OPEN r_cursor_fk_drop; FETCH NEXT FROM r_cursor_fk_drop INTO @referencing_object,      @referenced_object,      @constraint_name  WHILE @@FETCH_STATUS = 0 BEGIN   SET @stm = CONCAT('ALTER TABLE ', @referencing_object     , ' DROP CONSTRAINT ', @constraint_name, ';');      PRINT @stm;      BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH      FETCH NEXT FROM r_cursor_fk_drop   INTO @referencing_object,        @referenced_object,        @constraint_name; END  CLOSE r_cursor_fk_drop; DEALLOCATE r_cursor_fk_drop;  DECLARE r_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT t.[name],        t.sch_name,        t.IsNOTInsert   FROM #tbls AS t   ORDER BY t.[name] ASC;  OPEN r_cursor; FETCH NEXT FROM r_cursor INTO @table_name,      @schema_name,      @IsNOTInsert;  WHILE @@FETCH_STATUS = 0 BEGIN   SET @cols = '';   SET @is_identity = 0;   SET @col_name_identity = NULL;      SET @stm = CONCAT('TRUNCATE TABLE ', @DB                     , '.[', @schema_name, '].[', @table_name, ']');      PRINT @stm;      BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH      IF (@IsNOTInsert = 0)   BEGIN     SELECT @cols        = @cols + CASE WHEN @cols = ''                    THEN c.[name] ELSE ',' + c.name END,            @is_identity = @is_identity + c.is_identity,            @col_name_identity = CASE WHEN (c.is_identity = 1)                      THEN c.[name] ELSE @col_name_identity END       FROM sys.tables t,            sys.columns c       WHERE t.[object_id] = c.[object_id]         AND t.[name]      = @table_name         AND c.is_computed = 0;      SET @stm = '';      IF @is_identity > 0 SET @stm = CONCAT('SET IDENTITY_INSERT '           , @DB, '.[', @schema_name, '].[', @table_name, '] ON');      SET @stm = CONCAT(@stm, ' INSERT INTO ', @DB, '.['               , @schema_name, '].[', @table_name                       , '](', @cols, ') SELECT ', @cols                       , ' FROM [',@DBMaster,'].['                       , @schema_name, '].['                       , @table_name, '] WITH(NOLOCK) ');      --здесь можно задать ограничение на наполнение данными     IF @HistoryLimited = 1     BEGIN       IF @table_name LIKE '%History'           SET @stm = CONCAT(@stm         , ' WHERE ChangeDateTime > DATEADD   (month, -1, SYSDATETIME()) ');     END      IF @is_identity > 0 SET @stm = CONCAT(@stm, ' SET IDENTITY_INSERT '         , @DB, '.[', @schema_name, '].[', @table_name, '] OFF');     IF @is_identity > 0 SET @stm = CONCAT(@stm, ' DBCC CHECKIDENT ("'         , @table_name, '")');      SET @StartMoment = SYSDATETIME();     SET @ERROR = NULL;      PRINT @stm;      BEGIN TRY       BEGIN TRANSACTION;         EXEC sys.sp_executesql @stmt = @stm;       COMMIT;     END TRY     BEGIN CATCH       SET @ERROR = ERROR_MESSAGE();       PRINT @ERROR;       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION     END CATCH      SET @FinishMoment = SYSDATETIME();      SET @stm = CONCAT('SELECT COUNT_BIG(*) FROM ', '[', @schema_name         , '].[', @table_name, '] WITH (NOLOCK);');      DELETE FROM @cnt;      INSERT INTO @cnt (cnt)     EXEC sys.sp_executesql @stmt = @stm;      INSERT INTO #tbl_res (               SchName,               TblName,               StartMoment,               FinishMoment,               Cnt,               ErrorMsg              )     SELECT @schema_name,            @table_name,            @StartMoment,            @FinishMoment,            COALESCE((SELECT SUM(cnt) FROM @cnt), 0) AS Cnt,            @ERROR;   END    FETCH NEXT FROM r_cursor   INTO @table_name,        @schema_name,        @IsNOTInsert; END CLOSE r_cursor; DEALLOCATE r_cursor;  WHILE (@RowCount > 0) BEGIN   SET @RowCount = 0;   SET @WhileDelCount += 1;    DECLARE r_cursor_fk_corr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR   SELECT t.referencing_object,          t.referenced_object,          t.constraint_name,          STRING_AGG (CONCAT('(trg.', t.referencing_column_name           , '=src.', t.referenced_column_name, ')'), ' AND ')            WITHIN GROUP (ORDER BY t.constraint_column_id ASC) AS rules,          STRING_AGG (CONCAT('(trg.', t.referencing_column_name           , ' IS NOT NULL)'), ' AND ')            WITHIN GROUP (ORDER BY t.constraint_column_id ASC) AS key_cols   FROM dbo.FKs AS t   GROUP BY t.referencing_object,            t.referenced_object,            t.constraint_name;    OPEN r_cursor_fk_corr;   FETCH NEXT FROM r_cursor_fk_corr   INTO @referencing_object      , @referenced_object      , @constraint_name      , @rules      , @key_cols;    WHILE @@FETCH_STATUS = 0   BEGIN       SET @stm = CONCAT('DELETE FROM trg FROM ', @referencing_object         ,' AS trg WHERE ', @key_cols, ' AND NOT EXISTS (SELECT 1 FROM '         , @referenced_object,       ' AS src WITH (NOLOCK) WHERE ', @rules, ');');        PRINT @stm;        BEGIN TRY         BEGIN TRANSACTION;           EXEC sys.sp_executesql @stmt = @stm;            SET @RowCount += @@ROWCOUNT;         COMMIT;       END TRY       BEGIN CATCH         SET @ERROR = ERROR_MESSAGE();         PRINT @ERROR;         IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION       END CATCH        FETCH NEXT FROM r_cursor_fk_corr       INTO @referencing_object          , @referenced_object          , @constraint_name          , @rules          , @key_cols;   END    CLOSE r_cursor_fk_corr;   DEALLOCATE r_cursor_fk_corr; END  PRINT CONCAT('WHILE DELETE COUNT: ', @WhileDelCount);  DECLARE r_cursor_stat CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT CONCAT('UPDATE STATISTICS ', @DB, '.[', t.sch_name, '].['   , t.[name], '] WITH FULLSCAN;') AS stm FROM #tbls AS t;  OPEN r_cursor_stat; FETCH NEXT FROM r_cursor_stat INTO @stm;  WHILE @@FETCH_STATUS = 0 BEGIN   PRINT @stm;    BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH    FETCH NEXT FROM r_cursor_stat   INTO @stm END  CLOSE r_cursor_stat; DEALLOCATE r_cursor_stat;  DECLARE r_cursor_trigg_on CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT FORMATMESSAGE('ALTER TABLE [%s].[%s] ENABLE TRIGGER [%s];'    + CHAR(13), SCHEMA_NAME(b.[schema_id]), OBJECT_NAME(t.parent_id)                      , t.[Name]) AS stm FROM sys.triggers          t LEFT JOIN sys.tables  b ON b.[object_id] = t.parent_id WHERE t.is_disabled = 1   AND t.[type_desc] = 'SQL_TRIGGER'   AND OBJECT_NAME(t.parent_id) IS NOT NULL  OPEN r_cursor_trigg_on; FETCH NEXT FROM r_cursor_trigg_on INTO @stm;  WHILE @@FETCH_STATUS = 0 BEGIN   PRINT @stm;    BEGIN TRY     BEGIN TRANSACTION;       EXEC sys.sp_executesql @stmt = @stm;     COMMIT;   END TRY   BEGIN CATCH     SET @ERROR = ERROR_MESSAGE();     PRINT @ERROR;     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION   END CATCH    FETCH NEXT FROM r_cursor_trigg_on   INTO @stm; END  CLOSE r_cursor_trigg_on; DEALLOCATE r_cursor_trigg_on;  SET @stm = '';  SELECT @stm += FORMATMESSAGE('ENABLE TRIGGER [%s] ON DATABASE;'                               + CHAR(13), t.[Name])   FROM sys.triggers          t   WHERE t.is_disabled = 0     AND t.[type_desc] = 'SQL_TRIGGER'     AND OBJECT_NAME(t.parent_id) IS NULL;  PRINT @stm;  BEGIN TRY   BEGIN TRANSACTION;     EXEC sys.sp_executesql @stmt = @stm;   COMMIT; END TRY BEGIN CATCH   SET @ERROR = ERROR_MESSAGE();   PRINT @ERROR;   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION END CATCH  DECLARE r_cursor_fk_recover CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT t.referencing_object,        t.referenced_object,        t.constraint_name,        STRING_AGG (t.referencing_column_name, ',')          WITHIN GROUP (ORDER BY t.constraint_column_id ASC)          AS referencing_columns,        STRING_AGG (t.referenced_column_name, ',')          WITHIN GROUP (ORDER BY t.constraint_column_id ASC)          AS referenced_columns,        t.delete_referential_action,        t.update_referential_action FROM dbo.FKs AS t WHERE NOT EXISTS (SELECT 1 FROM sys.foreign_key_columns AS FK    WHERE t.constraint_name = CONCAT('['                           , OBJECT_NAME(FK.constraint_object_id), ']')) GROUP BY t.referencing_object,          t.referenced_object,          t.constraint_name,          t.delete_referential_action,          t.update_referential_action;  OPEN r_cursor_fk_recover; FETCH NEXT FROM r_cursor_fk_recover INTO @referencing_object    , @referenced_object    , @constraint_name    , @referencing_columns    , @referenced_columns    , @delete_referential_action    , @update_referential_action;  WHILE @@FETCH_STATUS = 0 BEGIN     SET @stm = CONCAT('ALTER TABLE ', @referencing_object                       ,' WITH CHECK ADD  CONSTRAINT ', @constraint_name,     ' FOREIGN KEY(', @referencing_columns, ') REFERENCES '                       , @referenced_object, ' (', @referenced_columns, ') '     , CASE         WHEN @delete_referential_action = 1           THEN 'ON DELETE CASCADE '         WHEN @delete_referential_action = 2           THEN 'ON DELETE SET NULL '         ELSE ''       END     , CASE         WHEN @update_referential_action = 1           THEN 'ON UPDATE CASCADE '         WHEN @update_referential_action = 2           THEN 'ON UPDATE SET NULL '         ELSE ''       END     , '; '     , 'ALTER TABLE ', @referencing_object, ' CHECK CONSTRAINT '                       , @constraint_name, '; ');      PRINT @stm;      BEGIN TRY       BEGIN TRANSACTION;         EXEC sys.sp_executesql @stmt = @stm;       COMMIT;     END TRY     BEGIN CATCH       SET @ERROR = ERROR_MESSAGE();       PRINT @ERROR;       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION     END CATCH      FETCH NEXT FROM r_cursor_fk_recover     INTO @referencing_object        , @referenced_object        , @constraint_name        , @referencing_columns        , @referenced_columns        , @delete_referential_action        , @update_referential_action; END  CLOSE r_cursor_fk_recover; DEALLOCATE r_cursor_fk_recover;  EXEC sys.sp_msforeachtable @commAND1="PRINT '?'" , @commAND2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";  SELECT t.SchName,        t.TblName,        t.Cnt,        DATEDIFF(millisecond, t.StartMoment, t.FinishMoment) AS DiffMSec,        t.ErrorMsg FROM #tbl_res AS t ORDER BY t.SchName ASC, t.TblName ASC;