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 and code to get the best help
- 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 14 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply