sql-server - 如何监视SQL Server中的表结构更改？

我想监视SQL Server中的某些表结构更改。

例如，假设我有下表。

现有

ID,int,not null
Name,varchar, null

已变更

ID,int,not null
Name,varchar, null
Lastname, varchar, null

是否可以通过SQL查询知道是否在该表中添加了新列？我想找到新添加的特定列。

我试过了但这并没有给我新添加的列名。

SELECT
    OBJECT_NAME(sc.[object_id]) as [table]
    ,sc.[name] as [column]
    ,so.modify_date
    ,so.create_date
  FROM [sys].[columns] sc
  JOIN [sys].[objects] so
  ON sc.[object_id] = so.[object_id]
  ORDER BY so.modify_date DESC

任何想法？

最佳答案

您可以在数据库上使用ddl触发器

create table dbo.logtablechanges
(
    tableobject_id int,
    tablename sysname,
    columnname sysname,--log a single column alteration
    theaction varchar(50),
    actionbylogin sysname constraint df_logtablechanges_actionbylogin default(original_login()),
    thestatement nvarchar(max),
    thedate datetime constraint df_logtablechanges_thedate default(getdate())
);
go

create table dbo.guineapigtbl(id int identity);
insert into dbo.guineapigtbl default values;
select * from guineapigtbl;
go

create trigger trig_db_alter_table on database
for ALTER_TABLE
as
begin

    declare @d xml = EVENTDATA();

    declare @tblschemaname sysname,
            @tblname sysname,
            @action varchar(20),
            @colname sysname,
            @sqlcommand nvarchar(max);

    --for multiple columns
    select
        @tblschemaname = @d.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
        @tblname = @d.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
        @action = @d.value('local-name((/EVENT_INSTANCE/AlterTableActionList/*)[1])', 'varchar(20)'),
        --change this for multiple columns (depends on the logging)
        @colname = @d.value('(/EVENT_INSTANCE/AlterTableActionList/*[1]/Columns/Name)[1]', 'sysname'),
        @sqlcommand = @d.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');


    if object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)) = object_id('dbo.guineapigtbl')
      and @colname is not null
    begin
        insert into dbo.logtablechanges
        (
            tableobject_id,
            tablename, columnname,  theaction, thestatement
        )
        values
        (
            object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)),
            @tblname, @colname, @action, @sqlcommand
        )

    end

end
go


--testing
alter table dbo.guineapigtbl add col1 int;
alter table dbo.guineapigtbl add col2 varchar(10);
alter table dbo.guineapigtbl alter column col1 bigint;
alter table dbo.guineapigtbl drop column col1;

--multiple
alter table dbo.guineapigtbl add col3 int, col4 int;

--check log
select *
from logtablechanges

--cleanup
/*
drop table dbo.logtablechanges
drop table dbo.guineapigtbl
drop trigger trig_db_alter_table on database

go
*/