ObjectType Reference from Default Trace

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » ObjectType Reference from Default Trace

12 posts, Page 1 of 2

1 2 »»

ObjectType Reference from Default Trace

Rate Topic

Display Mode

Topic Options

Author

Message

Rin Sitah

Posted Thursday, December 04, 2008 2:13 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 276, Visits: 381

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.

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 (UDF)
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 (constraint or standalone)
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 (For example, XML Node Table, Queue 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

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!

Post #613481

Rin Sitah

Posted Thursday, December 04, 2008 8:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 276, Visits: 381

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

Post #613743

Rin Sitah

Posted Friday, December 05, 2008 6:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 276, Visits: 381

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. Sad

Post #614474

RBarryYoung

Posted Tuesday, December 30, 2008 4:02 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Select * from sys.types

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #627720

Rin Sitah

Posted Wednesday, December 31, 2008 2:37 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 276, Visits: 381

RBarryYoung (12/30/2008)

Select * from sys.types

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

Post #627909

RBarryYoung

Posted Wednesday, December 31, 2008 8:30 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #628114

ALZDBA

Posted Friday, February 13, 2009 1:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 6,866, Visits: 8,071

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

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

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

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

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

Post #656345

Rin Sitah

Posted Friday, February 13, 2009 2:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:02 AM
Points: 276, Visits: 381

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...

Post #656379