I’m in the middle of reading a good book on DMVs, “SQL Server DMVs in Action” http://www.manning.com/stirk by Ian W. Stirk. In it he points out that you can create a temporary table from a query and use it to store the data from multiple runs of the query. This is a very handy technique and one I’ve been using for years. Ian recommends that you had a simple 1=2 to the WHERE clause of the query, and of course an INTO clause. So the query:

 SELECT members.name AS membername, members.principal_id, members.type_desc, 
members.default_schema_name, roles.name AS rolename
FROM sys.database_principals members
LEFT OUTER JOIN sys.database_role_members xref
ON members.principal_id = xref.member_principal_id
LEFT OUTER JOIN sys.database_principals roles
ON xref.role_principal_id = roles.principal_id
WHERE members.type_desc = 'SQL_USER'

can be changed to

 SELECT members.name AS membername, members.principal_id, members.type_desc, 
members.default_schema_name, roles.name AS rolename
INTO #UsersnRoles
FROM sys.database_principals members
LEFT OUTER JOIN sys.database_role_members xref
ON members.principal_id = xref.member_principal_id
LEFT OUTER JOIN sys.database_principals roles
ON xref.role_principal_id = roles.principal_id
WHERE members.type_desc = 'SQL_USER'
AND 1=2

This will create a temporary table #UsersnRoles that has the correct structure to hold the data from my query. This method is quick, because, as Ian mentions in his book, SQL only really looks at the structure of the tables. Code using it is also low maintenance because if the underlying tables change then so will the temp table (on the next run of course).

Given that there are lots of ways to do virtually anything in SQL, it won’t surprise anyone that this isn’t the method I use. Let’s say instead the query was:

 SELECT members.name AS membername, members.principal_id, members.type_desc, 
members.default_schema_name, roles.name AS rolename
FROM sys.database_principals members
LEFT OUTER JOIN sys.database_role_members xref
ON members.principal_id = xref.member_principal_id
LEFT OUTER JOIN sys.database_principals roles
ON xref.role_principal_id = roles.principal_id
WHERE members.type_desc = 'SQL_USER'
OR roles.name = ' db_owner' 

Now I have a slight, and admittedly very slight, problem. Because I have an OR in my WHERE clause I now have to go back and put in parens before I add the “AND 1=2” or I won’t get the correct results. Also please remember that this is a very simple example. I’ve had cases where my WHERE clause was a dozen lines long or longer and adding “AND 1=2″ was more complicated than it was worth. Because of this I generally prefer to use TOP 0 instead. Using this method the query looks like this:

 SELECT TOP 0 members.name AS membername, members.principal_id, members.type_desc, 
members.default_schema_name, roles.name AS rolename
INTO #UsersnRoles
FROM sys.database_principals members
LEFT OUTER JOIN sys.database_role_members xref
ON members.principal_id = xref.member_principal_id
LEFT OUTER JOIN sys.database_principals roles
ON xref.role_principal_id = roles.principal_id
WHERE members.type_desc = 'SQL_USER'
OR roles.name = ' db_owner' 

I’ve found this to be just as easy but without the possible “gotcha” the 1=2 method has.

I should probably mention for those who haven’t run this type of query before that if querying a single table this method will create tables that include identity columns, but not column level constraints (haven’t tried table), defaults, foreign & primary keys, or indexes from the parent table. If multiple tables are used the identity column is excluded also. I’ve found this handy when creating archive tables.

SELECT TOP 0 * INTO tableNameArchive FROM tableName

I still have to add in any defaults, constraints etc that I want, and sometimes I have to remove or rename the identity column. But it’s a quick and easy starting point.