Início / dba / Perguntas / 330745
Accepted
Farid Zahidov
Asked: 2023-08-30 20:21:01 +0800 CST

Descobrir quando colunas são adicionadas – SQL Server

  • 772

No SQL Server existe alguma maneira de obter informações sobre quando as colunas são criadas e quais colunas são novas? Além disso, preciso encontrar qualquer alteração nas colunas, por exemplo, quais tipos de dados das colunas foram alterados. Consigo obter a data de criação e última modificação das tabelas, mas para colunas não encontrei nenhuma consulta para fazer isso. Tentei usar trigger, audit e outras consultas que encontrei em fóruns, mas não obtive o resultado que desejo. Se alguém souber a solução, ajude por favor. Ferramentas de terceiros também são aceitáveis. Desde já, obrigado. Este é o meu código de gatilho:

DROP TABLE IF EXISTS ColumnChanges
CREATE TABLE ColumnChanges (
Id INT IDENTITY(1,1) PRIMARY KEY,
DatabaseName NVARCHAR(100),
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
ActionType NVARCHAR(50),
OldDataType NVARCHAR(50),
NewDataType NVARCHAR(50),
Add_Modify_Date DATETIME
)


CREATE TRIGGER trg_ColumnChange_AfterAlter
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
DECLARE @EventData XML
SET @EventData = EVENTDATA()

DECLARE @DatabaseName NVARCHAR(100)
DECLARE @TableName NVARCHAR(100)
DECLARE @ActionType NVARCHAR(50)
DECLARE @NewDataType NVARCHAR(100)
DECLARE @Add_Modify_Date DATETIME

SET @DatabaseName = @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)')
SET @TableName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)')
SET @ActionType = 'ADDED/MODIFIED'
SET @NewDataType = @EventData.value('(/EVENT_INSTANCE/AlterTableActionList/Alter/Columns/Column/DataTypeWithCollation)[1]', 'NVARCHAR(100)')
SET @Add_Modify_Date = GETDATE()

DECLARE @ColumnName NVARCHAR(100)
    SELECT @ColumnName = COLUMN_NAME    
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName

DECLARE @OldDataType NVARCHAR(100)
        SELECT @OldDataType = DATA_TYPE 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE COLUMN_NAME = @ColumnName


INSERT INTO ColumnChanges (DatabaseName, TableName, ColumnName, ActionType,OldDataType,NewDataType, Add_Modify_Date)
VALUES (@DatabaseName, @TableName, @ColumnName, @ActionType, @OldDataType, @NewDataType, @Add_Modify_Date)
  END
sql-server

1 respostas

  1. Best Answer

    Este é meu roteiro final. Criei 2 gatilhos, um para rastrear cada nova tabela e suas colunas, outro para rastrear novas colunas e colunas alteradas (alterando tipo de dados). Isso funciona bem para mim.

    DROP TABLE IF EXISTS [Table_name]
CREATE TABLE [Table_name] (
Id INT IDENTITY(1,1) PRIMARY KEY,
DatabaseName NVARCHAR(100),
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
ActionType NVARCHAR(50),
DataType NVARCHAR(50),
Add_Modify_Date DATETIME
)

CREATE OR ALTER TRIGGER trg_TableCreation 
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
DECLARE @EventData XML = EVENTDATA()
DECLARE @TableName sysname

SET @TableName = @EventData.value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname')

INSERT INTO [Table_name] (DatabaseName, TableName, ColumnName, ActionType, DataType, Add_Modify_Date)
SELECT @EventData.value('(EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
    , t.name
    , c.name
    , 'New table'
    , ty.name
    , @EventData.value('(EVENT_INSTANCE/PostTime)[1]', 'datetime2')
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types ty on c.user_type_id = ty.user_type_id
WHERE t.name = @TableName
END


CREATE OR ALTER TRIGGER trg_ColumnChanges
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
DECLARE @EventData XML = EVENTDATA()

INSERT INTO [Table_name] (DatabaseName, TableName, ColumnName, ActionType, DataType, Add_Modify_Date)
SELECT e.value('(DatabaseName/text())[1]', 'sysname')
    , e.value('(ObjectName/text())[1]', 'sysname')
    , c.value('.', 'SYSNAME')
    , CASE 
        WHEN @EventData.exist('/EVENT_INSTANCE/AlterTableActionList/Create') = 1 
        THEN 'Create'
        ELSE 'Alter'
        END
    , sc.DATA_TYPE
    , e.value('(PostTime/text())[1]', 'datetime2')
FROM @EventData.nodes('/EVENT_INSTANCE') e(e)
CROSS APPLY e.nodes('AlterTableActionList/*/Columns/Name') c(c)
LEFT JOIN INFORMATION_SCHEMA.COLUMNS sc
       ON sc.TABLE_NAME = e.value('(ObjectName/text())[1]', 'sysname')
       AND sc.COLUMN_NAME = c.value('.', 'sysname')
END
    • 0

relate perguntas