CODEWITHSUNDEEP
sqlt-sql
Valentyn
Valentyn

Reputation: 612

Looping over all the values of a local T-SQL variable

My goal is to write a reusable script to check that each of my views runs and contains some records.

I've written a core functionality, but my @subquery variable contains only the first view name.

But how to loop over all the values of a local T-SQL variable?

DB: 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

Upvotes: 0

Views: 41

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

I'm Not really sure I understand why you're selecting from VIEW_TABLE_USAGE instead of VIEWS, but here's a far simpler way to do what you want:

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)

Upvotes: 2

Related Questions