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
63
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
14
You can use those 2 queries:
您可以使用这两个查询:
select count(*) as TablesCount from sys.tables
select count(*) as ProceduresCount from sys.procedures
#3
5
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
3
Try below
试试以下
SELECT COUNT(*) AS [Total Tables] FROM sys.tables
SELECT COUNT(*) AS [Total Procedures] FROM sys.procedures
#5
2
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
1
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
0
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
63
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
14
You can use those 2 queries:
您可以使用这两个查询:
select count(*) as TablesCount from sys.tables
select count(*) as ProceduresCount from sys.procedures
#3
5
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
3
Try below
试试以下
SELECT COUNT(*) AS [Total Tables] FROM sys.tables
SELECT COUNT(*) AS [Total Procedures] FROM sys.procedures
#5
2
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
1
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
0
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';