Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ObjectType Reference from Default Trace


ObjectType Reference from Default Trace

Author
Message
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9456 Visits: 9517
dbs.basu (9/5/2012)
try dbo.spt_values


Note: 4-year old thread.

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

Group: General Forum Members
Points: 299 Visits: 537
dbs.basu (9/5/2012)
try dbo.spt_values


yep 4 years old Smile
But yes- this table does give the references i was after...

Good to have as a reference for future searches.

Many thanks!
bartedgerton
bartedgerton
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 227
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;


dave hants
dave hants
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 539
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21137 Visits: 18259
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search