如何在管理工作室中擴展字元串的字元限制

下面的 SQL 可以擴展，但是當我在具有很多列的 SSMS 表中查看輸出時，似乎被截斷了，因為游標生成的字元串似乎只達到了最大限制。

   USE [HealthBI]
GO

/*** ENTER VIEW SCHEMA ***/
DECLARE @schema_name AS varchar(MAX) = 'trust'

/*** ENTER CDO TABLE TO REPLICATE ***/
DECLARE @table_name_value  AS varchar(MAX) = 'CDO[_]%'
DECLARE @table_name  AS varchar(MAX)
DECLARE @column_name AS varchar(MAX)
DECLARE @viewsql AS varchar(MAX)

DECLARE csr_view CURSOR FOR
SELECT TABLE_NAME ,
STUFF(( SELECT  ',' +QUOTENAME(TABLE_NAME)+'.'+QUOTENAME(COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = a.TABLE_NAME
ORDER BY COLUMN_NAME 
FOR XML PATH(''),Type)
   .value('text()[1]','nvarchar(max)' ), 1, 1, '') AS value
FROM    INFORMATION_SCHEMA.COLUMNS a
where  TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME like @table_name_value
   and TABLE_NAME NOT LIKE 'CDO_CDS%'
GROUP BY a.TABLE_NAME

   OPEN csr_view
       FETCH NEXT FROM csr_View INTO @TABLE_NAME, @column_name
   SET @viewsql = ''
--  PRINT @TABLE_NAME PRINT @column_name

       WHILE @@fetch_status = 0
       BEGIN

       IF OBJECT_ID('['+ @schema_name + '].[' + @table_name + ']') IS NULL
       SET @viewsql = @viewsql + '
       CREATE '
       ELSE
       SET @viewsql = @viewsql + '
       ALTER '

       SET @viewsql = @viewsql + 'VIEW [' + @schema_name + '].[' + @table_name + ']
       AS SELECT ' + @column_name + '
       FROM [dbo].[' + @table_name + ']'


       IF @schema_name = 'trust' AND @table_name <> 'CDO_MPI'
       SET @viewsql = @viewsql + '
       INNER JOIN [CDO_MPI]
       ON [CDO_MPI].[UNIQUE_ID] = [' + @table_name + '].[CDO_MPI_UNIQUE_ID]
       AND [CDO_MPI].[IS_DUMMY_PATIENT] = ''N''   GO'

       IF @schema_name = 'trust' AND @table_name = 'CDO_MPI'
       SET @viewsql = @viewsql + '
       WHERE [CDO_MPI].[IS_DUMMY_PATIENT] = ''N'''
       PRINT (@viewsql)
--       EXEC (@viewsql)

       FETCH NEXT FROM csr_View INTO @TABLE_NAME, @column_name
       SET @viewsql = ''

       END
   CLOSE csr_view
DEALLOCATE csr_View

更改 PRINT (@viewsql)為 SELECT (@viewsql)

您遇到了PRINT命令的限制，我相信該命令限制為 8000 個字元。

由於您的腳本輸出看起來包含\r\n字元，因此您的情況的解決方法是替換：

PRINT (@viewsql)

具有以下內容：

DECLARE @printCur INT
SET @printCur = 8000

WHILE LEN(@viewsql) > 8000
BEGIN
    SET     @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@viewsql, 0, 8000)))

    PRINT LEFT(@viewsql, @printCur)
    SELECT @viewsql = RIGHT(@viewsql, LEN(@viewsql) - @printCur)
END

這將遍歷您的@viewsql變數並使用換行符（即\r\n）作為字元串中的潛在分隔符點列印出命令。我在我編寫的常式中使用它來列印數據庫角色定義，但它還沒有讓我失望。

希望有幫助！

引用自：https://dba.stackexchange.com/questions/239424