All my good intentions to write a couple of posts a week are already down in flames.
So I’ll post a couple of entries today.
First, a followup to my previous post about the new catalog views. I admitted last time that I loved them, but I’ll admit now that not only do I not know what every column of every view means, I haven’t even discovered some of the views. But that keeps the mystery alive, and mystery is a good thing with something or someone you love, right?
This also relates to an earlier post about the relaxed syntax for the system defined table valued functions, such as fn_trace_getinfo, for which you no longer need to use the double colon syntax. It didn’t even occur to me that I don’t need to use the function at all. I use fn_trace_getinfo all the time and have no problems typing in the name quickly, so I never thought to search if there was anything new and better in SQL Server 2005.
Just last week I found out there is something new and better. There is a view called sys.traces, which not only has the five properties that fn_trace_getinfo returns, it has a lot more besides. And, the results come back in one row for each trace, so it is a lot easier to get information about multiple traces that may be running.
Here is the column list:
id
status
path
max_size
stop_time
max_files
is_rowset
is_rollover
is_shutdown
is_default
buffer_count
buffer_size
file_position
reader_spid
start_time
last_event_time
event_count
dropped_event_count
Those of you who have worked with traces will probably know what most of the columns mean, but here’s a few more details. ‘is_rowset’ refers to a trace that is returning rows one at a time, and that applies to traces that are returning events to the Profiler. ‘file_position’ refers only to a trace that is writing to a file, and indicates the position in the file where the last writes were made. That can help give you an idea how big your trace file is getting to be.
I had more fun with catalog views while trying to find the metadata that would just give me the list of column names that I have above. Yes, sp_help will show me, but that doesn’t give queryable, tabular results, and I wanted just a list of column names for one particular view. I tried looking in sys.columns, but it doesn’t include columns for system objects. Then I remembered sys.system_objects, and thought there might be a comparable view called sys.system_columns. Sure enough, the following query gave me what I wanted:
select name from sys.system_columns
where object_id = object_id('sys.traces')
order by column_id
Then I thought I’d see how sp_help returned the column names, and this query showed me the definition:
select object_definition(object_id('sys.sp_help'))
It uses a view called sys.all_columns which includes columns from system objects and user objects. So I could get the columns from sys.traces from either sys.system_columns or sys.all_columns.
Now I’ll go see what other fun stuff I can discover in the metadata.
-- Kalen