Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

A Seasonal Query

A Seasonal Query

  • Comments 12
  • Likes

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


  • Hi Ward

    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

  • I was catching up on my blog reading and came across a very nice post on Ward Pond's blog ; he gives

  • Interesting, good use of features (over, various functions, a dmv and a system table) as well :)

  • Stephen Forte has Dirk's code as well, here..

  • @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' ) -----------

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment