Перенесення всіх View в іншу базу
Скрипт проходить по всіх View бази TCC і генерує скрипт для створення всіх View, які є в цій базі.
SQL
/*
Base2Base VIEW deployment script generator.
Запускати в ДЖЕРЕЛЬНІЙ базі даних з довідниками.
Потрібно вказати лише ім'я цільової бази.
Наприклад:
Source database: CRM_MHP
Source archive: CRM_MHP_Archive
Target database: TradeControlCenter.Db11743
Target archive: TradeControlCenter.Db11743Archive
Генератор нічого не створює в цільовій базі.
Він лише формує deployment-скрипт у вкладці Messages.
Під час виконання deployment-скрипта:
- кожна VIEW створюється окремо;
- помилка створення VIEW не зупиняє процес;
- проблемна VIEW пропускається;
- наприкінці виводиться перелік VIEW, які не вдалося створити.
*/
-------------------------------------------------------------------------------
-- Прибираємо temp-таблиці від можливого попереднього запуску.
--
-- GO тут важливий: наступний batch буде компілюватися вже без старих
-- визначень temp-таблиць.
-------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#ViewScripts') IS NOT NULL
DROP TABLE #ViewScripts;
IF OBJECT_ID('tempdb..#OutputLines') IS NOT NULL
DROP TABLE #OutputLines;
GO
SET NOCOUNT ON;
-------------------------------------------------------------------------------
-- Єдиний параметр, який потрібно змінювати.
-------------------------------------------------------------------------------
DECLARE @TargetDatabase sysname = N'TradeControlCenter.Db11743';
-------------------------------------------------------------------------------
-- Імена джерельних та цільових баз.
-------------------------------------------------------------------------------
DECLARE @SourceDatabase sysname = DB_NAME();
-- Джерельна Archive-база:
-- CRM_MHP -> CRM_MHP_Archive
DECLARE @SourceArchiveDatabase sysname = @SourceDatabase + N'_Archive';
-- Цільова Archive-база:
-- TradeControlCenter.Db11743 -> TradeControlCenter.Db11743Archive
DECLARE @TargetArchiveDatabase sysname = @TargetDatabase + N'Archive';
-------------------------------------------------------------------------------
-- Таблиця VIEW, які потрібно перенести.
-------------------------------------------------------------------------------
CREATE TABLE #ViewScripts
(
ObjectId int NOT NULL,
SchemaName sysname NOT NULL,
ViewName sysname NOT NULL,
DependencyLevel int NOT NULL,
UsesAnsiNulls bit NOT NULL,
UsesQuotedIdentifier bit NOT NULL,
Definition nvarchar(max) NOT NULL
);
-------------------------------------------------------------------------------
-- Визначаємо залежності VIEW між собою.
--
-- Якщо ViewA використовує ViewB, то ViewB намагаємося створити раніше.
-------------------------------------------------------------------------------
;WITH ViewDependencies AS
(
SELECT DISTINCT
sed.referencing_id,
sed.referenced_id
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.views referencingView
ON referencingView.object_id = sed.referencing_id
INNER JOIN sys.views referencedView
ON referencedView.object_id = sed.referenced_id
WHERE
referencingView.is_ms_shipped = 0
AND referencedView.is_ms_shipped = 0
),
ViewLevels AS
(
---------------------------------------------------------------------------
-- VIEW, які не залежать від інших VIEW цієї бази.
---------------------------------------------------------------------------
SELECT
v.object_id,
0 AS DependencyLevel,
CAST(
N'|' + CONVERT(nvarchar(20), v.object_id) + N'|'
AS nvarchar(max)
) AS DependencyPath
FROM sys.views v
WHERE
v.is_ms_shipped = 0
AND NOT EXISTS
(
SELECT 1
FROM ViewDependencies vd
WHERE vd.referencing_id = v.object_id
)
UNION ALL
---------------------------------------------------------------------------
-- VIEW наступних рівнів залежності.
---------------------------------------------------------------------------
SELECT
vd.referencing_id,
vl.DependencyLevel + 1,
CAST(
vl.DependencyPath
+ CONVERT(nvarchar(20), vd.referencing_id)
+ N'|'
AS nvarchar(max)
) AS DependencyPath
FROM ViewLevels vl
INNER JOIN ViewDependencies vd
ON vd.referenced_id = vl.object_id
WHERE
vl.DependencyPath NOT LIKE
N'%|' + CONVERT(nvarchar(20), vd.referencing_id) + N'|%'
),
FinalViewLevels AS
(
SELECT
v.object_id,
ISNULL(MAX(vl.DependencyLevel), 0) AS DependencyLevel
FROM sys.views v
LEFT OUTER JOIN ViewLevels vl
ON vl.object_id = v.object_id
WHERE
v.is_ms_shipped = 0
GROUP BY
v.object_id
)
-------------------------------------------------------------------------------
-- Отримуємо definition кожної VIEW та адаптуємо її до цільових баз.
-------------------------------------------------------------------------------
INSERT INTO #ViewScripts
(
ObjectId,
SchemaName,
ViewName,
DependencyLevel,
UsesAnsiNulls,
UsesQuotedIdentifier,
Definition
)
SELECT
v.object_id,
s.name,
v.name,
vl.DependencyLevel,
sm.uses_ansi_nulls,
sm.uses_quoted_identifier,
REPLACE
(
REPLACE
(
REPLACE
(
REPLACE
(
-------------------------------------------------------------------
-- CREATE VIEW / ALTER VIEW -> CREATE OR ALTER VIEW.
-------------------------------------------------------------------
CASE
WHEN UPPER(LEFT(LTRIM(sm.definition), 20)) = N'CREATE OR ALTER VIEW'
THEN LTRIM(sm.definition)
WHEN UPPER(LEFT(LTRIM(sm.definition), 11)) = N'CREATE VIEW'
THEN
N'CREATE OR ALTER VIEW'
+ SUBSTRING(
LTRIM(sm.definition),
12,
LEN(LTRIM(sm.definition))
)
WHEN UPPER(LEFT(LTRIM(sm.definition), 10)) = N'ALTER VIEW'
THEN
N'CREATE OR ALTER VIEW'
+ SUBSTRING(
LTRIM(sm.definition),
11,
LEN(LTRIM(sm.definition))
)
ELSE
LTRIM(sm.definition)
END,
-------------------------------------------------------------------
-- [CRM_MHP_Archive].
-- ->
-- [TradeControlCenter.Db11743Archive].
-------------------------------------------------------------------
QUOTENAME(@SourceArchiveDatabase) + N'.',
QUOTENAME(@TargetArchiveDatabase) + N'.'
),
-----------------------------------------------------------------------
-- CRM_MHP_Archive.
-- ->
-- [TradeControlCenter.Db11743Archive].
-----------------------------------------------------------------------
@SourceArchiveDatabase + N'.',
QUOTENAME(@TargetArchiveDatabase) + N'.'
),
---------------------------------------------------------------------------
-- [CRM_MHP].
-- ->
-- [TradeControlCenter.Db11743].
---------------------------------------------------------------------------
QUOTENAME(@SourceDatabase) + N'.',
QUOTENAME(@TargetDatabase) + N'.'
),
-----------------------------------------------------------------------------
-- CRM_MHP.
-- ->
-- [TradeControlCenter.Db11743].
-----------------------------------------------------------------------------
@SourceDatabase + N'.',
QUOTENAME(@TargetDatabase) + N'.'
)
FROM sys.views v
INNER JOIN sys.schemas s
ON s.schema_id = v.schema_id
INNER JOIN sys.sql_modules sm
ON sm.object_id = v.object_id
INNER JOIN FinalViewLevels vl
ON vl.object_id = v.object_id
WHERE
v.is_ms_shipped = 0
AND sm.definition IS NOT NULL
OPTION (MAXRECURSION 32767);
-------------------------------------------------------------------------------
-- Рядки готового deployment-скрипта.
--
-- Кожний елемент навмисно робимо коротшим за максимальний розмір PRINT
-- для nvarchar.
-------------------------------------------------------------------------------
CREATE TABLE #OutputLines
(
LineNumber bigint IDENTITY(1, 1) NOT NULL,
ScriptLine nvarchar(max) NOT NULL
);
-------------------------------------------------------------------------------
-- Заголовок deployment-скрипта.
-------------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N'-------------------------------------------------------------------------------'),
(N'-- Base2Base VIEW deployment script'),
(N'--'),
(N'-- Source database: ' + QUOTENAME(@SourceDatabase)),
(N'-- Source archive: ' + QUOTENAME(@SourceArchiveDatabase)),
(N'-- Target database: ' + QUOTENAME(@TargetDatabase)),
(N'-- Target archive: ' + QUOTENAME(@TargetArchiveDatabase)),
(N'-------------------------------------------------------------------------------'),
(N'');
-------------------------------------------------------------------------------
-- Перевірка існування цільової бази.
-------------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(
N'IF DB_ID(N'''
+ REPLACE(@TargetDatabase, N'''', N'''''')
+ N''') IS NULL'
),
(
N' THROW 50000, N''Target database '
+ REPLACE(@TargetDatabase, N'''', N'''''')
+ N' does not exist.'', 1;'
),
(N'GO'),
(N'');
-------------------------------------------------------------------------------
-- Перевірка Archive-бази.
-------------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(
N'IF DB_ID(N'''
+ REPLACE(@TargetArchiveDatabase, N'''', N'''''')
+ N''') IS NULL'
),
(
N' THROW 50000, N''Target archive database '
+ REPLACE(@TargetArchiveDatabase, N'''', N'''''')
+ N' does not exist.'', 1;'
),
(N'GO'),
(N'');
-------------------------------------------------------------------------------
-- Переходимо в цільову довідникову базу.
-------------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N'USE ' + QUOTENAME(@TargetDatabase) + N';'),
(N'GO'),
(N''),
(N'SET NOCOUNT ON;'),
(N'GO'),
(N'');
-------------------------------------------------------------------------------
-- Таблиця помилок deployment.
--
-- Temp-таблиця живе між batch-ами GO в межах тієї самої SSMS-сесії.
-------------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N'IF OBJECT_ID(''tempdb..#ViewDeploymentErrors'') IS NOT NULL'),
(N' DROP TABLE #ViewDeploymentErrors;'),
(N''),
(N'CREATE TABLE #ViewDeploymentErrors'),
(N'('),
(N' SchemaName sysname NOT NULL,'),
(N' ViewName sysname NOT NULL,'),
(N' ErrorNumber int NOT NULL,'),
(N' ErrorMessage nvarchar(4000) NOT NULL'),
(N');'),
(N'GO'),
(N'');
-------------------------------------------------------------------------------
-- Якщо є VIEW, definition яких прочитати неможливо, вони не потрапляють
-- у deployment.
-------------------------------------------------------------------------------
IF EXISTS
(
SELECT 1
FROM sys.views v
LEFT OUTER JOIN sys.sql_modules sm
ON sm.object_id = v.object_id
WHERE
v.is_ms_shipped = 0
AND sm.definition IS NULL
)
BEGIN
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N'-------------------------------------------------------------------------------'),
(N'-- WARNING'),
(N'-- Some source VIEW definitions cannot be read and were not included.'),
(N'-- Most likely they were created WITH ENCRYPTION.'),
(N'-------------------------------------------------------------------------------'),
(N'');
END;
-------------------------------------------------------------------------------
-- Генеруємо окремий deployment-block для кожної VIEW.
--
-- Важлива відмінність від попередньої версії:
--
-- definition НЕ вкладається одним величезним рядком у
--
-- EXEC sys.sp_executesql N'...'
--
-- Замість цього оригінальний текст VIEW спочатку ріжеться на шматки
-- по 1800 Unicode-символів, кожний шматок окремо екранується, після чого
-- у deployment-скрипті вони збираються назад у @ViewSql.
--
-- Таким чином:
--
-- 'E-Commerce Mobile'
--
-- гарантовано не втратить закриваючу лапку через обмеження SSMS/PRINT.
-------------------------------------------------------------------------------
DECLARE @SchemaName sysname;
DECLARE @ViewName sysname;
DECLARE @UsesAnsiNulls bit;
DECLARE @UsesQuotedIdentifier bit;
DECLARE @Definition nvarchar(max);
DECLARE @DisplayName nvarchar(600);
DECLARE @EscapedSchemaName nvarchar(256);
DECLARE @EscapedViewName nvarchar(256);
DECLARE @EscapedDisplayName nvarchar(1200);
DECLARE @DefinitionPosition int;
DECLARE @DefinitionLength int;
DECLARE @RawChunk nvarchar(1800);
DECLARE @EscapedChunk nvarchar(max);
DECLARE @ChunkNumber int;
DECLARE @GeneratedLine nvarchar(max);
DECLARE ViewScriptCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
SchemaName,
ViewName,
UsesAnsiNulls,
UsesQuotedIdentifier,
Definition
FROM #ViewScripts
ORDER BY
DependencyLevel,
SchemaName,
ViewName;
OPEN ViewScriptCursor;
FETCH NEXT FROM ViewScriptCursor
INTO
@SchemaName,
@ViewName,
@UsesAnsiNulls,
@UsesQuotedIdentifier,
@Definition;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DisplayName =
QUOTENAME(@SchemaName)
+ N'.'
+ QUOTENAME(@ViewName);
SET @EscapedSchemaName =
REPLACE(
@SchemaName,
N'''',
N''''''
);
SET @EscapedViewName =
REPLACE(
@ViewName,
N'''',
N''''''
);
SET @EscapedDisplayName =
REPLACE(
@DisplayName,
N'''',
N''''''
);
---------------------------------------------------------------------------
-- Заголовок VIEW.
---------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N'-------------------------------------------------------------------------------'),
(N'-- ' + @DisplayName),
(N'-------------------------------------------------------------------------------'),
(
N'SET ANSI_NULLS '
+ CASE
WHEN @UsesAnsiNulls = 1 THEN N'ON;'
ELSE N'OFF;'
END
),
(
N'SET QUOTED_IDENTIFIER '
+ CASE
WHEN @UsesQuotedIdentifier = 1 THEN N'ON;'
ELSE N'OFF;'
END
),
(N''),
(N'DECLARE @ViewSql nvarchar(max);');
---------------------------------------------------------------------------
-- Ріжемо ОРИГІНАЛЬНИЙ definition на невеликі шматки.
--
-- Важливо: ріжемо ДО екранування одинарних лапок.
-- Тому ми ніколи не можемо розрізати створену нами пару '' посередині.
--
-- Після цього кожний шматок екранується самостійно.
---------------------------------------------------------------------------
SET @DefinitionPosition = 1;
SET @DefinitionLength = LEN(@Definition);
SET @ChunkNumber = 0;
WHILE @DefinitionPosition <= @DefinitionLength
BEGIN
SET @RawChunk =
SUBSTRING(
@Definition,
@DefinitionPosition,
1800
);
-----------------------------------------------------------------------
-- Одинарна лапка всередині зовнішнього N'...' повинна бути
-- представлена двома одинарними лапками.
-----------------------------------------------------------------------
SET @EscapedChunk =
REPLACE(
@RawChunk,
N'''',
N''''''
);
IF @ChunkNumber = 0
BEGIN
SET @GeneratedLine =
N'SET @ViewSql = N'''
+ @EscapedChunk
+ N''';';
END
ELSE
BEGIN
SET @GeneratedLine =
N'SET @ViewSql = @ViewSql + N'''
+ @EscapedChunk
+ N''';';
END;
INSERT INTO #OutputLines
(
ScriptLine
)
VALUES
(
@GeneratedLine
);
SET @DefinitionPosition =
@DefinitionPosition + 1800;
SET @ChunkNumber =
@ChunkNumber + 1;
END;
---------------------------------------------------------------------------
-- Виконання VIEW через окремий dynamic SQL batch.
--
-- CREATE OR ALTER VIEW буде першим statement усередині @ViewSql.
---------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N''),
(N'BEGIN TRY'),
(N' EXEC sys.sp_executesql @ViewSql;'),
(N''),
(
N' PRINT N''CREATED: '
+ @EscapedDisplayName
+ N''';'
),
(N'END TRY'),
(N'BEGIN CATCH'),
(N' INSERT INTO #ViewDeploymentErrors'),
(N' ('),
(N' SchemaName,'),
(N' ViewName,'),
(N' ErrorNumber,'),
(N' ErrorMessage'),
(N' )'),
(N' VALUES'),
(N' ('),
(
N' N'''
+ @EscapedSchemaName
+ N''','
),
(
N' N'''
+ @EscapedViewName
+ N''','
),
(N' ERROR_NUMBER(),'),
(N' ERROR_MESSAGE()'),
(N' );'),
(N''),
(
N' PRINT N''SKIPPED: '
+ @EscapedDisplayName
+ N' - '' + ERROR_MESSAGE();'
),
(N'END CATCH;'),
(N'GO'),
(N'');
FETCH NEXT FROM ViewScriptCursor
INTO
@SchemaName,
@ViewName,
@UsesAnsiNulls,
@UsesQuotedIdentifier,
@Definition;
END;
CLOSE ViewScriptCursor;
DEALLOCATE ViewScriptCursor;
-------------------------------------------------------------------------------
-- Фінальна статистика.
-------------------------------------------------------------------------------
INSERT INTO #OutputLines (ScriptLine)
VALUES
(N'-------------------------------------------------------------------------------'),
(N'-- Deployment result'),
(N'-------------------------------------------------------------------------------'),
(N''),
(N'IF EXISTS (SELECT 1 FROM #ViewDeploymentErrors)'),
(N'BEGIN'),
(N' PRINT N'''';'),
(N' PRINT N''Some VIEWs were skipped:'';'),
(N''),
(N' SELECT'),
(N' SchemaName,'),
(N' ViewName,'),
(N' ErrorNumber,'),
(N' ErrorMessage'),
(N' FROM #ViewDeploymentErrors'),
(N' ORDER BY'),
(N' SchemaName,'),
(N' ViewName;'),
(N'END'),
(N'ELSE'),
(N'BEGIN'),
(N' PRINT N''All VIEWs were created successfully.'';'),
(N'END;'),
(N'GO'),
(N''),
(N'-------------------------------------------------------------------------------'),
(N'-- End of Base2Base VIEW deployment script'),
(N'-------------------------------------------------------------------------------');
-------------------------------------------------------------------------------
-- Виводимо готовий deployment-скрипт у Messages.
--
-- PRINT для nvarchar має обмеження 4000 символів.
--
-- Завдяки тому, що definition VIEW був попередньо розбитий на шматки
-- по 1800 символів, навіть у найгіршому випадку, коли майже кожний символ
-- є одинарною лапкою і після escaping подвоюється, рядок залишається
-- коротшим за 4000 символів.
-------------------------------------------------------------------------------
DECLARE @OutputLine nvarchar(max);
DECLARE OutputCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
ScriptLine
FROM #OutputLines
ORDER BY
LineNumber;
OPEN OutputCursor;
FETCH NEXT FROM OutputCursor
INTO @OutputLine;
WHILE @@FETCH_STATUS = 0
BEGIN
---------------------------------------------------------------------------
-- Додаткова страховка від випадкового обрізання PRINT.
---------------------------------------------------------------------------
IF DATALENGTH(@OutputLine) / 2 > 4000
BEGIN
CLOSE OutputCursor;
DEALLOCATE OutputCursor;
THROW 50001, N'Generated deployment line exceeds the PRINT limit of 4000 Unicode characters.', 1;
END;
PRINT ISNULL(@OutputLine, N'');
FETCH NEXT FROM OutputCursor
INTO @OutputLine;
END;
CLOSE OutputCursor;
DEALLOCATE OutputCursor;
-------------------------------------------------------------------------------
-- Прибираємо temp-таблиці самого генератора.
-------------------------------------------------------------------------------
DROP TABLE #ViewScripts;
DROP TABLE #OutputLines;
Andriy Kravchenko
Admin, Writer, File Uploader
Останнє оновлення:
9/22/2026 11:34:02 PM
5