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

Question

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?

Answer 1

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.

Answer 2

Whilst it's not a single script, the method described by Andrey Nikolov here seems to work:

https://stackoverflow.com/questions/53263435/how-do-i-get-the-list-of-all-views-present-in-a-database-and-its-record-count

Using a version of the script that returns views only and not tables:

select o.name, sum(p.rows) as RowsCount, o.type, concat('select ''', o.name, ''' as ObjectName, count(*) as RowsCount from ', o.name, ' union all ')
from sys.objects o
left join sys.partitions p on p.object_id = o.object_id
where o.type in ('V')
group by o.name, o.type
order by o.name

And then copying and running the results of the last column with the final UNION ALL removed.

(Though in my case I had to alter the script generated by copying the final column of the results of the first query by adding square brackets and schema names, because the database designer had named the tables things like "User" and "Case".)

Hope this is useful to anyone else having the same issue!