Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Creating an empty table from a SELECT statement

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.


Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...