Can we get the table schema changes from SQL server using T SQL queries. Is there are any tracking inbuild tables in SQL Server?

Question

Hi,

I want to track the table schema changes using T SQL queries. Can anyone help me related to this problem.

Thanks and Regards,
Radha Pusarapu

Answer 1

Hi @Radha Pusarapu (Ernst & Young LLP) ,

Default Trace was introduced in SQL Server 2005. It is an internal, server-side SQL trace which as the name suggests is created and enabled by default on every SQL Server instance. You can very easily check if it is running on your instance using this simple query:

SELECT * FROM sys.traces WHERE is_default = 1;  

Status = 1 means that trace is running. You can also see that it is configured to have a maximum of 5 rollover files 20 megabytes each.

Default trace collects details of 34 events and stores them in the *.trc files in default SQL Server Instance Log directory. This query will list all 34 events captured by this trace.

SELECT  tc.category_id AS CategoryId,  
    tc.name AS CategoryName,  
    tei.eventid AS EventId,  
    te.name AS EventName  
FROM sys.traces t   
CROSS APPLY (SELECT DISTINCT eventid FROM fn_trace_geteventinfo(t.id)) tei  
INNER JOIN sys.trace_events te ON tei.eventid = te.trace_event_id  
INNER JOIN sys.trace_categories tc ON te.category_id = tc.category_id  
WHERE t.is_default = 1;  

• How to read data from Default Trace

1. We can use the Schema Changes History report. It is one of the SQL Server Management Studio built-in reports. You can get to this report on database or instance level. In my case, the database has already been dropped so I will use it from the instance level. Right-click on the instance, choose Reports, then Standard Reports, and finally select Schema Changes History.

2.We can also use T-sql , thanks to this we’re able to quickly get the latest object modifications.

DECLARE @file varchar(255);  
SELECT @file = path FROM sys.traces WHERE is_default = 1;  
  
