比较昨天和今天在同一数据库中存在的表的名称 [英] Compare names of tables which was present yesterday and present today in same database

问题描述

如果有人可以帮助我。

我想要一个存储过程，它将今天数据库中存在的表列表与昨天在同一个数据库中存在的表列表进行比较，如果有任何更改，如表删除，则邮件发送该表名。

只是我们比较的脚本对我有用。

提前致谢

解决方

您需要先拥有一个ObjectList表，每天存储表列表并将其用于第二天的比较

所以创建一个类似于  <的表/ p>

 CREATE TABLE ObjectList 
（
 TABLE_NAME varchar（100）
）
 

然后有这样的程序

 CREATE PROC CompareTables 
 AS 
 
 SELECT TABLE_NAME AS DeletedTables 
 FROM ObjectList o 
 WHERE NOT EXISTS 
（
 SELECT 1 
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_NAME = o.TABLE_NAME 
 AND TABLE_TYPE ='BASE TABLE'
）
 
 TRUNCATE TABLE ObjectList 
 
 INSERT ObjectList 
 SELECT TABLE_NAME 
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_TYPE ='BASE TABLE' 
 GO 

Hi,

If someone can help me in this.

I want a stored procedure which compares list of tables present in a database today with list of tables present in same db yesterday and if any changes like table deletion then mail that table name.

Just a script where we compare will work for me.

Thanks in advance

解决方案

you need to have a ObjectList table first to store table list each day and use it for comparison to next day

so create a table like 

CREATE TABLE ObjectList
(
TABLE_NAME varchar(100)
)

then have a procedure like this

CREATE PROC CompareTables
AS

SELECT TABLE_NAME AS DeletedTables
FROM ObjectList o
WHERE NOT EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = o.TABLE_NAME
AND TABLE_TYPE = 'BASE TABLE'
)

TRUNCATE TABLE ObjectList

INSERT ObjectList
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

这篇关于比较昨天和今天在同一数据库中存在的表的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！