如何从数据库中获取视图的行数? [英] How to get the row count for views from a database?
本文介绍了如何从数据库中获取视图的行数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建一个自定义视图,该视图可以获取数据库所有视图和表的行数.
I'm trying to make a customized view that gets me the row count for all the views and tables of a database.
在 SQL Server 中获取表的数量是该死的
Getting the count of table is damn in SQL Server
SELECT TABLE_SCHEMA,
TABLE_NAME = TABLES.TABLE_NAME,
RECORD_COUNT = MAX(SYSINDEXES.ROWS)
FROM SYS.SYSINDEXES "SYSINDEXES",
INFORMATION_SCHEMA.TABLES "TABLES"
WHERE TABLES.TABLE_NAME = OBJECT_NAME(SYSINDEXES.ID)
AND TABLES.TABLE_TYPE = 'BASE TABLE'
GROUP BY TABLES.TABLE_SCHEMA,
TABLES.TABLE_NAME
现在,我需要获取 VIEWS 的行数
Now, I need to get the rowcount for VIEWS
我觉得唯一的方法是从视图中计算行数即 count(*) from view_name
I feel the only way is to count the number of rows from the views
i.e. count(*) from view_name
但是,我找不到将行数与 view_name、table_schema 等一起用于视图的方法.
But, I could not find a way to have the rowcount for view along with view_name, table_schema and so on.
这方面的任何进展都会有所帮助.
Any advance on this would be helpful.
推荐答案
这里,是最终的解决方案:
Here , is the final solution:
SET NOCOUNT ON
DECLARE @ViewName AS nVarChar(128)
, @TmpQuery AS nVarChar(500)
, @Out3 as int
DECLARE Cur_Views CURSOR FOR
SELECT schema_name(schema_id)+'.'+name as "Table_Name" FROM [sys].[all_views]
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)
CREATE TABLE #Data (var int)
SELECT @TmpQuery = 'SELECT COUNT(*) AS [Count] FROM ' + @ViewName
INSERT #Data exec (@TmpQuery)
SELECT @Out3 = var from #Data
--PRINT @ViewName
--PRINT @Out3
insert into Person.ViewCountTracker values(@ViewName,@Out3)
DROP TABLE #Data
FETCH NEXT FROM Cur_Views INTO @ViewName
END
CLOSE Cur_Views
DEALLOCATE Cur_Views
GO
--create table Person.ViewCountTracker
--(
-- ViewName varchar(255),
-- RowValue int
--)
--select * from Person.ViewCountTracker
这篇关于如何从数据库中获取视图的行数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文