檢視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