如何计算SQL Server 2008中的存储过程和表的总数

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.

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

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?

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

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

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

8 个解决方案

#1

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

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.

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

#2

You can use those 2 queries:

您可以使用这两个查询：

select count(*) as TablesCount from sys.tables
select count(*) as ProceduresCount from sys.procedures

#3

I often use this script I found on this blog

我经常使用我在这个博客上找到的脚本

USE [MyDatabase]
GO
SELECT 'Count' = COUNT(*), 'Type' = CASE type 
                WHEN 'C' THEN 'CHECK constraints' 
                WHEN 'D' THEN 'Default or DEFAULT constraints' 
                WHEN 'F' THEN 'FOREIGN KEY constraints' 
                WHEN 'FN' THEN 'Scalar functions' 
                WHEN 'IF' THEN 'Inlined table-functions' 
                WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints' 
                WHEN 'L' THEN 'Logs' 
                WHEN 'P' THEN 'Stored procedures' 
                WHEN 'R' THEN 'Rules' 
                WHEN 'RF' THEN 'Replication filter stored procedures' 
                WHEN 'S' THEN 'System tables' 
                WHEN 'TF' THEN 'Table functions' 
                WHEN 'TR' THEN 'Triggers' 
                WHEN 'U' THEN 'User tables' 
                WHEN 'V' THEN 'Views' 
                WHEN 'X' THEN 'Extended stored procedures' 
    END 

    FROM sys.objects 
    GROUP BY type 
    ORDER BY type 
GO

You can modify it by type from information about sys.objects

您可以通过类型从sys.objects的信息修改它

Or by object from this reference Object Catalog Views, as you already got an answer for tables and procedures in the previous answers, e.g.

或者通过此参考对象目录视图中的对象，因为您已经获得了前面答案中的表和过程的答案，例如

SELECT count(*) AS MyTables FROM sys.tables
SELECT count(*) AS MyProcedures FROM  sys.procedures
SELECT count(*) AS MyTriggers FROM  sys.triggers
SELECT count(*) AS MyViews FROM  sys.views

Hope this gives you some additional help

希望这会给你一些额外的帮助

#4

Try below

试试以下

SELECT COUNT(*) AS [Total Tables] FROM sys.tables  
SELECT COUNT(*) AS [Total Procedures] FROM sys.procedures

#5

Use the following queries.

使用以下查询。

USE YOURDBNAME
SELECT COUNT(*) AS totalTable from information_schema.tables 
WHERE table_type = 'base table'  

select Count(*) AS TotalProc from sys.procedures

#6

I now use the below, based on Milica's answer with some extra types, default value and sorted by count.

我现在使用下面的，基于Milica的答案，一些额外的类型，默认值和按计数排序。

SELECT 'Count' = COUNT(*), 'Type' = CASE type 
    WHEN 'AF' THEN 'Aggregate function (CLR)' 
    WHEN 'C' THEN 'CHECK constraints' 
    WHEN 'D' THEN 'Default or DEFAULT constraints' 
    WHEN 'F' THEN 'FOREIGN KEY constraints' 
    WHEN 'FN' THEN 'Scalar functions' 
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function' 
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function' 
    WHEN 'IF' THEN 'Inlined table-functions' 
    WHEN 'IT' THEN 'Internal table' 
    WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints' 
    WHEN 'L' THEN 'Logs' 
    WHEN 'P' THEN 'Stored procedures' 
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure' 
    WHEN 'PG' THEN 'Plan guide' 
    WHEN 'PK' THEN 'PRIMARY KEY constraint' 
    WHEN 'R' THEN 'Rules' 
    WHEN 'RF' THEN 'Replication filter stored procedures' 
    WHEN 'S' THEN 'System tables' 
    WHEN 'SN' THEN 'Synonym' 
    WHEN 'SO' THEN 'Sequence object' 
    WHEN 'SQ' THEN 'Service queue' 
    WHEN 'TF' THEN 'Table functions' 
    WHEN 'TR' THEN 'Triggers' 
    WHEN 'U' THEN 'User tables' 
    WHEN 'UQ' THEN 'UNIQUE constraint' 
    WHEN 'V' THEN 'Views' 
    WHEN 'X' THEN 'Extended stored procedures' 
    ELSE type
END 
FROM sys.objects 
GROUP BY type 
ORDER BY 'Count' desc

#7

Use this script. It is not using switch case statement.

使用此脚本。它不使用switch case语句。

USE [MyDatabase]
GO
select distinct type_desc as 'Type Description', Count from
(SELECT 'Count' = COUNT(*), type FROM sys.objects GROUP BY type) as dbstatistics 
left join sys.objects on dbstatistics.type = sys.objects.type ORDER BY Count desc
GO

#8

-1

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbName';

#1