Exploring system tables, views, SPs etc

Kenneth Fisher, 2018-04-18

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads