如何计算 SQL Server 2008 中存储过程和表的总数 [英] How to count total number of stored procedure and tables in SQL Server 2008

问题描述

我在 SQL Server 2008 R2 中有数据库 Test1.在实时服务器上，我从那里备份并在我们的本地机器上将其恢复为 Test2 并添加了一些表和过程.

I have database Test1 in SQL Server 2008 R2. On the live server I took backup from there and restore it at our local machine as Test2 and added some tables and procedures.

如果我们将 Test2 恢复到实时服务器上，那么任何可以获取表名称和过程名称的查询是否仅在测试 2 中而不是在测试 1 中或 SQL Server 将其视为完全不同数据库?

If we restore Test2 back onto the live server so is it any query which can get tables name and procedure name which is only in test 2 not in test 1 or SQL Server treated it as totally different database?

如果我只想知道Test1 和Test2 数据库的差异数量，那么查询是什么

And what is the query if I want to know only the number of difference of Test1 and Test2 databases

推荐答案

这将为您提供表和存储过程的数量.

This will give you the count of tables and stored procedures.

SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
        WHEN 'S'
            THEN 'System Tables'
        WHEN 'IT'
            THEN 'Internal Tables'
        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
    END, 
    COUNT(*)     
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')
GROUP BY TYPE

您可以在sys.objects 中找到数据库中所有类型的对象.您必须在每个数据库上运行此查询才能查看对象数.

You can find in sys.objects all types of objects in the database. You will have to run this query on each of your databases to see the count of objects.

您可以找到有关 sys.objects 中存储内容的所有信息 此处.

You can find all information about what is stored in sys.objects here.

这篇关于如何计算 SQL Server 2008 中存储过程和表的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！