/*
A couple of years ago, I wrote the Simple-Talk Prettifier. This is really no more than a stored procedure that renders SQL code  as HTML. I then suggested that the code should be allowed on Simple-Talk, and had the humiliation of seeing the contemptuous ease with which Neil and the Red Gate testers found all sorts of examples of code that it failed to render properly. I'd tested it with a lot of code, but had no idea of the scale of testing that goes on for a product such as SQL Refactor, SQL Prompt or SQL Doc. Much to my indignation, they used Evil SQL. Evil SQL?  Hmm.. Stuff like this. */
 
CREATE TABLE ## ( # INT )
DECLARE @ INT  set @=9
INSERT  INTO ##
        ( # )
        SELECT  @%2
SELECT  *
FROM    ##
DROP TABLE ##
/* can you imagine that this would execute perfectly happily? Try it!  */
 
/* The team that writes these tools at Red Gate come across all sorts of oddities. The other day, they showed me a couple of strange things that inspired me to write this. */
--first create a GetDate schema
CREATE SCHEMA GETDATE
--and a GetDate table to go in it.
CREATE TABLE GetDate.GetDate
(
GetDate DATETIME,
[GetDate GetDate] DATETIME
)
GO
--and a function called GetDate
CREATE FUNCTION GetDate()
RETURNS TABLE
AS RETURN
(
     SELECT GetDate() AS [GetDate]
)
GO
-- Now we can write some startlingly silly code
INSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])
     SELECT GetDate() AS GetDate, GetDate FROM GetDate()
--but we can do far far siller stuff if we wanted purely because there is no restriction on what goes between angle-brackets      
GO
CREATE FUNCTION [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
RETURNS TABLE
AS RETURN
(
     SELECT GetDate() AS [GetDate]
)
GO
 
INSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])
     SELECT GetDate() AS GetDate, GetDate FROM [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
 
DROP FUNCTION GETDATE,[GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]
DROP TABLE GETDATE.GetDate
DROP SCHEMA GETDATE
 
/* but this is boring. We are much better off using a poem by Odgen Nash as the name of a table. Whoever said that having object-naming policies was a good thing? */
CREATE FUNCTION
   [Candy
    Is dandy
    But liquor
    Is quicker.]()
RETURNS TABLE
AS RETURN
(
     SELECT GetDate() AS [GetDate]
)
GO
Select * from
    [Candy
    Is dandy
    But liquor
    Is quicker.]()
   
/* but you try leaving out a space!  I leave to the reader the exercise of having an entire database of different tables, all of which use exactly the same poem for all its object names. This is serious obfuscation. */ 
 
/* we could be a bit more creative and see if we can execute a verse of Macauley's famous poem 'Horatius'. */
 
--create a table with a slightly unusual name
create table [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
    (
[The horsemen and the footmen
    Are pouring in amain] int,
    [, hid by beech and pine,] varchar(100)
)   
--put a value into this table
insert into [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,] ([The horsemen and the footmen
    Are pouring in amain], [, hid by beech and pine,])
    Select 1,'an eagle’s nest, hangs on the crest
    Of purple Apennine;'
 
/* now, with that preparation work done, we can execute the third verse */
Select   
    [The horsemen and the footmen
    Are pouring in amain]
    From [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
    Where[, hid by beech and pine,]
    Like 'an eagle’s nest, hangs on the crest
    Of purple Apennine;'
 
/* If this seems a bit .er.. florid, you can go minimalist approach to Evil Code */
--first clear up
DROP TABLE [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
DROP FUNCTION [Candy
    Is dandy
    But liquor
    Is quicker.]
--now let's go neo-bauhaus
 
Create table [
] (# int)
 
insert   into [
]        select   1
 
Select   *
from     [
]
 
create table [ ] (# int)
insert   into " " select   1
select   *
     from     " "
 
drop table "
"," "
/*which twice creates a TABLE, inserts data INTO it, SELECTs the contents and then drops both tables.

So armed with this little lot I downloaded the new SQL Prompt 4  and tested it. Did it blink an eyelid? Did it lay it all out nicely? Darn it; I'd have loved to find code that SQL Prompt would trip over. Memories of their transparent joy when finding problems with the Prettifier still linger. */