T-SQL coding challenge


This is an oldie, but a goodie.  It’s an article by Ken Thompson about, among other things, writing self-replicating programs.  Thompson is one of my heroes and someone who’s had a profound impact on the industry.  His list of accomplishments is too long to get into here, but let me summarize it by saying that he was one of the original authors of Unix, invented the B programming language (the precursor to C), UTF-8 encoding, regular expressions, and some of the earliest sophisticated chess programs.  It would be hard to overstate the influence he has had on computer science and computer technology.


Back to the article.  It touches on the educational value of writing a self-replicating program.  I’ve learned something from this exercise every time I’ve done it.  So, today’s challenge is to do this in T-SQL — to write a T-SQL script that replicates itself.  Please read Thompson’s article before beginning.  Then post back here with your solutions.  I know a lot of you like to email me directly, and that’s fine, but if you’ll post your answers here, everyone can benefit from them.  I’ll commit to perusing through these and posting my comments about your solutions asap.


Comments (76)

  1. mabster says:

    I think this works, though I guess it could be cheating. It’s kind of like a C program that simply reads from "main.c" and writes each line out to stdout:

    create procedure GetMe

    as

    declare @text varchar(500);

    select @text = text from sysobjects o

    inner join syscomments c on c.id = o.id

    where o.name = ‘GetMe’;

    exec(@text)

    GO

  2. mabster says:

    Ah – makes more sense to have "drop procedure GetMe" as the first line of the stored proc.

  3. mabster says:

    Here we go (feel free to delete my prior comments):

    create procedure GetMe

    as

    declare @text varchar(500);

    select @text = text from sysobjects o

    inner join syscomments c on c.id = o.id

    where o.name = ‘GetMe’;

    exec (‘drop procedure GetMe’);

    exec(@text);

    GO

  4. MSDNArchive says:

    Yeah, that’s kind of like cheating, alright πŸ™‚ Let’s pretend that syscomments doesn’t exist and your code must actually self replicate rather than load and display its own source code. Can you come up with a solution that does this?

  5. It may be cheating, but here’s a shorter version πŸ˜‰

    create proc x

    as

    exec sp_helptext ‘x’

    go

  6. MSDNArchive says:

    Oh, Adam. You guys are incorrigible πŸ™‚ I should have made this explicit: no grabbing source text via sp_helptext or syscomments (and no loading into the server your source script, either). Your code has to be self-replicating, not something that can merely load and display its source code.

  7. I know πŸ™‚

    Working on that now… -much- harder, to say the least!

  8. David Jones says:

    I think this works but it’s a little ugly. It’s an interesting problem that I’ve never actually attempted before.

    declare @string varchar(8000)

    declare @quote char(1)

    declare @asterisk char(1)

    declare @s char(1)

    declare @percent char(1)

    set @quote = char(39)

    set @asterisk = char(42)

    set @s = char(115)

    set @percent = char(37)

    set @string = ‘declare @string varchar(8000)

    declare @quote char(1)

    declare @asterisk char(1)

    declare @s char(1)

    declare @percent char(1)

    set @quote = char(39)

    set @asterisk = char(42)

    set @s = char(115)

    set @percent = char(37)

    set @string = *%s*

    set @string = replace(@string, @asterisk, @quote)

    set @string = replace(@string, @quote + @percent + @s + @quote, @quote + @string + @quote)

    set @string = replace(@string, @quote + @percent + @s + @quote, @asterisk + @percent + @s + @asterisk)

    print @string



    set @string = replace(@string, @asterisk, @quote)

    set @string = replace(@string, @quote + @percent + @s + @quote, @quote + @string + @quote)

    set @string = replace(@string, @quote + @percent + @s + @quote, @asterisk + @percent + @s + @asterisk)

    print @string

  9. Paul Ibison says:

    Not nice but seems to work ok…..

    declare @x varchar(1000)

    declare @y varchar(20)

    set @y = ””

    set @x = ‘declare @x varchar(1000) declare @y varchar(20) set @y = set @x =

    select substring (@x,1,57) + substring (@y,1,4) + substring (@y,1,4) +

    substring (@y,1,4) + substring (@y,1,4) + substring (@x,57,9)

    + substring (@y,1,4)

    + substring (@x,1,1000)

    + substring (@y,1,4)

    + substring (@x,67,1000)



    select substring (@x,1,57) + substring (@y,1,4) + substring (@y,1,4) +

    substring (@y,1,4) + substring (@y,1,4) + substring (@x,57,9)

    + substring (@y,1,4)

    + substring (@x,1,1000)

    + substring (@y,1,4)

    + substring (@x,67,1000)

  10. Steve Kass says:

    This "program" self-replicates if run in Query Analyzer:

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near ‘Msg’.

  11. MSDNArchive says:

    Paul Ibison: I think yours works better if you use PRINT rather than SELECT. If you use SELECT, I get a blank column header that’s not in the original. Other than that, the only differences with the original are formatting, so I think this one satisfies the criteria. Nice job!

  12. MSDNArchive says:

    David Jones: looks like you nailed it. Nice job!

  13. Denis Gobo says:

    How about?

    declare @x varchar(1000)

    declare @y varchar(20)

    set @y = ””

    set @x = ‘declare @x varchar(1000) declare @y varchar(20) set @y = set @x =

    select substring (@x,1,57) + substring (@y,1,4) + substring (@y,1,4) +

    substring (@y,1,4) + substring (@y,1,4) + substring (@x,57,9)

    + substring (@y,1,4)

    + substring (@x,1,1000)

    + substring (@y,1,4)

    + substring (@x,67,1000)



    dbcc inputbuffer (@@SpID)

  14. MSDNArchive says:

    Steve Kass: Clever, but I think you get to join Adam at the back of the class πŸ™‚ OK, in addition to not being able to cheat by getting at your source code somehow, you also have to have a running program. I forgot to mention that, but thanks for reminding me πŸ˜‰

  15. MSDNArchive says:

    Denis Gobo: this one doesn’t satisfy the criteria because:

    1. It returns a result set (the original script is, of course, just text)

    2. It returns the line "DBCC execution completed…" — also not in the original

    3. It’s result set returns text not in the original script

    Suggestions: Use PRINT instead of SELECT. Since SELECT always returns a result set, you’ll need PRINT to approximate your original script. Also, don’t rely on any technique that needs DBCC or you’ll see the message in #2 above in the output.

  16. MSDNArchive says:

    OK, so we have a couple of solutions, but they are (I think even their authors would admit) pretty long and ugly. Can you come up with a solution that is no more than ten lines long? (Adam & Steve: no cheating by combining multiple lines of code into one πŸ™‚

  17. Steve Kass says:

    How many lines does this count as? (Hopefully it doesn’t wrap. It’s 5 lines in my editor.)

    declare @ varchar(1000)

    set @ = ‘declare @ varchar(1000)

    set @ = "declare @ varchar(1000)"

    exec sp_executesql N"print substring(@,1,33)+char(39)+@+char(39) set @ = replace(@,char(34),char(39)) print substring(@,61,8000) ", N"@ varchar(1000)", @=@’

    exec sp_executesql N’print substring(@,1,33)+char(39)+@+char(39) set @ = replace(@,char(34),char(39)) print substring(@,61,8000) ‘, N’@ varchar(1000)’, @=@

  18. Steve Kass says:

    A shorter version:

    declare @ char(444)

    set @ = ‘declare @ char(444)

    set @ = "declare @ char(444)"

    exec sp_executesql N"print left(@,29)+char(39)+@+char(39) print right(replace(@,0x22,0x27),113)",N"@ char(444)",@’

    exec sp_executesql N’print left(@,29)+char(39)+@+char(39) print right(replace(@,0x22,0x27),113)’,N’@ char(444)’,@

  19. MSDNArchive says:

    Nice work. Any solution to this problem is necessarily a bit ugly, but this one is not as hard on the eyes. Any others out there?

  20. Paul Ibison says:

    Another route….

    set nocount on

    create table #m(E1 varchar(30), P1 int, V1 varchar(255))

    insert into #m(E1,P1,V1)

    exec(‘DBCC INPUTBUFFER (@@spid) WITH NO_INFOMSGS’)

    declare @EventInfo varchar(255)

    select @EventInfo = V1 from #m

    print @EventInfo

  21. Wow, is my solution ugly. It is the stored proc that drops and recreates itself:

    create proc x

    as

    declare @sql1 varchar(200)

    set @sql1 = ‘drop proc x’

    exec(@sql1)

    declare @sql2 varchar(600)

    set @sql2 = ‘

    create proc x

    as

    declare @sql1 varchar(200)

    set @sql1 = ”drop proc x”

    exec(@sql1)

    declare @sql2 varchar(600)

    set @sql2 = xyz

    set @sql2 = replace(@sql2, ”x”+”yz”, ”””” + replace(@sql2, ””””, ””””””) + ””””)

    exec(@sql2)

    set @sql2 = replace(@sql2, ‘x’+’yz’, ”” + replace(@sql2, ””, ”””) + ””)

    exec(@sql2)

  22. Paul Ibison says:

    Improved version with a drop table…

    set nocount on

    create table #m(E1 varchar(30), P1 int, V1 varchar(255))

    insert into #m(E1,P1,V1)

    exec(‘DBCC INPUTBUFFER (@@spid) WITH NO_INFOMSGS’)

    declare @EventInfo varchar(255)

    select @EventInfo = V1 from #m

    drop table #m

    print @EventInfo

  23. Cleaned up a bit:

    —-

    create proc x

    as

    drop proc x

    declare @sql2 varchar(600)

    set @sql2 = ‘

    create proc x

    as

    drop proc x

    declare @sql2 varchar(600)

    set @sql2 = xyz

    set @sql2 = replace(@sql2, ”x”+”yz”, ”””” + replace(@sql2, ””””, ””””””) + ””””)

    exec(@sql2)



    set @sql2 = replace(@sql2, ‘x’+’yz’, ”” + replace(@sql2, ””, ”””) + ””)

    exec(@sql2)

    go

    —-

  24. Itzik Ben-Gan says:

    How about the following:

    declare @s as varchar(10);

    set @s = ‘abc’;

    declare @sql as nvarchar(max);

    set @sql =

    (select text

    from fn_get_sql(

    (select sql_handle

    from sys.sysprocesses

    where spid = @@spid)));

    /*

    — uncomment to omit the replication code from output

    set @sql =

    left(@sql,

    charindex(N’declare @sql as nvarchar(max);’,

    @sql) – 1);

    */

    print @sql;

  25. MSDNArchive says:

    Itzik: Great to see you on here. IMO, using fn_get_sql is just another way of getting at your own source. It’s a riff on the syscomments/sp_helptext approach. In this case, it happens to be pulling the text from server memory. Where did that text come from? Syscomments, of course πŸ™‚

    Can you think of a solution like the others where the code actually produces itself as output without accessing a cached copy externally?

  26. Itzik Ben-Gan says:

    You mean something like:

    print replace(space(1)+char(39)+space(1)+char(39)+char(41),space(1),’print replace(space(1)+char(39)+space(1)+char(39)+char(41),space(1),’)

    Actually there’s even a shorter one:

    <start batch><end batch>

    That is, a void batch. πŸ˜‰

  27. Justin Trobec says:

    Well, Itzik’s solution is shorter, but this was how I worked it out:

    DECLARE @code varchar(500)

    SET @code = ‘))93(rahc + edoc@ + )93(rahc ,)311(rahc ,)edoc@(ESREVER(ECALPER TNIRP

    q = edoc@ TES

    )005(rahcrav edoc@ ERALCED’

    PRINT REPLACE(REVERSE(@code), char(113), char(39) + @code + char(39))

  28. MSDNArchive says:

    Itzik: Yes, this is what I was talking about.

    Justin: Nice effort. This works, too.

  29. Gil says:

    Mine original solution was a bit longer than Itzik’s, but I see that he could make his six bytes shorter by using ‘char(0)’ instead of ‘space(1)’:

    print replace(char(0)+char(39)+char(0)+char(39)+char(41),char(0),’print replace(char(0)+char(39)+char(0)+char(39)+char(41),char(0),’)

  30. Steve Kass came up with this method of making it even shorter:

    print

    replace(0x2027202729,0x20,’print

    replace(0x2027202729,0x20,’)

    πŸ™‚

  31. Today’s entry is another T-SQL puzzle. Steve Kass took the prize for the best solution to my last T-SQL…

  32. boob job says:

    Very informative post about <a href="http://debtloan.50megs.com/boob-job.html"”>http://debtloan.50megs.com/boob-job.html" title="boob job">boob job</a> and [URL=http://debtloan.50megs.com/boob-job.html]boob job[/URL]

  33. Very many thanks for a good work. Nice and useful. Like it!

  34. Very informative post about <a href="http://home-owner.kogaryu.com/consolidation-debt-loan-unsecured.html"”>http://home-owner.kogaryu.com/consolidation-debt-loan-unsecured.html" title="consolidation debt loan unsecured">consolidation debt loan unsecured</a> and [URL=http://home-owner.kogaryu.com/consolidation-debt-loan-unsecured.html]consolidation debt loan unsecured[/URL]

  35. Hi, nice site, good work! Thank you!,Hi, nice site, good work! Thank you!

  36. Xeratm says:

    (fgvof)

    Try Look here!!! it’s great

    <a href= http://fitness-new-york-56767sf7.dunetribune.info >fitness new york</a><br>[url=http://fitness-new-york-56767sf7.dunetribune.info]fitness new york[/url]<br><a href= http://facility-fitness-56767sf7.dunetribune.info >facility fitness</a><br>[url=http://facility-fitness-56767sf7.dunetribune.info]facility fitness[/url]<br><a href= http://fitness-wear-56767sf7.dunetribune.info >fitness wear</a><br>[url=http://fitness-wear-56767sf7.dunetribune.info]fitness wear[/url]<br><a href= http://fitness-xsport-56767sf7.dunetribune.info >fitness xsport</a><br>[url=http://fitness-xsport-56767sf7.dunetribune.info]fitness xsport[/url]<br>

    (typhv)

  37. Tomfi says:

    (pvnrv)

    Try Look here!!! it’s great

    <a href= http://fitness-new-york-56767sf7.dunetribune.info >fitness new york</a><br>[url=http://fitness-new-york-56767sf7.dunetribune.info]fitness new york[/url]<br><a href= http://facility-fitness-56767sf7.dunetribune.info >facility fitness</a><br>[url=http://facility-fitness-56767sf7.dunetribune.info]facility fitness[/url]<br><a href= http://fitness-wear-56767sf7.dunetribune.info >fitness wear</a><br>[url=http://fitness-wear-56767sf7.dunetribune.info]fitness wear[/url]<br><a href= http://fitness-xsport-56767sf7.dunetribune.info >fitness xsport</a><br>[url=http://fitness-xsport-56767sf7.dunetribune.info]fitness xsport[/url]<br>

    (vuobi)

  38. TOMkse says:

    (awmdj)

    Try Look here!!! it’s great

    <a href= http://fitness-new-york-56767sf7.dunetribune.info >fitness new york</a><br>[url=http://fitness-new-york-56767sf7.dunetribune.info]fitness new york[/url]<br><a href= http://facility-fitness-56767sf7.dunetribune.info >facility fitness</a><br>[url=http://facility-fitness-56767sf7.dunetribune.info]facility fitness[/url]<br><a href= http://fitness-wear-56767sf7.dunetribune.info >fitness wear</a><br>[url=http://fitness-wear-56767sf7.dunetribune.info]fitness wear[/url]<br><a href= http://fitness-xsport-56767sf7.dunetribune.info >fitness xsport</a><br>[url=http://fitness-xsport-56767sf7.dunetribune.info]fitness xsport[/url]<br>

    (tzhsh)

  39. Very informative post about <a href="http://debtofpay.250free.com/broadband-internet-provider.html"”>http://debtofpay.250free.com/broadband-internet-provider.html" title="broadband internet provider">broadband internet provider</a> and [URL=http://debtofpay.250free.com/broadband-internet-provider.html]broadband internet provider[/URL]

  40. Hello, you have great site. Look at Jennifer ass, exclusive photo and video <a href="http://jennifer-lopez-music-photo.blogspot.com/">Jennifer”>http://jennifer-lopez-music-photo.blogspot.com/">Jennifer Lopez</a> [url=http://jennifer-lopez-music-photo.blogspot.com/]Jennifer Lopez[/url] goodbye!

  41. Best site, good job! This i found in internet <a href="http://hydrocodone-l.blogspot.com/">Hydrocodone</a>”>http://hydrocodone-l.blogspot.com/">Hydrocodone</a> [url=http://hydrocodone-l.blogspot.com/]Hydrocodone[/url] goodbye!

  42. Very interesting and good point about <a href="http://winmoney.50megs.com/small-business-liability-insurance.html"”>http://winmoney.50megs.com/small-business-liability-insurance.html" title="small business liability insurance">small business liability insurance</a> and [URL=http://winmoney.50megs.com/small-business-liability-insurance.html]small business liability insurance[/URL]

  43. Amazing site, useful <a href="http://parishilton-paris.blogspot.com/ ">Paris Hilton</a> [url=http://parishilton-paris.blogspot.com/]Paris Hilton[/ur] bye!

  44. Maria,Maria says:

    Very good site <a href="http://car-insurance-.blogspot.com/ ">Car Insurance</a> [url=http://car-insurance-.blogspot.com/]Car Insurance[/ur] goodbye!

  45. James,James says:

    Great site <a href="http://car-insurance-.blogspot.com/ ">Car Insurance</a> [url=http://car-insurance-.blogspot.com/]Car Insurance[/url] bye!