Michael J. Swart

September 10, 2008

Self replicating SQL

Filed under: Tongue In Cheek — Michael J. Swart @ 12:22 pm

While thinking about self replicating programs (See Quine). It became natural to think about self replicating T-SQL, and not just that, but the shortest self-replicating SQL out there.

And naturally, some-one beat me too it. (Ken Henderson wrote about it here.)

Ken mentioned that there seemed to be two flavors of submissions:

Those that tried to cheat by accessing a cached copy of the source code somewhere external to the code (e.g., from syscomments) and those that genuinely tried to replicate themselves

Now I wouldn’t call it cheating, but in the end, it’s his blog. Here’s what I came up with myself along those lines:

FROM sys.dm_exec_cached_plans p 
CROSS apply sys.dm_exec_sql_text(p.plan_handle) s 


DBCC inputbuffer(@@spid)

Although this last example doesn’t quite work out.

There are two more solutions that I like that don’t fall into either of Ken’s categories. One was actually mentioned in the comments of his post, and that’s the null batch. Executing nothing will print nothing. This was submitted by Itzik Ben Gan (not a surprise). This is clearly the winner when it comes to shortest self-replicating T-SQL script.

And then there’s this one I came up with. It seems like an abuse of the rules, but that’s why I like it.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '15'.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress