SQL Alternative to Virtual Table using “With” Keyword

Let’s say I have a table called States with the following columns and data.

Now let’s say I need to create a virtual table that consists of the ID and Abbreviation columns only.  I can do this by using the “with” keyword.  Here’s an example:

with myVirtualTable as (
Select ID, Abbreviation
From States
)

select * From myVirtualTable

Here are the results:

need

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