Look Up Tables in SQL Database Using SysObject

This one is probably a no brainer for many SQL experts, but I continue to talk to people that don’t know how to do this.

Here’s SQL to look up user tables:

SELECT	*
FROM	dbo.SysObjects
Where	XType = 'U'
Order by Name

For Stored Procedures, replace “XType = ‘U'” with “XType = ‘P'”

There’s a lot more you can find by replacing that string.  That table holds objects in the database including tables, columns, foreign keys, primary keys, views, triggers, constraints, defaults, and more.  Here’s a link for all the possible values.

Advertisements

About Brian McKay
Software Architect

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: