Find SQL Changes

Find SQL Changes.sql

-- =====================================
-- ==== FIND CHANGES in SQL OBJECTS ====
-- =====================================

-- Optional: Find changes ig LOG Trace file
DECLARE @search_log bit = 1 -- 0=Do not search trace log; 1=Search trace log (WARNING: SLOW!!)

-- ============================================================================
-- Search sys.objects
SELECT  TOP (10000)
        * --name, type, create_date, modify_date
FROM    sys.objects
WHERE   type IN ('P', 'IF', 'FN', 'TF', 'V', 'SN', 'U')
--  AND name LIKE '%YOUR_OBJECT_NAME%'
ORDER BY modify_date DESC

-- Filter for sys.objects.type:
-- C  = CHECK_CONSTRAINT
-- D  = DEFAULT_CONSTRAINT
-- F  = FOREIGN_KEY_CONSTRAINT
-- FN = SQL_SCALAR_FUNCTION
-- IF = SQL_INLINE_TABLE_VALUED_FUNCTION
-- IT = INTERNAL_TABLE
-- P  = SQL_STORED_PROCEDURE
-- PK = PRIMARY_KEY_CONSTRAINT
-- S  = SYSTEM_TABLE
-- SN = SYNONYM
-- SO = SEQUENCE_OBJECT
-- SQ = SERVICE_QUEUE
-- TF = SQL_TABLE_VALUED_FUNCTION
-- TR = SQL_TRIGGER
-- TT = TYPE_TABLE
-- U  = USER_TABLE
-- UQ = UNIQUE_CONSTRAINT
-- V  = VIEW

-- ============================================================================
-- LOG TRACE: If you need to search the trace log...
-- NOTE: Only has data since last log backup
-- WARNING: It is slow and takes a long time...
IF COALESCE(@search_log, 0) = 1
BEGIN
    DECLARE @db_name    NVARCHAR(256)
        ,   @filename   NVARCHAR(4000)

    SET @db_name = DB_NAME();

    SELECT  @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
    FROM    sys.traces
    WHERE   is_default = 1;

    SELECT  TOP (10000)
            gt.HostName
        ,   gt.ApplicationName
        ,   gt.NTUserName
        ,   gt.NTDomainName
        ,   gt.LoginName
        ,   gt.SPID
        ,   gt.EventClass
        ,   te.Name AS EventName
        ,   gt.EventSubClass
        ,   gt.TEXTData
        ,   gt.StartTime
        ,   gt.EndTime
        ,   gt.ObjectName
        ,   gt.DatabaseName
        ,   gt.FileName
        ,   gt.IsSystem
        ,   gt.ObjectType
        ,   tot.ObjectTypeName
    FROM    [fn_trace_gettable](@filename, DEFAULT) gt
    JOIN    sys.trace_events te ON gt.EventClass = te.trace_event_id
    LEFT JOIN (VALUES -- trace_object_types(ObjectType, ObjectTypeName)
                  (1, 'Index')
                , (2, 'Database')
                , (3, 'User Object')
                , (4, 'CHECK Constraint')
                , (5, 'Default or DEFAULT Constraint')
                , (6, 'FOREIGN KEY Constraint')
                , (7, 'PRIMARY KEY Constraint')
                , (8, 'Stored Procedure')
                , (9, 'User-Defined Function')
                , (10, 'Rule')
                , (11, 'Replication Filter Stored Procedure')
                , (12, 'System Table')
                , (13, 'Trigger')
                , (14, 'Inline Function')
                , (15, 'Table Valued UDF')
                , (16, 'UNIQUE Constraint')
                , (17, 'User Table')
                , (18, 'View')
                , (19, 'Extended Stored Procedure')
                , (20, 'Ad hoc Query')
                , (21, 'Prepared Query')
                , (8259, 'Check Constraint')
                , (8260, 'Default')
                , (8262, 'Foreign-key Constraint')
                , (8272, 'Stored Procedure')
                , (8274, 'Rule')
                , (8275, 'System Table')
                , (8276, 'Trigger on Server')
                , (8277, '(User-defined) Table')
                , (8278, 'View')
                , (8280, 'Extended Stored Procedure')
                , (16724, 'CLR Trigger')
                , (16964, 'Database')
                , (16975, 'Object')
                , (17222, 'FullText Catalog')
                , (17232, 'CLR Stored Procedure')
                , (17235, 'Schema')
                , (17475, 'Credential')
                , (17491, 'DDL Event')
                , (17741, 'Management Event')
                , (17747, 'Security Event')
                , (17749, 'User Event')
                , (17985, 'CLR Aggregate Function')
                , (17993, 'Inline Table-valued SQL Function')
                , (18000, 'Partition Function')
                , (18002, 'Replication Filter Procedure')
                , (18004, 'Table-valued SQL Function')
                , (18259, 'Server Role')
                , (18263, 'Microsoft Windows Group')
                , (19265, 'Asymmetric Key')
                , (19277, 'Master Key')
                , (19280, 'Primary Key')
                , (19283, 'ObfusKey')
                , (19521, 'Asymmetric Key Login')
                , (19523, 'Certificate Login')
                , (19538, 'Role')
                , (19539, 'SQL Login')
                , (19543, 'Windows Login')
                , (20034, 'Remote Service Binding')
                , (20036, 'Event Notification on Database')
                , (20037, 'Event Notification')
                , (20038, 'Scalar SQL Function')
                , (20047, 'Event Notification on Object')
                , (20051, 'Synonym')
                , (20549, 'End Point')
                , (20801, 'Adhoc Queries which may be cached')
                , (20816, 'Prepared Queries which may be cached')
                , (20819, 'Service Broker Service Queue')
                , (20821, 'Unique Constraint')
                , (21057, 'Application Role')
                , (21059, 'Certificate')
                , (21075, 'Server')
                , (21076, 'Transact-SQL Trigger')
                , (21313, 'Assembly')
                , (21318, 'CLR Scalar Function')
                , (21321, 'Inline scalar SQL Function')
                , (21328, 'Partition Scheme')
                , (21333, 'User')
                , (21571, 'Service Broker Service Contract')
                , (21572, 'Trigger on Database')
                , (21574, 'CLR Table-valued Function')
                , (21577, 'Internal Table')
                , (21581, 'Service Broker Message Type')
                , (21586, 'Service Broker Route')
                , (21587, 'Statistics')
                , (21825, 'User')
                , (21827, 'User')
                , (21831, 'User')
                , (21843, 'User')
                , (21847, 'User')
                , (22099, 'Service Broker Service')
                , (22601, 'Index')
                , (22604, 'Certificate Login')
                , (22611, 'XMLSchema')
                , (22868, 'Type')
            ) tot(ObjectType, ObjectTypeName) ON gt.ObjectType = tot.ObjectType
    WHERE
            gt.DatabaseName = @db_name
        AND gt.EventClass IN (164)
    --  AND gt.EventSubClass = 2
    --  AND gt.ObjectType NOT IN (17747) -- 17747=Security Event
    --  AND gt.ObjectName IN ('YOUR_OBJECT_NAME')
    ORDER BY gt.StartTime DESC;
END