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
, 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)
where system_type_id = 36) + (
select top 1 substring(wait_type,10,2)
where wait_type like 'PageIO%' ) + (
select substring(@@version,4,1) ) + (
select substring(object_name(55),4,2) ) + (
where configuration_id = 124 ) + (
select left(db_name(1),3) ) theEnd
select case therow
when 11 then
when 13 then upper(theEnd )
else stuff( theLine,(datalength(theLine)/2) -
replicate(char(42),therow)) end ' '
cross join SecondTable
cross join ThirdTable
cross join FourthTable
Happy New Year!