I wanted to spend a few minutes highlighting a couple of important tools for figuring out what information you have available to you within SQL Server. sys.all_objects and sys.all_columns. Note the All. These system views are almost exactly the same as sys.objects and sys.columns but with the single major difference that they include system objects. This let’s you do things like this:

I’d like to know all of the other all views available to me.

SELECT * FROM sys.all_objects WHERE name LIKE 'all[_]%'

I’d like to know all of my options when dealing with replication.

SELECT * FROM sys.all_objects WHERE name LIKE '%repl%'

I’d like to know all of the system views that refer back to objects.

SELECT object_name(object_id), * FROM sys.all_columns 
WHERE name LIKE '%object%'

You can see with a little extra work you can get a solid idea of what’s available. In fact, add that to sys.all_sql_modules and you can seven get an idea of how something is working.

SELECT definition FROM sys.all_sql_modules 
WHERE object_id = object_id('sys.all_views')

CREATE VIEW sys.all_views AS
SELECT o.name, o.id AS object_id,
r.indepid AS principal_id, o.nsid AS schema_id,
o.pid AS parent_object_id,
o.type, n.name AS type_desc,
o.created AS create_date, o.modified AS modify_date,
convert(bit, o.status & 1) AS is_ms_shipped,
convert(bit, o.status & 16) AS is_published,
convert(bit, o.status & 64) AS is_schema_published,
convert(bit, o.status & 0x1000) AS is_replicated,
convert(bit, o.status & 0x2000) AS has_replication_filter,
convert(bit, o.status & 512) AS has_opaque_metadata,
convert(bit, o.status & 2048) AS has_unchecked_assembly_data,
convert(bit, o.status & 1024) AS with_check_option,
convert(bit, o.status & 2) AS is_date_correlation_view,
convert(bit, o.status & 0x01000000) AS is_tracked_by_cdc
FROM sys.sysschobjs o
LEFT JOIN sys.syssingleobjrefs r ON r.depid = o.id AND r.class = 97 AND r.depsubid = 0-- SRC_OBJOWNER
LEFT JOIN sys.syspalnames n ON n.class = 'OBTY' AND n.value = o.type
WHERE o.nsclass = 0 -- x_eonc_Standard
AND o.pclass = 1 AND type = 'V' -- x_eunc_Object
AND has_access('AO', o.id) = 1

A couple of notes: You are going to see both documented and undocumented objects this way. Before you use anything go to BOL to find out which it is, and not incidentally see exactly what it does. And last but not least every now and again you are going to see commands and ways of doing things you haven’t seen before. Bonus! New knowledge! Of course, be warned that occasionally you’ll see commands that we don’t actually have access to.