Query for Duplicate records

Repeat | duplicate record
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_qry] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_qry]
Go

/*--Common stored procedures for querying duplicate records

You can query that the data in the table is duplicated, and here is the repetition, which refers to a duplicate record except the primary key.
If there is a primary key in the table, specify the primary key.
If there are marked segments in the table and there are no duplicates for the Identity field, specify the primary key as the standard literacy section when called
If the identity field repeats, you cannot use this stored procedure

--2004.4--*/

Create proc P_qry
@tbname sysname--Name of the table to query
@keyfdname Sysname=null-primary key in table, no primary key in table if unspecified
As
Declare @nokey bit, @fd varchar (8000), @tj varchar (8000)
SET NOCOUNT ON
If IsNull (@keyfdname, ') = '
Begin
Select @keyfdname =cast (NEWID () as char), @nokey =1
EXEC (' ALTER TABLE [' + @tbname + '] add [' + @keyfdname + '] Decimal (38,0) identity (1,1) ')
End
Select @fd = ', @tj = '
Select @fd = @fd + ', [' +name+ '] '
, @tj = @tj + ' [' +name+ ']=a.[' +name+ '] and '
From syscolumns
where object_name (id) = @tbname and name<> @keyfdname
Set @fd =substring (@fd, 2,8000)
EXEC (' select ' + @fd + ' from [' + @tbname + '] A
where exists (select 1 from [' + @tbname
+ '] where a.[' + @keyfdname + ']) ' > ' + @tj + ' [' + @keyfdname + ']<>a.[' + @keyfdname + '])
If @nokey =1
EXEC (' ALTER TABLE [' + @tbname + '] drop column [' + @keyfdname + '] ')
SET NOCOUNT OFF
Go

--Invoke the example
--Create test data
CREATE TABLE Table (F1 int,f2 int,f3 int,f4 int,f5 int)
INSERT INTO table
Select 1,1,1,1,1
UNION ALL Select 2,1,1,1,1
UNION ALL Select 3,2,1,23,1
UNION ALL Select 4,2,3,1,3
UNION ALL Select 5,1,1,1,1
Go

--Call the common stored procedure to realize the landlord's query
EXEC p_qry ' table ', ' F1 '

--Delete test environment
drop TABLE Table


/*--test Results

F2 F3 f4 f5
----------- ----------- ----------- -----------
1 1 1 1
1 1 1 1
1 1 1 1
--*/