SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Exploring system tables, views, SPs etc

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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...