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 Wednesday, September 5, 2012 5:55 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #1354962
Posted Thursday, September 6, 2012 2:08 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
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!
Post #1355098
Posted Tuesday, October 8, 2013 10:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:27 PM
Points: 24, Visits: 113
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;

Post #1502701
Posted Thursday, May 8, 2014 7:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 29, 2014 4:33 AM
Points: 82, Visits: 415
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

Post #1568875
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse