Find duplicates in a table - SSMS Tools to the rescue

The other day one of my co-workers was asking if there is a quick way to insert a SQL query to find duplicates in a given column in Microsoft SQL Server Management Studio (SSMS) similar to how you can insert SQL scripts, i.e. “Select Top 1000 Rows”, “Edit top 200 Rows” etc., when you right-click on a table name.

You could sort of do this using Template Explorer feature in SSMS, but it is not very user-friendly. A while back I have installed an add-in for SSMS called SSMS Tools Pack to help me organize length SQL Scripts into regions and sub-regions. This tool is very useful and I have yet to capitalize on all of the features that it offers.

This tool has a feature called Custom Scripts and it is very powerful.
Using the Custom Scripts feature, I have created a template that looks like this to quickly find duplicate values under a given column:

SELECT |ObjectName|, COUNT(*)
FROM |SchemaName|.|ParentTableOrViewName|
GROUP BY |ObjectName|
HAVING COUNT(*) > 1

The tokens between the pipes are automatically inserted by this tool when you instantiate the template from a context-menu off of the selected object. For example, I would right-click on column Title in a table called Courses and select the template "Find duplicates" from the SSMS Tools context menu. It will open a new Query window with the following script embedded:

SELECT Title, COUNT(*)
FROM dbo.Courses
GROUP BY Title
HAVING COUNT(*) > 1

You simply run the script to find the duplicates. As you can see this is a very useful feature and the next step is to come up with custom scripts that we find ourselves repeating them often and turn them into templates using SSMS Tools Pack.

Hope you find this tool useful and come up with some creative custom scripts that you could share them with your blog readers online.

Happy Coding,
Sonny