1

For a validation purpose, I want to get the list of all the views present in a database and its corresponding record count. Can someone please help me to query this in SQL Server Management Studio?

Anjali
  • 11
  • 1
  • 3
  • https://stackoverflow.com/questions/2903262/sql-query-to-list-all-views-in-an-sql-server-2005-database – MusicLovingIndianGirl Nov 12 '18 at 13:42
  • Possible duplicate of [Sql Query to list all views in an SQL Server 2005 database](https://stackoverflow.com/questions/2903262/sql-query-to-list-all-views-in-an-sql-server-2005-database) – Cid Nov 12 '18 at 13:42

1 Answers1

2

Unfortunately, it isn't possible to retrieve rows count returned from a view by reading system tables, because this information is not maintained in the metadata. Only possible way is to actually execute select COUNT(*) from view_name, because views can contain complicated logic and the data returned by them actually is allocated to other objects (e.g. tables). For tables for example, you can get this rows count from sys.partitions DMV.

One workaround is to select the list of views and generate a dynamic query, which will execute select count(*) from each view and combine the results, but this could be quite heavy query.

Anyway, here is an example how to return a list of tables ('T') and views ('V') in your database. For tables you will also get a rows count, while for views this column will be null. Then change the where clause to return only views (where o.type = 'V') and execute the query. Copy/paste the last column in new query window (removing the last union all) and execute it to return the rows count for each view (NOTE: It could take a long time and have significant impact on your server!).

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 ('U', 'V')
group by o.name, o.type
order by o.name
Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32