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