April 18, 2018 by Kenneth Fisher
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.