Sql-server – SQL script to count rows for all views in a database with SELECT-only access

countpermissionssql serverview

I need to know the row count for every view in a third-party database that I have been given SELECT-only access to. The following script works for tables but not views:

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        --A.Name, SUM(B.rows) AS 'RowCount'  Use AVG instead of SUM
          A.Name, AVG(B.rows) AS 'RowCount'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
Order by AVG(B.rows) desc
GO

I have looked online but all the suggestions either work only for tables, require me to create tables or procedures (which I can't do with this level of permissions), or require me to count the rows for each view separately, which is infeasible for a database containing a large number of views such as this.

Does anyone have any ideas for a script that would count all of the rows in all of the views in a database without requiring CREATE or EXECUTE permissions?

Best Answer

A few improvements.

  • QUOTENAME() to thwart bad namers
  • add schema name, since schema is important
  • use sys.views instead of sys.objects (and remove type from output)
  • limit sys.partitions to index_id 0 or 1, to prevent double-counting in the case of an indexed view with additional non-clustered indexes (not sure what you think AVG will accomplish - what if there is an indexed view with a non-clustered filtered index?

  • use dynamic SQL to avoid manual copy/ manual paste/ manual massage/ manual run

    DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL 
SELECT ' 
  + 'N''' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N''',
  FromMetadata = N' + CONVERT(varchar(11),COALESCE(SUM(p.rows),0)) + N',
  TheHarderWay = COUNT(*) FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) +N'
'
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.schema_id = s.schema_id
LEFT OUTER JOIN sys.partitions AS p
ON v.object_id = p.object_id
AND p.index_id IN (0,1)
GROUP BY s.name, v.name
ORDER BY s.name, v.name;

SET @sql = STUFF(@sql, 1, CHARINDEX(N'SELECT', @sql)-1, N'');

PRINT @sql;
--EXEC sys.sp_executesql @sql;

Note that FromMetadata will be 0 unless the view is indexed.

Related Question