Just chatting yesterday about quick wins in code reviews and the subject of de-duping code came up. Even recently I was in a project where two of us implemented the same checking logic at different tiers of the application (replace that with layers if you're into onion architectures). Something that is able to detect clone code would be incredibly useful...... well, it turns out there is a codeplex project: 'Clone Detective' that does just that (thanks to Rupert for putting me onto this). Internally it tokenizes your code, and matches the patterns - allowing you to quickly find repeating sections that are possibly candidates for refactoring even if the local names are different. The tool is available here, and I'd recommend watching the 10 minute video.

I think it does a great job of finding dupe code - it won't pick out small numbers of lines, for example common foreach loops, so is reasonably noise free and it's not looking at the functionality of the code. This does mean if your code differs by one line you may not match the rest of that block - (which makes perfect sense if that one line is consequential). For example - in the following, the block of recognised code stops at 'purple monkey dishwasher' and the remaining 'matching code' is not matched. This isn't by any means a criticism - I still think this is still a very cool tool.

A documented dependancy on conQAT means you will need to install a Java Virtual machine and configure the Java Home Directory - currently my install is looking at 'C:\Program Files\Java\jdk1.6.0_07'. It also appears to reset your DevEnv settings - so definately worth exporting and re-importing them after. It also has a great add-in which looks exactly like Solution explorer, but shows the number of dupes in each file. As they might say in some camps: 'Great Job' and 'Im super excited to be able to use this' ;)

 I recently had an issue where I had a general search procedure which took a comma delimited list of search terms, and I wanted to data mine the combination of the parameter values called in production, to establish things such as most popular searches and poorly performing search options. Itzik BenGan has already written about grouping proc and query calls, but I needed to group the param names and values. The following regEx string allowed me to extract the parameter names and values as an xml fragment from a profiler trace. This can then be cast to xml in Sql 2005 and shredded into a table using XPath that, if you want, you could mine for free using the table analysis feature in the cloud mining project: http://www.sqlserverdatamining.com/cloud/

@(?'Param'[a-z0-9A-Z_]+) = (?:(?:'(?'Value'[^']*?)')|(?'Value'[^']+?))(?:,|$)

Many thanks to Baron Von Garfoot (Rob Garfoot -www.garfoot.com/blog) and Commander B Benbrook (aka Rubert Benbrook - http://phazed.com), who, with impish glee, led me through getting to grips with RegEx using http://www.radsoftware.com.au/regexdesigner/

If you need a snippet to start using it, this will get you out of the gate until I can write something a little sharper that doesn't rely on string concatenation to build an xml fragment.

 private static string spParams(string inputString, string ElementName)

    {

        string patternMatch = "@(?'Param'[a-z0-9A-Z_]+) = (?:(?:'(?'Value'[^']*?)')|(?'Value'[^']+?))(?:,|$)";

        Match m = Regex.Match(inputString, patternMatch);

        StringBuilder sb = new StringBuilder(100);

        sb.Append("<");

        sb.Append(ElementName);

        while (m.Groups.Count != 1)

        {

            string paramName = m.Groups[1].Value;

            string paramValue = m.Groups[2].Value;

            paramValue = paramValue.Replace("&", @"&amp;");

            paramValue = paramValue.Replace("%", @"&#37;");

            paramValue = paramValue.Replace("<", @"&lt;");

            paramValue = paramValue.Replace(">", @"&gt;");

            string paramString = string.Format(" {0}=\"{1}\" ", paramName, paramValue);

            sb.Append(paramString);

            m = m.NextMatch();

        }

        sb.Append("/>");

        return sb.ToString();

    }

Wow - time flies, I really can't beleive its been 3 weeks since I last looked at this blog. To follow up on some of the feedback I got, here is a summarey of the post I was discussing. Its a re-hash of the actual posting from OJ, but it should give you some idea.

My problem was that any stored proc that uses temp tables wouldn't return a column list when queried from a linked server, which in turn might be used as part of a view. In the following example we have to turn that behaviour of by calling set fmtonly on first. Also, the sp can return as many resultsets it wants. However, only the first will be accepted and redirected by the view. The example shows both of these facts in 'action'.
 
e.g.
 
use tempdb
go
create proc usp
as
set nocount on
create table #tmp1(OrderID int, CustomerID sysname)
create table #tmp2(EmployeeID int, Freight money)
 
insert #tmp1
select top 1 OrderID,CustomerID
from Northwind..Orders
 
insert #tmp2
select top 1 EmployeeID,Freight
from Northwind..Orders
 
select * from #tmp1
select * from #tmp2
go
 
use Northwind
go
create view _v
as
select *
from openquery(myserver,'set fmtonly off; exec tempdb..usp')x
go
select * from _v
go
 
drop view _v
go
exec tempdb..sp_executesql N'drop proc usp'
go
 

Openquery() opens a new connection for distributed query based on the current_user authentication/profile. By default, every login's default database is master (unless you over-ride it). Thus, the distributed connection will work under master database context. If you want to invoke an user-object (i.e. view/sp/udf/etc) from master
database, you will have to qualify the database. Note that system objects (stored procedures, functions, etc.) can be called from any database, hence you wouldn't have to qualify them.

Thanks to all

Ryan