Tool for detecting copy/pasted code in TSQL?

Question

I'm working on a SQL Server 2005 system with a lot of copy-pasted code, lots of repeated calculations instead of functions, many stored procedures that differ only very slightly instead of being parameterized. Also lots of SSIS packages that are very similar.

Moving forward, I'd like to be able to to detect this type of "code smell". There's lots of tools around for this, but I haven't found anything for SQL.

Any ideas on products to use, or best practices to apply regarding copy/pasted code in TSQL?

Many thanks...

EDIT: Thanks for all the feedback. I poked around with a couple of them, but the one that looks most straightforward and easy to use appears to be Atomiq. I had to exported all my objects to .sql files, then had to use a batch file to rename all the files with extension .sql to .cs because it doesn't use .sql. However, I've already gotten some good information on major problems with our code.

Answer 1

You have several for that purpose:

• Atomiq - commercial
• Black Duck Suite - commercial (software analyzing suite)
• CloneDR - commercial (Ada, C, C++, C#, Java, COBOL, Fortran, Python, VB.net, VB6, PHP4/5, PLSQL, SQL2011, XML, many others)
• ConQAT (Open Source, supports: ABAP, ADA, Cobol, C/C++, C#, Java, PL/I, PL/SQL, Python, Text, Transact SQL, Visual Basic, XML)
• Simian (software)

Answer 2

You could try to run Simian. It is a tool which supports many languages (including SQL)