WITH TraceCTE AS (  
    SELECT DatabaseID,   
        DatabaseName,   
        LoginName,   
        HostName,   
        ApplicationName,   
        SPID,  
        StartTime,  
        LEAD(StartTime) OVER (PARTITION BY XactSequence ORDER BY EventSequence) AS EndTime,  
        EventClass,  
        CASE EventClass  
            WHEN 46 THEN 'Object:Created'  
            WHEN 47 THEN 'Object:Deleted'  
            WHEN 164 THEN 'Object:Altered'  
            ELSE CAST(EventClass AS VARCHAR(max))  
        END AS EventClassDesc,  
        ObjectType,  
        CASE ObjectType  
            WHEN 8259 THEN 'Check Constraint'  
            WHEN 8260 THEN 'Default (constraint or standalone)'  
            WHEN 8262 THEN 'Foreign-key Constraint'  
            WHEN 8272 THEN 'Stored Procedure'  
            WHEN 8274 THEN 'Rule'  
            WHEN 8275 THEN 'System Table'  
            WHEN 8276 THEN 'Trigger on Server'  
            WHEN 8277 THEN '(User-defined) Table'  
            WHEN 8278 THEN 'View'  
            WHEN 8280 THEN 'Extended Stored Procedure'  
            WHEN 16724 THEN 'CLR Trigger'  
            WHEN 16964 THEN 'Database'  
            WHEN 16975 THEN 'Object'  
            WHEN 17222 THEN 'FullText Catalog'  
            WHEN 17232 THEN 'CLR Stored Procedure'  
            WHEN 17235 THEN 'Schema'  
            WHEN 17475 THEN 'Credential'  
            WHEN 17491 THEN 'DDL Event'  
            WHEN 17741 THEN 'Management Event'  
            WHEN 17747 THEN 'Security Event'  
            WHEN 17749 THEN 'User Event'  
            WHEN 17985 THEN 'CLR Aggregate Function'  
            WHEN 17993 THEN 'Inline Table-valued SQL Function'  
            WHEN 18000 THEN 'Partition Function'  
            WHEN 18002 THEN 'Replication Filter Procedure'  
            WHEN 18004 THEN 'Table-valued SQL Function'  
            WHEN 18259 THEN 'Server Role'  
            WHEN 18263 THEN 'Microsoft Windows Group'  
            WHEN 19265 THEN 'Asymmetric Key'  
            WHEN 19277 THEN 'Master Key'  
            WHEN 19280 THEN 'Primary Key'  
            WHEN 19283 THEN 'ObfusKey'  
            WHEN 19521 THEN 'Asymmetric Key Login'  
            WHEN 19523 THEN 'Certificate Login'  
            WHEN 19538 THEN 'Role'  
            WHEN 19539 THEN 'SQL Login'  
            WHEN 19543 THEN 'Windows Login'  
            WHEN 20034 THEN 'Remote Service Binding'  
            WHEN 20036 THEN 'Event Notification on Database'  
            WHEN 20037 THEN 'Event Notification'  
            WHEN 20038 THEN 'Scalar SQL Function'  
            WHEN 20047 THEN 'Event Notification on Object'  
            WHEN 20051 THEN 'Synonym'  
            WHEN 20307 THEN 'Sequence'  
            WHEN 20549 THEN 'End Point'  
            WHEN 20801 THEN 'Adhoc Queries which may be cached'  
            WHEN 20816 THEN 'Prepared Queries which may be cached'  
            WHEN 20819 THEN 'Service Broker Service Queue'  
            WHEN 20821 THEN 'Unique Constraint'  
            WHEN 21057 THEN 'Application Role'  
            WHEN 21059 THEN 'Certificate'  
            WHEN 21075 THEN 'Server'  
            WHEN 21076 THEN 'Transact-SQL Trigger'  
            WHEN 21313 THEN 'Assembly'  
            WHEN 21318 THEN 'CLR Scalar Function'  
            WHEN 21321 THEN 'Inline scalar SQL Function'  
            WHEN 21328 THEN 'Partition Scheme'  
            WHEN 21333 THEN 'User'  
            WHEN 21571 THEN 'Service Broker Service Contract'  
            WHEN 21572 THEN 'Trigger on Database'  
            WHEN 21574 THEN 'CLR Table-valued Function'  
            WHEN 21577 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'  
            WHEN 21581 THEN 'Service Broker Message Type'  
            WHEN 21586 THEN 'Service Broker Route'  
            WHEN 21587 THEN 'Statistics'  
            WHEN 21825 THEN 'User'  
            WHEN 21827 THEN 'User'  
            WHEN 21831 THEN 'User'  
            WHEN 21843 THEN 'User'  
            WHEN 21847 THEN 'User'  
            WHEN 22099 THEN 'Service Broker Service'  
            WHEN 22601 THEN 'Index'  
            WHEN 22604 THEN 'Certificate Login'  
            WHEN 22611 THEN 'XMLSchema'  
            WHEN 22868 THEN 'Type'  
            ELSE CAST(ObjectType AS VARCHAR(max))  
        END AS ObjectTypeDesc,  
        ObjectID,   
        ObjectName,  
        EventSubClass  
    FROM sys.fn_trace_gettable(@file, DEFAULT)  
    WHERE EventClass IN (46,47,164)  
    AND ApplicationName <> 'SQLServerCEIP' --Telemetry  
)  
SELECT * FROM TraceCTE   
WHERE EventSubClass = 0  
ORDER BY StartTime;  

Default Trace has captured all Object Schema modifications but without many details. Analyzing the trace files, we can find out which databases have been modified. We know the names of objects that were created, modified or dropped. We also know who executed changes and when.

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Radha Pusarapu (Ernst & Young LLP) ,

In order to find out who update the table, you could try with below options:

1. Try and read the Transaction Logs to see what happened.
2. Start trace in SQL Server profiler and checked events(TSQL-SQL:BatchCompleted,SQL:BatchStarting,SQL:StmtCompleted and SQL:StmtStarting)(Recommended).
3. Create a trigger and track the username into a log table. (SELECT APP_NAME() or SELECT program_name from sys.dm_exec_sessions where session_id=@@SPID).
4. Try with SQLAudit functionality which will give you some great granualar information about who is touching your tables, and the commands that are being executed.
5. Change Data Capture (CDC).
6. Third-part tool like ApexSQL Log.

You could refer more details in How to find who changed a table value in SQL Server 2014?.

----------

Best regards,
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.