Click here to Skip to main content
15,112,866 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick
Posted 13 Dec 2014

Tagged as

Stats

20.7K views
12 bookmarked

SQL Server Procedure to Report Duplicates

Rate me:
Please Sign up or sign in to vote.
5.00/5 (10 votes)
13 Dec 2014CPOL2 min read
A stored procedure I use to find duplicate records in tables and views.

Introduction

This stored procedure finds duplicate records in tables and views. It is a tool I use frequently; whenever I hear rumors of duplicates being reported by the systems I support. It has evolved to the current state over time, through real-world use on production databases.

Background

What constitutes a "duplicate" in any particular circumstance depends on the data and the needs of the application. For the purpose of this tip, I'll consider records with the same value for some key field (or fields) to be duplicates, even if not all the fields are the same. For example, consider the following simple table:

ID Name Race
1 Bob Alien
1 Bob Alien
1 Bob Martian
1 Robert Alien
  • All four records match on ID.
  • The first three records match on ID and Name.
  • The first two records are the only two that match exactly on all three fields.

No one likes having to track down duplicates. Remembering how to write an SQL query to do it and then writing one for each different table or view that you need to investigate can be a burden, and if "production is down!" and every minute spent investigating costs money, then you will be grateful to have a tool handy.

One other point I'd like to make is that a query that simply provides a list of duplicate IDs isn't generally as helpful as a query that provides the entire records that match the duplicate criteria.

FindDuplicates

Such a tool should be flexible enough to work with pretty much any table or view you throw at it. This procedure accepts parameters to specify the table or view to query and which column (or columns) to use as a key.

SQL
EXECUTE dbo.FindDuplicates 'dbo' , 'Mascot' , 'ID'
EXECUTE dbo.FindDuplicates 'dbo' , 'Mascot' , 'Name'
EXECUTE dbo.FindDuplicates 'dbo' , 'Mascot' , 'CAST(ID AS VARCHAR(50)) + Name'
EXECUTE dbo.FindDuplicates 'dbo' , 'Mascot' , 'CAST(ID AS VARCHAR(50)) + Name + Race'

As this is a tool for developers and DBAs to use, I chose to accomplish this with dynamic SQL.

SQL
CREATE PROCEDURE [dbo].[FindDuplicates]
  @schemaname  VARCHAR(128)
, @tablename   VARCHAR(128)
, @columnname  VARCHAR(128)
, @whereclause VARCHAR(128)='0=0'
AS
BEGIN
  DECLARE @sql VARCHAR(MAX)
  SET @sql=
  '
  WITH cte AS ( SELECT __columnname__ __KEY__ , * FROM __schemaname__.__tablename__ WHERE __where_clause__ )
  SELECT B.HowMany , A.* 
  FROM cte A
  INNER JOIN 
  ( SELECT __KEY__ , COUNT(*) HowMany FROM cte GROUP BY __KEY__ ) B
  ON A.__KEY__=B.__KEY__
  WHERE B.HowMany>1
  ORDER BY A.__KEY__
  '
 
  SET @sql=REPLACE( @sql , '__schemaname__'   , @schemaname  )
  SET @sql=REPLACE( @sql , '__tablename__'    , @tablename   )
  SET @sql=REPLACE( @sql , '__columnname__'   , @columnname  )
  SET @sql=REPLACE( @sql , '__where_clause__' , @whereclause )
 
--  PRINT @sql 
 
  EXECUTE (@sql)
END

As I said, I have had to use this many times and I'm always glad I have it handy. Please add comments (or alternatives!) with any suggestions of improvements you have.

History

  • 2014-12-13

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

PIEBALDconsult
Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian

---------------

"I would be looking for better tekkies, too. Yours are broken." -- Paul Pedant

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming

Comments and Discussions

 
GeneralThis is great! Pin
Member 129452216-Dec-14 6:55
MemberMember 129452216-Dec-14 6:55 
QuestionAwesome! Pin
andegre16-Dec-14 3:51
Memberandegre16-Dec-14 3:51 
nm
QuestionData Integrity Pin
Member 1068497715-Dec-14 22:38
MemberMember 1068497715-Dec-14 22:38 
GeneralRe: Data Integrity Pin
PIEBALDconsult16-Dec-14 4:57
professionalPIEBALDconsult16-Dec-14 4:57 
GeneralRe: Data Integrity Pin
Member 1068497716-Dec-14 5:01
MemberMember 1068497716-Dec-14 5:01 
GeneralRe: Data Integrity Pin
PIEBALDconsult16-Dec-14 5:09
professionalPIEBALDconsult16-Dec-14 5:09 
QuestionThat is... surprising handy! Pin
Yasskier15-Dec-14 10:45
MemberYasskier15-Dec-14 10:45 
GeneralRe: That is... surprising handy! Pin
PIEBALDconsult15-Dec-14 10:59
professionalPIEBALDconsult15-Dec-14 10:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.