Is there a way to store full DDL of View or Procedure definition from another database in separate table

Question

I'm trying to store DDLs of some views and stored procedures in a separate table in the Dump database. There are too many similar databases on the server. But some objects are redundant. They will be listed in the table and then dropped from the database. But their DDLs will be backed up if somebody will need them later.

The procedure works fine when the views are small, but if the size of the code exceeds some value - I'm get an error:

XML parsing: line 120, character 31, incorrect CDATA section syntax

Maybe that's I'm using the dbo.sp_sqlexec procedure, but I'm not sure. Will appreciate any ideas.

Definition of the table where those views will be firstly listed and then stored:

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)

This is the code of the procedure:

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

Answer 1

Not sure why you're storing module definitions as XML but you should be able to do this in one step, without the cursor, unsupported system procedures from decades ago, and INFORMATION_SCHEMA which is generally garbage (<-- see the section on 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;

The main problem was mentioned in a comment already: VIEW_DEFINITION is limited to 4,000 characters.

The purpose of @SQLstringToDrop is unclear. If you're on a modern enough version of SQL Server, you could instead inject CREATE OR ALTER into the definition, or generate that only at time of actual execution / deployment ... that doesn't change per view so there's no reason to store the entire IF EXISTS / DROP sequence for each and every view.

If you want to drop the views after you've backed up their definitions (though, really, why you aren't using proper version control system for this is a mystery), you can simply use the same technique (all in one shot instead of in a loop):

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;

As an additional tip, don't ever put square brackets around names manually (e.g. [' + @SchemaName + ']) - this does not protect you against SQL injection. And while it's unlikely someone put nefarious object names into the system you're working against now, it sets a bad example.