主页 / dba / 问题 / 237999
Accepted
Mel
Asked: 2019-05-14 01:19:00 +0800 CST

SQL 脚本来计算数据库中所有视图的行数，只有 SELECT 访问

  • 772

我需要知道第三方数据库中每个视图的行数，我被授予仅 SELECT 访问权限。以下脚本适用于表，但不适用于视图：

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

我在网上查看过，但所有建议要么仅适用于表格，要么要求我创建表格或过程（我无法使用此级别的权限），要么要求我分别计算每个视图的行数，这是不可行的对于包含大量此类视图的数据库。

有没有人对一个脚本有任何想法，它可以计算数据库中所有视图中的所有行，而不需要 CREATE 或 EXECUTE 权限？

sql-server permissions

2 个回答

  1. Best Answer

    一些改进。

    • QUOTENAME()挫败坏名声
    • 添加架构名称，因为架构很重要
    • 使用sys.views代替sys.objects（并type从输出中删除）
    • 限制sys.partitions为index_id0 或 1，以防止在带有其他非聚集索引的索引视图的情况下重复计数（不确定您认为AVG会完成什么 - 如果有一个带有非聚集过滤索引的索引视图怎么办？

    • 使用动态 SQL 避免手动复制/手动粘贴/手动按摩/手动运行

      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;

    请注意，FromMetadata除非视图被索引，否则它将为 0。

    • 4

  2. 虽然它不是单个脚本，但 Andrey Nikolov 在这里描述的方法似乎有效：

    https://stackoverflow.com/questions/53263435/how-do-i-get-the-list-of-all-views-present-in-a-database-and-its-record-count

    使用仅返回视图而不返回表的脚本版本：

    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

    然后复制并运行最后一列的结果，并删除最终的 UNION ALL。

    （尽管在我的例子中，我必须通过添加方括号和模式名称来更改通过复制第一个查询结果的最后一列生成的脚本，因为数据库设计人员已将表命名为“用户”和“案例”。 )

    希望这对其他有同样问题的人有用！

    • 2

相关问题