我正在嘗試將某些視圖和存盤程序的 DDL 存盤在Dump資料庫中的單獨表中。服務器上有太多類似的資料庫。但是有些物件是多余的。它們將列在表中，然后從資料庫中洗掉。但是如果以后有人需要它們，它們的 DDL 將被備份。
當視圖很小時，該程序可以正常作業，但是如果代碼的大小超過某個值 - 我會收到一個錯誤：
XML 決議：第 120 行，字符 31，不正確的 CDATA 部分語法
也許那是我正在使用該dbo.sp_sqlexec程式，但我不確定。將欣賞任何想法。
將首先列出然后存盤這些視圖的表的定義：
CREATE TABLE [dbo].[ViewList_Explicit]
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[ServerName] [sysname] NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[SchemaName] [sysname] NOT NULL,
[ViewName] [sysname] NOT NULL,
[DefinitionText] [xml] NULL,
[IsTransferred] [bit] NOT NULL,
[DateTransferred] [datetime] NULL
);
INSERT INTO [dbo].[ViewList_Explicit] ([ServerName], [DatabaseName], [SchemaName], [ViewName], [DefinitionText], [IsTransferred], [DateTransferred])
VALUES ('dbserver', 'reco', 'dbo', 'v_redundant_toDrop', NULL, 0, NULL)
這是程式的代碼：
CREATE OR ALTER PROCEDURE [dbo].[sp_moveViews2Dump]
(@DatabaseName SYSNAME)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Serv SYSNAME = @@SERVERNAME;
DECLARE @SQLstringToDrop NVARCHAR(MAX), @SQLstringForDefinition NVARCHAR(MAX);
DECLARE @SchemaName SYSNAME, @ViewName SYSNAME, @ExplicitID INTEGER;
DECLARE @DDLview XML;
DECLARE @Buffer TABLE(line XML);
DECLARE Schedule_cursor CURSOR LOCAL FOR
SELECT ID, SchemaName, ViewName
FROM [Dump].dbo.ViewList_Explicit
WHERE DatabaseName = @DatabaseName
AND ServerName = @Serv
AND IsTransferred = 0
OPEN Schedule_cursor
FETCH NEXT FROM Schedule_cursor INTO @ExplicitID, @SchemaName, @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLstringForDefinition = 'SELECT CONCAT(''<query><![CDATA['', VIEW_DEFINITION, '']]></query>'') FROM ['
@DatabaseName '].INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ''' @ViewName ''' AND TABLE_SCHEMA = ''' @SchemaName ''';'
--PRINT @SQLstringForDefinition
INSERT @Buffer EXECUTE dbo.sp_sqlexec @SQLstringForDefinition
SELECT @DDLview = line FROM @Buffer
SELECT @SQLstringToDrop = 'USE [' @DatabaseName ']
DROP VIEW IF EXISTS [' @SchemaName '].[' @ViewName ']'
--EXECUTE dbo.sp_sqlexec @SQLstringToDrop -- Commented out to avoid the deletion
UPDATE [Dump].dbo.ViewList_Explicit
SET [DefinitionText] = @DDLview, IsTransferred = 1, DateTransferred = GETDATE()
WHERE ID = @ExplicitID
DELETE FROM @Buffer
FETCH NEXT FROM Schedule_cursor INTO @ExplicitID, @SchemaName, @ViewName
END
CLOSE Schedule_cursor
DEALLOCATE Schedule_cursor
SET NOCOUNT OFF
END
uj5u.com熱心網友回復：
不知道為什么要將模塊定義存盤為 XML，但您應該能夠一步完成，沒有游標，幾十年前不受支持的系統程序，INFORMATION_SCHEMA這通常是垃圾（< - 請參閱有關部分Module Definitions）：
DECLARE @exec nvarchar(1024) = QUOTENAME(@DatabaseName)
N'.sys.sp_executesql';
DECLARE @sql nvarchar(max) = N';WITH vws AS
(SELECT SchemaName = s.name, ViewName = v.name,
DefinitionText = CONCAT(''<query><![CDATA['',
OBJECT_DEFINITION(v.[object_id]), N'']]></query>'')
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id]
)
UPDATE vle
SET vle.DefinitionText = sps.DefinitionText,
vle.IsTransferred = 1,
vle.DateTransferred = GETDATE()
FROM [Dump].dbo.ViewList_Explicit AS vle
INNER JOIN vws
ON vle.SchemaName = vws.SchemaName
AND vle.ViewName = vws.ViewName
WHERE vle.DatabaseName = @db
AND vle.ServerName = @@SERVERNAME
AND vle.IsTransferred = 0;';
EXEC @exec @sql, N'@db sysname', @DatabaseName;
主要問題已經在評論中提到：VIEW_DEFINITION限制為 4,000 個字符。
的目的@SQLstringToDrop不明確。如果您使用的是足夠現代的 SQL Server 版本，則可以改為注入CREATE OR ALTER定義，或者僅在實際執行/部署時生成它……這不會因視圖而改變，因此沒有理由存盤整個IF EXISTS / DROP每個視圖的順序。
如果您想在備份視圖定義后洗掉視圖（不過，實際上，為什么您不使用適當的版本控制系統是一個謎），您可以簡單地使用相同的技術（全部一次性完成）在一個回圈中）：
DECLARE @exec nvarchar(1024) = QUOTENAME(@DatabaseName)
N'.sys.sp_executesql';
DECLARE @sql nvarchar(max) = N'';
SELECT @sql = CONCAT(N'DROP VIEW IF EXISTS ',
QUOTENAME(SchemaName), N'.',
QUOTENAME(ViewName), N';')
FROM [Dump].dbo.ViewList_Explicit
WHERE DatabaseName = @DatabaseName
AND ServerName = @@SERVERNAME;
EXEC @exec @sql;
作為一個額外的提示，永遠不要手動將方括號放在名稱周圍（例如[' @SchemaName ']） - 這不能保護您免受 SQL 注入。雖然不太可能有人將惡意物件名稱放入您現在正在處理的系統中，但它樹立了一個壞榜樣。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/476638.html