Freigeben über

A Seasonal Query

  • Artikel

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!

-wp

Comments

  • Anonymous
    January 01, 2003
    This post is a cousin of sorts to last December’s post regarding the OBJECT_ID function. I learned this

  • Anonymous
    January 01, 2003
    If this keeps up, I might need to define a tag for this stuff.. Our old friend Adam Machanic , perhaps

  • Anonymous
    January 01, 2003
    Here’s another seasonal query for you : select datediff ( dd , '10/20/2008' , '3/3/2009' ) -----------

  • Anonymous
    January 01, 2003
    On the heels of Christmas Eve’s seasonal query from Dirk Gubbels comes this gem from Madhivanan (with

  • Anonymous
    January 01, 2003
    Stephen Forte has Dirk's code as well, here..

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

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

  • Anonymous
    January 01, 2003
    @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!

  • Anonymous
    January 01, 2003
    The law of unintended consequences is an amazing thing. When I posted Dirk Gubbels’ holiday greeting

  • Anonymous
    December 26, 2008
    Here is an old one http://toponewithties.blogspot.com/2006/08/t-sql-drawing.html

  • Anonymous
    December 29, 2008
    The comment has been removed

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