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: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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, November 6, 2014 6:07 AM
Points: 291, Visits: 481
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: Thursday, October 23, 2014 2:58 PM
Points: 24, Visits: 115
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: Friday, October 10, 2014 2:29 AM
Points: 82, Visits: 416
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