SQL Clone
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
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80476 Visits: 9519
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1300 Visits: 560
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 399
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 Eights!
SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)

Group: General Forum Members
Points: 889 Visits: 545
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 Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145011 Visits: 18651
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
Learn Extended Events

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