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:
SELECT TEXT
FROM sys.dm_exec_cached_plans p
CROSS apply sys.dm_exec_sql_text(p.plan_handle) s
WHERE TEXT LIKE '%|%'and
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'.