create table v_table(Name sysname, row int) go declare test cursor for select 'insert v_table select '+quotename(Name,'''')+',row=count(1) from '+quotename(Name) from sysobjects where xtype='V' declare @Sql nvarchar(100) open test fetch next from test into @Sql while @@fetch_status=0 begin exec(@Sql) fetch next from test into @Sql end close test deallocate testgo select * from v_table
use Northwind go declare @View_Name nvarchar(128) declare @RowCount int declare @sql nvarchar(4000) declare @View_Details table(ViewName nvarchar(128),ViewRowCount int)declare cur cursor for select name from sysobjects where xtype=N'V' open cur fetch next from cur into @View_Name while @@fetch_status=0 begin set @sql=N'select @RowCount=count(*) from ['+@View_Name+N']' exec sp_executesql @sql,N'@RowCount int output',@RowCount output insert @View_Details values(@View_Name,@RowCount) fetch next from cur into @View_Name end close cur deallocate curselect * from @View_Details/* viewa.view 830 Customer and Suppliers by City 120 Alphabetical list of products 69 Current Product List 69 Orders Qry 830 Products Above Average Price 25 Products by Category 69 Quarterly Orders 86 Invoices 2155 Order Details Extended 2155 Order Subtotals 830 Product Sales for 1997 77 Category Sales for 1997 8 Sales by Category 77 Sales Totals by Amount 66 Summary of Sales by Quarter 809 Summary of Sales by Year 809 syssegments 3 sysconstraints 45*/
go
declare test cursor for
select
'insert v_table select '+quotename(Name,'''')+',row=count(1) from '+quotename(Name)
from
sysobjects
where
xtype='V'
declare @Sql nvarchar(100)
open test
fetch next from test into @Sql
while @@fetch_status=0
begin
exec(@Sql)
fetch next from test into @Sql
end
close test
deallocate testgo
select * from v_table
go
declare @View_Name nvarchar(128)
declare @RowCount int
declare @sql nvarchar(4000)
declare @View_Details table(ViewName nvarchar(128),ViewRowCount int)declare cur cursor for
select name from sysobjects where xtype=N'V'
open cur
fetch next from cur into @View_Name
while @@fetch_status=0
begin
set @sql=N'select @RowCount=count(*) from ['+@View_Name+N']'
exec sp_executesql @sql,N'@RowCount int output',@RowCount output
insert @View_Details values(@View_Name,@RowCount)
fetch next from cur into @View_Name
end
close cur
deallocate curselect * from @View_Details/*
viewa.view 830
Customer and Suppliers by City 120
Alphabetical list of products 69
Current Product List 69
Orders Qry 830
Products Above Average Price 25
Products by Category 69
Quarterly Orders 86
Invoices 2155
Order Details Extended 2155
Order Subtotals 830
Product Sales for 1997 77
Category Sales for 1997 8
Sales by Category 77
Sales Totals by Amount 66
Summary of Sales by Quarter 809
Summary of Sales by Year 809
syssegments 3
sysconstraints 45*/