UPDATED 29 Dec 2008: Back to the tried-and-true source code formatting method, per Kalen’s comment and my response.
UPDATED 24 Dec 2008: I got this from a couple of different sources, so I didn't realize when I drafted this post that Dirk wrote this query. Yesterday. Dirk is obviously a genius with a huge spirit.
Courtesy of SQL Ranger Dirk Gubbels, here’s a query for the season.
Run this query in the master database on a SQL Server 2005 or SQL Server 2008 instance. Text output works best, but grid output is okay. It’s guaranteed safe..
with FirstTable as (select top 14 row_number() over (order by name) therow from master.sys.objects) , SecondTable as (select replicate(char(32),15) theLine) , ThirdTable as (select replicate(char(124),3) theOtherLine) , ForthTable as ( select ( select left(db_name(4),1) ) + ( select substring(db_name(2),2,1) ) + ( select replicate(substring(db_name(1),6,1),2) ) + ( select replace(schema_name(4),'s','') ) + ( select char(max_length * 2) from master.sys.types where system_type_id = 36) + ( select top 1 substring(wait_type,10,2) from master.sys.dm_os_wait_stats where wait_type like 'PageIo%' ) + ( select substring(@@version,4,1) ) + ( select substring(object_name(55),4,2) ) + ( select convert(char(1),(reverse(convert(char(7),name)))) from sys.configurations where configuration_id = 124 ) + ( select left(db_name(1),3) ) theEnd ) select case therow when 11 then stuff( theLine,(datalength(theLine)/2) - 1,3,TheOtherLine) when 13 then upper(theEnd ) else stuff( theLine,(datalength(theLine)/2) - (theRow/2),therow,replicate(char(42),therow)) end ' ' from firstTable cross join SecondTable cross join ThirdTable cross join ForthTable where therow%2!=0
Best wishes for a blessed and happy holiday!
Here is an old one
Thanks for the great query!
I found when I copied and pasted into SSMS, there were no line breaks; it came across all as one long line, so it was very difficult to get any kind of idea as to what the query was doing.
It was particularly difficult when I got a syntax error message...trying to debug one long line of code was a challenge.
It turned out that the breaks before the word 'cross' were being swallowed, so the last part of the query was this:
from firstTablecross join SecondTablecross join ThirdTablecross join ForthTablewhere therow%2!=0
I think I'll blog this with my adjustments to the code. I hope that's ok.
Happy New Year!
I was catching up on my blog reading and came across a very nice post on Ward Pond's blog ; he gives
The law of unintended consequences is an amazing thing. When I posted Dirk Gubbels’ holiday greeting
Interesting, good use of features (over, various functions, a dmv and a system table) as well :)
Stephen Forte has Dirk's code as well,
@Mike: I agree with you completely. It's a wonderful use of a lot of nifty SQL Server 2005+ features. I'm a big CTE fan, so I'd add that to your list..
And any time you see a modulus operator, you KNOW something fun is going on.
Thanks for your note, Mike!
On the heels of Christmas Eve’s seasonal query from Dirk Gubbels comes this gem from Madhivanan (with
If this keeps up, I might need to define a tag for this stuff.. Our old friend Adam Machanic , perhaps
This post is a cousin of sorts to last December’s post regarding the OBJECT_ID function. I learned this
Here’s another seasonal query for you : select datediff ( dd , '10/20/2008' , '3/3/2009' ) -----------