时间戳

首页 > 解决方案 > 循环遍历本地 T-SQL 变量的所有值

sql - 循环遍历本地 T-SQL 变量的所有值

问题描述

我的目标是编写一个可重用的脚本来检查我的每个视图是否运行并包含一些记录。

我编写了一个核心功能，但我的@subquery变量只包含第一个视图名称。

但是如何遍历本地 T-SQL 变量的所有值呢？

数据库：Microsoft SQL Server 2014

USE TSQL2012
GO

DECLARE @subquery nvarchar (255)
DECLARE @view_name nvarchar (255)
DECLARE @main_query nvarchar (255)

SET @subquery = 'select @view_name = concat(VIEW_CATALOG, char(46), VIEW_SCHEMA, 
                 char(46), VIEW_NAME) from (
                                           select distinct VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME 
                                           from INFORMATION_SCHEMA.VIEW_TABLE_USAGE) S'

EXEC sp_executesql @subquery,
                    N'@view_name nvarchar(255) OUTPUT', 
                    @view_name = @view_name OUTPUT

-- 1 - True (the table has records), 0 - False (the table doesn't have records)
SET @main_query = 'select ' + char(39) + @view_name + char(39) + 'as [View Name], 
                   count(*) as [The view has records] from   
                   (select top(1) 1 as cnt from ' + @view_name + ') s'

EXEC sp_executesql @main_query, N'@view_name nvarchar(255)', @view_name = @view_name

标签： sqltsql

解决方案

我不确定我是否理解您为什么选择 fromVIEW_TABLE_USAGE而不是VIEWS，但这是一种更简单的方法来做您想做的事情：

DECLARE @SQL nvarchar(max);

SELECT @SQL = STUFF((
SELECT  CONCAT(
        ' UNION ALL SELECT ''',
        TABLE_CATALOG, CHAR(46), TABLE_SCHEMA, CHAR(46), TABLE_NAME,
        ''' AS [View Name], COUNT(*) AS Cnt FROM ',
        TABLE_CATALOG, CHAR(46), TABLE_SCHEMA, CHAR(46), TABLE_NAME)
FROM INFORMATION_SCHEMA.VIEWS
FOR XML PATH('')
), 1, 11, 'WITH CTE AS (') + ') SELECT [View Name], Cnt FROM CTE WHERE Cnt > 0'

-- Whenever using dynamic SQL, print is your best friend.   
PRINT @SQL;

-- unremark once you've checked the printed SQL is O.k
--EXEC(@SQL)

推荐阅读