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
Source Click Here.
Post a Comment