THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Season's Greetings!

 

I was catching up on my blog reading and came across a very nice post on Ward Pond's blog;  he gives us some TSQL code that produces a holiday message. It's a few days late, but it's the thought that counts, right?

I did have a few problems with the code on Ward's site... when I copied and pasted to SSMS, there were no line breaks at all, so everything appeared as one huge long line. That made it hard to interpret, and hard to troubleshoot when a syntax error was reported. It turned out that not only did no line breaks appear, but whatever was in the code to break the lines before the word 'cross' got swallowed up, so the last part of the code was:

from firstTablecross join SecondTablecross join ThirdTablecross join ForthTablewhere therow%2!=0

So I fixed up the code to add line breaks, to separate the 'cross' from the preceding table names, and to fix a spelling error.

Enjoy... and thanks, Ward!

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)
   , FourthTable 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 FourthTable
where therow%2!=0;

Happy New Year!

~Kalen

Published Monday, December 29, 2008 11:50 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

noeldr said:

December 29, 2008 5:41 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement