Ken Henderson's WebLog

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.

Published Tuesday, October 25, 2005 6:02 AM by khen1234

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

 

mabster said:

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
October 25, 2005 8:37 AM
 

mabster said:

Ah - makes more sense to have "drop procedure GetMe" as the first line of the stored proc.
October 25, 2005 8:37 AM
 

mabster said:

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
October 25, 2005 8:39 AM
 

khen1234 said:

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?
October 25, 2005 9:38 AM
 

Adam Machanic said:

It may be cheating, but here's a shorter version ;)

create proc x
as
exec sp_helptext 'x'
go

October 25, 2005 10:01 AM
 

khen1234 said:

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.
October 25, 2005 10:05 AM
 

Adam Machanic said:

I know :)

Working on that now... -much- harder, to say the least!
October 25, 2005 10:26 AM
 

David Jones said:

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
October 25, 2005 11:57 AM
 

Paul Ibison said:

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)
October 25, 2005 12:40 PM
 

Steve Kass said:

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

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Msg'.
October 25, 2005 1:53 PM
 

khen1234 said:

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!
October 25, 2005 2:33 PM
 

Denis Gobo said:

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)
October 25, 2005 2:33 PM
 

khen1234 said:

David Jones: looks like you nailed it. Nice job!
October 25, 2005 2:33 PM
 

khen1234 said:

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 ;-)
October 25, 2005 2:37 PM
 

khen1234 said:

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.

October 25, 2005 2:41 PM
 

khen1234 said:

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 :-)
October 25, 2005 2:45 PM
 

Paul Ibison said:

Any clues?
October 25, 2005 3:03 PM
 

Steve Kass said:

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)', @=@
October 25, 2005 3:17 PM
 

Steve Kass said:

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)',@
October 25, 2005 4:09 PM
 

khen1234 said:

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?
October 25, 2005 4:16 PM
 

Paul Ibison said:

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
October 25, 2005 5:11 PM
 

Adam Machanic said:

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)
October 25, 2005 5:11 PM
 

Paul Ibison said:

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
October 25, 2005 5:14 PM
 

Adam Machanic said:

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
----
October 25, 2005 5:43 PM
 

Itzik Ben-Gan said:

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;

October 25, 2005 6:03 PM
 

khen1234 said:

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?
October 25, 2005 6:19 PM
 

Itzik Ben-Gan said:

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. ;-)
October 25, 2005 9:32 PM
 

Justin Trobec said:

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))
October 26, 2005 6:14 PM
 

khen1234 said:

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

Justin: Nice effort. This works, too.
October 26, 2005 7:50 PM
 

Gil said:

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),')
October 27, 2005 1:38 AM
 

Adam Machanic said:

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

print
replace(0x2027202729,0x20,'print
replace(0x2027202729,0x20,')

:)
October 27, 2005 9:54 AM
 

Ken Henderson's WebLog said:

Today’s entry is another T-SQL puzzle. Steve Kass took the prize for the best solution to my last T-SQL...
February 5, 2006 1:06 AM
 

Samuel said:

Nice site!
[url=http://xzimaxcr.com/lzgx/txgu.html]My homepage[/url] | [url=http://ybsibsnb.com/fmqe/rddo.html]Cool site[/url]
May 18, 2006 1:58 AM
 

Gloria said:

Great work!
<a href="http://xzimaxcr.com/lzgx/txgu.html">My homepage</a> | <a href="http://hwbhrldc.com/ofek/nawp.html">Please visit</a>
May 18, 2006 1:58 AM
 

Craig said:

May 18, 2006 1:58 AM
 

boob job said:

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]
August 27, 2006 1:30 AM
 

Jeorge Lukasing said:

Very many thanks for a good work. Nice and useful. Like it!
August 27, 2006 4:21 AM
 

consolidation debt loan unsecured said:

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]
August 27, 2006 10:00 AM
 

Robert,Robert said:

Hi, nice site, good work! Thank you!,Hi, nice site, good work! Thank you!
August 29, 2006 4:00 AM
 

Braza said:

August 30, 2006 9:03 AM
 

Brazajyi said:

August 31, 2006 2:17 PM
 

Brazahmy said:

August 31, 2006 2:28 PM
 

Brazacmt said:

August 31, 2006 3:34 PM
 

annual insurance travel said:

Very informative post about <a href="http://nowinsurance.bravehost.com/annual-insurance-travel.html"">http://nowinsurance.bravehost.com/annual-insurance-travel.html" title="annual insurance travel">annual insurance travel</a> and [URL=http://nowinsurance.bravehost.com/annual-insurance-travel.html]annual insurance travel[/URL]
September 4, 2006 4:58 AM
 

interest only mortgage rate said:

Thank you for this great post about <a href="http://eteamz.active.com/yourbusiness/files/interest-only-mortgage-rate.html"">http://eteamz.active.com/yourbusiness/files/interest-only-mortgage-rate.html" title="interest only mortgage rate">interest only mortgage rate</a> and [URL=http://eteamz.active.com/yourbusiness/files/interest-only-mortgage-rate.html]interest only mortgage rate[/URL]
September 5, 2006 2:43 AM
 

Xeratm said:

(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)
September 5, 2006 6:23 PM
 

Tomfi said:

(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)
September 7, 2006 4:06 PM
 

TOMkse said:

(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)
September 7, 2006 4:07 PM
 

broadband internet provider said:

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]
September 8, 2006 12:54 AM
 

musicwoman said:

September 13, 2006 1:23 PM
 

musicwoman said:

September 13, 2006 1:23 PM
 

Jennifer,Jennifer said:

September 14, 2006 11:56 PM
 

replicarolex said:

[URL=http://http://replicarolexwatch.ir.pl]replica-rolex-watch[/URL]<a href="http://http://replicarolexwatch.ir.pl">replica rolex watch</a>
September 15, 2006 2:22 AM
 

William,William said:

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!
September 16, 2006 1:23 PM
 

Steven,Steven said:

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!
September 19, 2006 1:09 PM
 

Frank,Frank said:

September 20, 2006 4:50 AM
 

Garry,Garry said:

Hi, you have great site!
Play online blackjack at <a href="http://hometown.aol.com/shlyapa2/black-jack.html">Black">http://hometown.aol.com/shlyapa2/black-jack.html">Black Jack</a> [url=http://hometown.aol.com/shlyapa2/black-jack.html]Black Jack[/url] bye..
September 23, 2006 4:45 AM
 

Cindy,Cindy said:

September 23, 2006 2:19 PM
 

Nicole,Nicole said:

September 24, 2006 5:55 AM
 

Nicole,Nicole said:

September 24, 2006 5:55 AM
 

Paul,Paul said:

Great site <a href="http://car-insurance-p4zw.blogspot.com">Car">http://car-insurance-p4zw.blogspot.com">Car Insurance</a> [url=http://car-insurance-p4zw.blogspot.com]Car Insurance[/url] best regards Paul.
September 25, 2006 1:30 PM
 

Jean,Jean said:

Good site <a href="http://car-insurance-0.blogspot.com/">Car">http://car-insurance-0.blogspot.com/">Car Insurance</a> [url=http://car-insurance-0.blogspot.com/]Car Insurance[/url] bye.
September 26, 2006 9:11 AM
 

small business liability insurance said:

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]
September 30, 2006 2:27 PM
 

Annabell,Annabell said:

Amazing site, useful <a href="http://parishilton-paris.blogspot.com/ ">Paris Hilton</a> [url=http://parishilton-paris.blogspot.com/]Paris Hilton[/ur] bye!
September 30, 2006 4:12 PM
 

Maria,Maria said:

Very good site <a href="http://car-insurance-.blogspot.com/ ">Car Insurance</a> [url=http://car-insurance-.blogspot.com/]Car Insurance[/ur] goodbye!
October 1, 2006 8:56 AM
 

Amanda,Amanda said:

Good site <a href="http://black-jack-black.blogspot.com/ ">Black Jack</a> [url=http://black-jack-black.blogspot.com/]Black Jack[/url] goodbye!
October 1, 2006 3:11 PM
 

Barry,Barry said:

Good site! <a href="http://mariah-carey-mariah.blogspot.com/">Mariah">http://mariah-carey-mariah.blogspot.com/">Mariah Carey</a> [url=http://mariah-carey-mariah.blogspot.com/]Mariah Carey[/url] goodbye!
October 1, 2006 8:35 PM
 

James,James said:

Great site <a href="http://car-insurance-.blogspot.com/ ">Car Insurance</a> [url=http://car-insurance-.blogspot.com/]Car Insurance[/url] bye!
October 2, 2006 6:16 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker