查看SQL Server数据库修改了哪些内容

---1 查询数据库最近执行的语句
SELECT TOP 1000
ST.text AS '执行的SQL语句',
QS.execution_count AS '执行次数',
QS.total_elapsed_time AS '耗时',
QS.total_logical_reads AS '逻辑读取次数',
QS.total_logical_writes AS '逻辑写入次数',
QS.total_physical_reads AS '物理读取次数',
QS.creation_time AS '执行时间' ,
QS.*
FROM sys.dm_exec_query_stats QS
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE QS.creation_time >'2024-04-18' and QS.creation_time <='2024-10-01'
ORDER BY
QS.creation_time DESC

---
--2 查询各数据表最后的改动时间
select name as [数据表名称], create_date as [创建时间], modify_date as [最后一次修改时间]
from sys.all_objects where type='U' and is_ms_shipped=0 order by modify_date desc

-----3 -----------------
SELECT A.[type],A.type_desc FROM sys.objects A GROUP BY A.[type],A.type_desc

GO

DECLARE @bTime AS DATETIME

SET @bTime='2024-05-23'

--存储过程

SELECT * FROM sys.objects A WHERE A.[type]='P' AND A.modify_date>=@bTime ORDER BY A.modify_date DESC

--表

SELECT * FROM sys.objects A WHERE (A.[type]='S' OR A.[type]='IT' OR A.[type]='U') AND A.modify_date>=@bTime ORDER BY A.modify_date DESC

--函数

SELECT * FROM sys.objects A WHERE (A.[type]='FN' OR A.[type]='TF') AND A.modify_date>=@bTime ORDER BY A.modify_date DESC

--视图

SELECT * FROM sys.objects A WHERE A.[type]='V' AND A.modify_date>=@bTime ORDER BY A.modify_date DESC

GO
--------------
---4 查询一段时间的日志
SELECT * FROM fn_dblog(NULL, NULL)
WHERE [Begin Time] >= '2024-05-01 00:00:00' AND [End Time] <= '2024-10-31 23:59:59';

 

---5 查询修改了什么
SELECT
o.name,
c.name AS ColumnName,
t.name AS TableName,
o.create_date AS AddDate
FROM
sys.columns c
JOIN
sys.objects o ON c.object_id = o.object_id
JOIN
sys.types t ON c.system_type_id = t.system_type_id

order by AddDate desc