Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ObjectType Reference from Default Trace Expand / Collapse
Author
Message
Posted Thursday, December 4, 2008 2:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
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
Posted Thursday, December 4, 2008 8:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
No one? Anyone know if there is a system table or view that contains that information? Surley somone must have an idea!
Post #613743
Posted Friday, December 5, 2008 6:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
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.
Post #614474
Posted Tuesday, December 30, 2008 4:02 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
Select * from sys.types

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #627720
Posted Wednesday, December 31, 2008 2:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
RBarryYoung (12/30/2008)
Select * from sys.types


Thanks for that, but that only returns Data Types. I want object types...
Post #627909
Posted Wednesday, December 31, 2008 8:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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
Posted Friday, February 13, 2009 1:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 8:09 AM
Points: 6,735, Visits: 8,495
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #656345
Posted Friday, February 13, 2009 2:50 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
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
Posted Friday, February 13, 2009 3:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
But I did just notice that under the Subclass value, we get the references that im after.

That should be ok!

Thanks for that!
Post #656396
Posted Wednesday, September 5, 2012 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 9:31 AM
Points: 1, Visits: 1
try dbo.spt_values
Post #1354614
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse