December 4, 2008 at 2:13 am
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!
December 4, 2008 at 8:32 am
No one? Anyone know if there is a system table or view that contains that information? Surley somone must have an idea!
December 5, 2008 at 6:42 am
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. 🙁
December 30, 2008 at 4:02 pm
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]
December 31, 2008 at 2:37 am
RBarryYoung (12/30/2008)
Select * from sys.types
Thanks for that, but that only returns Data Types. I want object types...
December 31, 2008 at 8:30 am
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]
February 13, 2009 at 1:51 am
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
February 13, 2009 at 2:50 am
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...
February 13, 2009 at 3:45 am
But I did just notice that under the Subclass value, we get the references that im after.
That should be ok!
Thanks for that!
September 5, 2012 at 8:48 am
try dbo.spt_values
September 5, 2012 at 5:55 pm
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]
September 6, 2012 at 2:08 am
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!
October 8, 2013 at 10:11 am
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;
May 8, 2014 at 7:16 am
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
March 10, 2015 at 9:23 pm
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