如何以列表方式查阅到某数据库所有建立的视图的名称、行数?
如何以列表方式查阅到某数据库所有建立的视图的名称、行数?(最好还能包括视图涉及的表名等信息)
敬向电脑专家请教,不胜感激!
[解决办法]
用游标逐个获取行数..
SET NOCOUNT ON
DECLARE @ViewName AS nVarChar(128),@Query AS nVarChar(500)
declare Cur_Views cursor for
SELECT name FROM [sys].[all_views] x
WHERE x.schema_id = 1
OPEN Cur_Views
FETCH NEXT FROM Cur_Views
INTO @ViewName
WHILE @@Fetch_Status = 0
BEGIN
SELECT @Query = 'SELECT COUNT(*) AS [Count] FROM ' + @ViewName
EXECUTE(@Query)
FETCH NEXT FROM Cur_Views INTO @ViewName
END
CLOSE Cur_Views
DEALLOCATE Cur_Views
--所有表行数
SELECT
A.NAME,
MaxRows = MAX(B.rows)
FROM sys.tables A
INNER JOIN sys.partitions B
ON A.object_id = B.object_id
GROUP BY A.name
--视图是虚拟表,只存储创建它的SQL语句
--想得到行数,只能COUNT(*),且用#1楼的方法从sys.views中循环读取
EXEC sp_depends [v_test] --查询依赖信息(表名、连接字段)
SET NOCOUNT ON
DECLARE @ViewName AS nVarChar(128),@Query AS nVarChar(500)
declare Cur_Views cursor for
SELECT name FROM sysobjects x
WHERE type='V'
OPEN Cur_Views
FETCH NEXT FROM Cur_Views INTO @ViewName
WHILE @@Fetch_Status = 0
BEGIN
SELECT @Query = 'SELECT '''+@ViewName+''',COUNT(*) AS [Count] FROM ' + @ViewName
EXECUTE(@Query)
FETCH NEXT FROM Cur_Views INTO @ViewName
END
CLOSE Cur_Views
DEALLOCATE Cur_Views
alter view pos_v_saleflow
as
select * from pos_t_saleflow
union all
select * from pos_t_saleflow_all