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

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?

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 = ' + 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.

StackExchange Database Administrators Q#237999, answer score: 5