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.

How to Create an Identity Column with SQL “Into” Statement

Use the syntax below to create an identity column with an “Into” statement.

SELECT IDENTITY(INT, 1, 1) AS MyIdentityColumn
	,Name
INTO #temp
FROM dbo.SysObjects
Where	XType = 'U'
Order by Name