如何监控SQL Server中表结构的更改？

Question

我希望能够监控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

任何想法？

- thatthing

---

这可能会有所帮助：https://dba.stackexchange.com/questions/165283/ddl-changes-tracking - Emrah Diril

没有任何代码可以弥补一个没有得到管理的开发过程。也没有任何代码可以弥补开发人员在不与团队其他成员沟通或协商的情况下进行模式更改所带来的影响。你的模式应该像你的应用程序代码一样受到源代码控制。 - SMor

Answer 1

您可以在数据库上使用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
*/

- lptr

Answer 2

非常感谢您的代码！我编辑了触发器，使其监视整个数据库中的所有表，而不仅仅是特定的“= object_id('dbo.guineapigtbl')”表。

ALTER 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),
            
            @WHILE_Count int,
            @WHILE_Count_Max int;

    --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)');

    CREATE TABLE #TMP_list_of_tables_in_DB
        (ID INT IDENTITY(1,1)
        ,object_id_table int
        )

    INSERT INTO #TMP_list_of_tables_in_DB
        (object_id_table)
    SELECT 
        (object_id)
    FROM [DBNAME].sys.objects
        WHERE TYPE IN('P','T','FN','u')
        AND TYPE_DESC = 'USER_TABLE'

    SET @WHILE_Count = 1
    SET @WHILE_Count_Max = (SELECT MAX(ID) FROM #TMP_list_of_tables_in_DB)

    WHILE @WHILE_Count <= @WHILE_Count_Max

    BEGIN

    if object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)) = (SELECT object_id_table FROM #TMP_list_of_tables_in_DB WHERE ID = @WHILE_Count)
      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

    SET @WHILE_Count = @WHILE_Count + 1

    END

    DROP TABLE #TMP_list_of_tables_in_DB

end

- Julian