2

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?

  • 1
    You will need to count from each view separately, unless the view is indexed (in which case the rowcount is materialized in sys.partitions similarly to other indexes). – Dan Guzman May 13 at 9:26
3

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.

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.