Cursors
如何在管理工作室中擴展字元串的字元限制
下面的 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)
您遇到了
由於您的腳本輸出看起來包含
\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
)作為字元串中的潛在分隔符點列印出命令。我在我編寫的常式中使用它來列印數據庫角色定義,但它還沒有讓我失望。希望有幫助!