SQL Server 2005 表更改历史记录

发布于 2024-08-28 00:13:27 字数 118 浏览 4 评论 0原文

SQL Server 是否维护任何历史记录来跟踪表更改，例如列添加、删除、重命名、类型/长度更改等？我发现很多建议使用存储过程来手动执行此操作。但我很好奇 SQL Server 是否在任何系统表中保留这样的历史记录？谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

东北女汉子 2024-09-04 00:13:27

在 SQL Server 2005 及更高版本中，您可以创建数据库级触发器来跟踪表更改。使用类似的内容：

CREATE TRIGGER [YourDatabaseTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @EventData      xml
DECLARE @Message        varchar(1000)
SET @EventData=EVENTDATA()

INSERT INTO YourLogTable 
    (EventDateTime,EventDescription) 
    VALUES (GETDATE(),SUSER_NAME()
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
           )
RETURN
GO

ENABLE TRIGGER [YourDatabaseTrigger] ON DATABASE

这是日志的一些简单输出：

select * from YourLogTable
EventID     EventDateTime           EventDescription
----------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1           2010-04-06 08:25:47.333 sa; TABLE; YourLogTable2; create table YourLogTable2 (EventID int primary key identity(1,1),EventDateTime datetime, EventDescription varchar(max))
2           2010-04-06 08:25:55.113 sa; TABLE; YourLogTable2; drop table YourLogTable2

(2 row(s) affected)

您可以扩展日志以包含更多列，或者像这个简单示例一样将所有内容转储到一个日志中。

in SQL Server 2005 and up you can create a database level trigger to track table changes. Use something like:

CREATE TRIGGER [YourDatabaseTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @EventData      xml
DECLARE @Message        varchar(1000)
SET @EventData=EVENTDATA()

INSERT INTO YourLogTable 
    (EventDateTime,EventDescription) 
    VALUES (GETDATE(),SUSER_NAME()
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)')
                     +'; '[email protected]('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
           )
RETURN
GO

ENABLE TRIGGER [YourDatabaseTrigger] ON DATABASE

here is some simple output from the log:

select * from YourLogTable
EventID     EventDateTime           EventDescription
----------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1           2010-04-06 08:25:47.333 sa; TABLE; YourLogTable2; create table YourLogTable2 (EventID int primary key identity(1,1),EventDateTime datetime, EventDescription varchar(max))
2           2010-04-06 08:25:55.113 sa; TABLE; YourLogTable2; drop table YourLogTable2

(2 row(s) affected)

you could expand the log to contain more columns, or just dump everything within a one like in this simple example.

回复收藏 0 原文

不寐倦长更 2024-09-04 00:13:27

事务日志存储所有这些信息，DBCC LOG 命令应该让您查看这些信息，但它是一个未记录的命令。

DBCC LOG(<database name>[,{0|1|2|3|4}])  
0 – Basic Log Information (default)  
1 – Lengthy Info  
2 – Very Length Info  
3 – Detailed  
4 – Full Example

句法：

DBCC log (MY_DB, 4)

The transaction logs store all this information and the DBCC LOG command should let you view that, but it's an undocumented command.

DBCC LOG(<database name>[,{0|1|2|3|4}])  
0 – Basic Log Information (default)  
1 – Lengthy Info  
2 – Very Length Info  
3 – Detailed  
4 – Full Example

Syntax: