ObjectType Reference from Default Trace

  • Hi there!

    I'm querying the default trace to get some audit info on some DB objects. (The variable @Trace contains the location of the default trace)

    Select * FROM ::fn_trace_gettable(@Trace,0)

    In the ObjectType field from the trace, we get some reference numbers. eg 8278.

    I found on MSDN a reference table (of sorts) that releate these numbers to Meaningful names. http://msdn.microsoft.com/en-us/library/ms180953.aspx

    Eg 8278 = View

    My question is: Is there a system table that contains this information? Otherwise I will have to create my own lookup as per below.

    1Index

    2Database

    3User Object

    4CHECK Constraint

    5Default or DEFAULT Constraint

    6FOREIGN KEY Constraint

    7PRIMARY KEY Constraint

    8Stored Procedure

    9User-Defined Function (UDF)

    10Rule

    11Replication Filter Stored Procedure

    12System Table

    13Trigger

    14Inline Function

    15Table Valued UDF

    16UNIQUE Constraint

    17User Table

    18View

    19Extended Stored Procedure

    20Ad hoc Query

    21Prepared Query

    8259Check Constraint

    8260Default (constraint or standalone)

    8262Foreign-key Constraint

    8272Stored Procedure

    8274Rule

    8275System Table

    8276Trigger on Server

    8277(User-defined) Table

    8278View

    8280Extended Stored Procedure

    16724CLR Trigger

    16964Database

    16975Object

    17222FullText Catalog

    17232CLR Stored Procedure

    17235Schema

    17475Credential

    17491DDL Event

    17741Management Event

    17747Security Event

    17749User Event

    17985CLR Aggregate Function

    17993Inline Table-valued SQL Function

    18000Partition Function

    18002Replication Filter Procedure

    18004Table-valued SQL Function

    18259Server Role

    18263Microsoft Windows Group

    19265Asymmetric Key

    19277Master Key

    19280Primary Key

    19283ObfusKey

    19521Asymmetric Key Login

    19523Certificate Login

    19538Role

    19539SQL Login

    19543Windows Login

    20034Remote Service Binding

    20036Event Notification on Database

    20037Event Notification

    20038Scalar SQL Function

    20047Event Notification on Object

    20051Synonym

    20549End Point

    20801Adhoc Queries which may be cached

    20816Prepared Queries which may be cached

    20819Service Broker Service Queue

    20821Unique Constraint

    21057Application Role

    21059Certificate

    21075Server

    21076Transact-SQL Trigger

    21313Assembly

    21318CLR Scalar Function

    21321Inline scalar SQL Function

    21328Partition Scheme

    21333User

    21571Service Broker Service Contract

    21572Trigger on Database

    21574CLR Table-valued Function

    21577Internal Table (For example, XML Node Table, Queue Table.)

    21581Service Broker Message Type

    21586Service Broker Route

    21587Statistics

    21825User

    21827User

    21831User

    21843User

    21847User

    22099Service Broker Service

    22601Index

    22604Certificate Login

    22611XMLSchema

    22868Type

    From my quick look around, it would apear that this information does not exist in a system table or view. Does anyone know an better?

    Many thanks!

  • No one? Anyone know if there is a system table or view that contains that information? Surley somone must have an idea!

  • I am going to guess by the lack of any response that no one knows if there is a system table that contains this information.

    Surprising. 🙁

  • Select * from sys.types

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (12/30/2008)


    Select * from sys.types

    Thanks for that, but that only returns Data Types. I want object types...

  • Oops, sorry I thought that I had checked for that...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • hopping in very late ....

    but maybe this query gets you on the rails ...

    Select *

    from sys.trace_subclass_values TSV

    inner join sys.trace_columns TC

    on TC.name = 'objecttype'

    and TC.trace_column_id = TSV.trace_column_id

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Kinda, but theres no entries for anything like say:

    Event_trace_id = 8278

    This corresponds to a View

    or Event_trace_id = 22601

    Should be an Index.

    So as far as I can still tell- that table I posted at the start seems like the only way of linking up opject types to the default trace...

  • But I did just notice that under the Subclass value, we get the references that im after.

    That should be ok!

    Thanks for that!

  • try dbo.spt_values

  • dbs.basu (9/5/2012)


    try dbo.spt_values

    Note: 4-year old thread.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • dbs.basu (9/5/2012)


    try dbo.spt_values

    yep 4 years old 🙂

    But yes- this table does give the references i was after...

    Good to have as a reference for future searches.

    Many thanks!

  • Basu's suggestion was right.

    Here is my full query to list all DDL changes:

    ALTER PROCEDURE [dbo].[SchemaChangesToday]

    @dbName VARCHAR(128) = NULL

    AS

    BEGIN

    DECLARE @filename nvarchar(1000);

    -- Get the name of the current default trace

    SELECT @filename = cast(value as nvarchar(1000))

    FROM ::fn_trace_getinfo(default)

    WHERE traceid = 1 and property = 2;

    -- view current trace file

    SELECT LoginName, HostName, ApplicationName, DatabaseName, ObjectName, sv.name [ObjectType], te.Name [Action], StartTime, ftg.TextData

    FROM ::fn_trace_gettable(@filename, default) AS ftg

    LEFT JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

    LEFT JOIN spt_values sv ON ftg.ObjectType = sv.number

    WHERE (ftg.EventClass in (46,47,118,125,126,128,129,131,164,165,176,177,178))

    and DatabaseName = case when @dbName is null then databasename else @dbName end

    and DatabaseName != 'tempdb'

    and EventSubClass = 0

    ORDER BY ftg.StartTime desc;

    END;

  • bartedgerton (10/8/2013)


    Basu's suggestion was right.

    Here is my full query to list all DDL changes:

    ALTER PROCEDURE [dbo].[SchemaChangesToday]

    @dbName VARCHAR(128) = NULL

    AS

    BEGIN

    DECLARE @filename nvarchar(1000);

    -- Get the name of the current default trace

    SELECT @filename = cast(value as nvarchar(1000))

    FROM ::fn_trace_getinfo(default)

    WHERE traceid = 1 and property = 2;

    -- view current trace file

    SELECT LoginName, HostName, ApplicationName, DatabaseName, ObjectName, sv.name [ObjectType], te.Name [Action], StartTime, ftg.TextData

    FROM ::fn_trace_gettable(@filename, default) AS ftg

    LEFT JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

    LEFT JOIN spt_values sv ON ftg.ObjectType = sv.number

    WHERE (ftg.EventClass in (46,47,118,125,126,128,129,131,164,165,176,177,178))

    and DatabaseName = case when @dbName is null then databasename else @dbName end

    and DatabaseName != 'tempdb'

    and EventSubClass = 0

    ORDER BY ftg.StartTime desc;

    END;

    I think there may be duplicates in this result because of duplicate support values....

    Regards, Dave

  • dave hants (5/8/2014)


    bartedgerton (10/8/2013)


    Basu's suggestion was right.

    Here is my full query to list all DDL changes:

    ALTER PROCEDURE [dbo].[SchemaChangesToday]

    @dbName VARCHAR(128) = NULL

    AS

    BEGIN

    DECLARE @filename nvarchar(1000);

    -- Get the name of the current default trace

    SELECT @filename = cast(value as nvarchar(1000))

    FROM ::fn_trace_getinfo(default)

    WHERE traceid = 1 and property = 2;

    -- view current trace file

    SELECT LoginName, HostName, ApplicationName, DatabaseName, ObjectName, sv.name [ObjectType], te.Name [Action], StartTime, ftg.TextData

    FROM ::fn_trace_gettable(@filename, default) AS ftg

    LEFT JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

    LEFT JOIN spt_values sv ON ftg.ObjectType = sv.number

    WHERE (ftg.EventClass in (46,47,118,125,126,128,129,131,164,165,176,177,178))

    and DatabaseName = case when @dbName is null then databasename else @dbName end

    and DatabaseName != 'tempdb'

    and EventSubClass = 0

    ORDER BY ftg.StartTime desc;

    END;

    I think there may be duplicates in this result because of duplicate support values....

    Regards, Dave

    Ok, I know it has been almost a year since the last past to this thread and the thread is really old. But since I happened across the thread and the duplication of data issue, I figured I would chime in.

    The duplicate data can be fixed by adding the following to the join on spt_values

    AND sv.type = 'EOD'

    All of the values are duplicated in the table. There is one for each the EOD and the EOB types.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply