This script
is a stored procedure to check duplicate record of a table or output of a SQL
statement by considering all columns. Download
the script file Duplicate Record
Check.sql. Run it on your database. A procedure named "[DUPLICATE_RECORD_CHECK]” will be
available under the default schema,
i.e [dbo].
This
procedure will return TRUE as output if
duplicate record found, otherwise return FALSE.
Let’s see the parameters to use it…
Parameter Name
|
Type
|
IN/OUT
|
Description
|
@SQLStm
|
VARCHAR(MAX)
|
INPUT
|
Put fully qualified table name,
i.e. [dbo].[TEST], luckily you can use DB name with table name, i.e.
[TEST_DB].[dbo].[TEST].
OR you can pass a SQL statement to check duplicate output, i.e. "select * from [dbo].[TEST]" |
@isTable
|
BIT
|
INPUT
|
Mention @SQLStm contains a
Table name or SQL stm. TRUE for table and FALSE for SQL Stm.
|
@duplicateFound
|
BIT
|
OUTPUT
|
Find the output as TRUE/FALSE. If duplicate found, returns TRUE.
|
An example to
call this procedure …
with a Table Name
|
BEGIN
DECLARE @duplicateFound BIT
EXEC [dbo].[DUPLICATE_RECORD_CHECK]
@SQLStm = N'[dbo].[TEST]'
, @isTable = 1
, @duplicateFound = @duplicateFound OUTPUT
SELECT
@duplicateFound AS N'@duplicateFound'
END
|
with a SQL Statement
|
BEGIN
DECLARE @duplicateFound BIT
EXEC [dbo].[DUPLICATE_RECORD_CHECK]
@SQLStm = N'select 1 as
test_column'
, @isTable = 0
, @duplicateFound = @duplicateFound OUTPUT
SELECT
@duplicateFound AS N'@duplicateFound'
END
|
