Find & Delete Duplicate Records on All Tables in current MSSQL Database

You started out with all your keywords being capitalized and then you abandoned your capitalization haphazardly throughout the script, this makes it a little hard to read because your brain says that keywords are capitalized and then all of a sudden you see a begin and your brain says,

"hold up a second, no that's a keyword, never mind"

make sure that all your keywords are capitalized.

Here is a good example

    if @columnname != '['+@pkColumnName+']'
    BEGIN
        if @columns != ' '
        begin
            set @columns = @columns + ', '
        END 
        set @columns = @columns + 't.'+@ColumnName
    END 

This also brings up something else that can really go either way, but I will explain my reasoning.

Your if statements should be indented, and this is how I would have written it,

IF @columnname != '['+@pkColumnName+']'
    BEGIN
        IF @columns != ' '
            BEGIN
                SET @columns = @columns + ', '
            END 
        SET @columns = @columns + 't.'+@ColumnName
    END

I know that is a lot of indentation for an if statement nested inside of an if statement, but I always want to make certain I know exactly what my code is doing, especially when writing SQL Stored Procedures that are going to be creating DELETE scripts.

I may also consider doing it this way to save on indentation

IF @columnname != '['+@pkColumnName+']'
BEGIN
    IF @columns != ' '
    BEGIN
        SET @columns = @columns + ', '
    END 
    SET @columns = @columns + 't.'+@ColumnName
END

I also indent the SELECT statements a little differently as well, and if I recall correctly, I think that many other SQL writers do it the same way that I do.

I would take this,

SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
    AND TABLE_NAME  = PARSENAME(@TableName, 1)
    AND QUOTENAME(COLUMN_NAME) > @ColumnName
    AND COLUMN_NAME NOT in (SELECT columnname FROM @@DiscludeColumns)

and indent everything after the SELECT like this,

SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
        AND TABLE_NAME = PARSENAME(@TableName, 1)
        AND QUOTENAME(COLUMN_NAME) > @ColumnName
        AND COLUMN_NAME NOT IN (SELECT columnname 
                                    FROM @@DiscludeColumns)

And the last and most important thing that I could advise is that you put intentional commenting on the DELETE portion of the script and uncomment the SELECT portion of the rendered script, so that you always run a SELECT to see what you are planning on deleting, before you delete it.

Aside from Malachi's remarks about capitalization of keywords and indentation of the code, I would add two points about variable naming:

• DON'T use variable names that start with @@, as they are reserved for T-SQL functions. This would now give a conflict between the parameter @DiscludeTables and the private table variable @@DiscludeTables, but I would suggest renaming the parameter to @tablesToDisclude.

• DO be consistent in capitalization of variable names. It is customary to use camelCasing, so change @TableName to @tableName, for instance. I would do the same for parameter names.

Some remarks about clean habits in SQL code:

• Use nvarchar when passing strings that are meant to reference dabatase object names. (Actually, it's a good idea to consider nvarchar for most values that will contain user-entered data which could easily need to include unicode characters, but that is outside the scope of this script).

• Use semicolons to end SQL statements. In your script you add a semicolon before the WITH for the CTE, because it needs to be separated from the statement preceding it. Always using semicolons keeps the code sane for future versions.

• You don't need to check whether something IS NULL if you are also comparing it against something else. For instance: if @OUTPUT IS NOT NULL AND @OUTPUT != '' could be written IF @OUTPUT != '', because if @OUTPUT is NULL, @OUTPUT compared to anything will always be false.

• IS NOT IN will only work for single column comparisons. When comparing two sets of data against each other, prefer to do a LEFT JOIN and check whether the right side IS NULL:

   LEFT JOIN @@DiscludeColumns dc
          ON dc.columnName = c.COLUMN_NAME
  WHERE dc.columnName IS NULL
  

  will yield the same result, and is extensible to more columns (if you would, for instance, exclude a column only if both column name AND table name are equal).

• Write a simple JOIN as in INNER JOIN explicitly. This may be a matter of taste, but once you go using multiple LEFT and RIGHT JOINs, this makes the INNER stand out.

Now, on to specific things in this script. In the beginning of the stored procedure, you have some duplicate code. Splitting the incoming parameters into multiple values. It is not always easy to remove duplicated code in SQL, but in this case, a table valued function makes most sense:

CREATE FUNCTION [dbo].[fn_splitString] (@stringToSplit nvarchar(max), @separator nchar(1))
RETURNS @output TABLE (i int identity(0,1), s nvarchar(max))
AS
BEGIN
    WHILE CHARINDEX(@separator, @stringToSplit) > 0
    BEGIN
        DECLARE @pos INT;
        SELECT @pos = CHARINDEX(@separator, @stringToSplit);

        INSERT INTO @output(s) SELECT SUBSTRING(@stringToSplit, 1, @pos-1);

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos);
    END
    IF @stringToSplit != ''
    BEGIN
        INSERT INTO @output SELECT @stringToSplit;
    END
    RETURN
END
GO

This function will take two inputs, the @stringToSplit, which you already use, and the @separator, for which you'd just enter the ';'. This function returns a table with two columns, namely i, which is an identity column, in case you would like to remember the order in which the strings were given, and s, the strings themselves. The rest of the function is actually quite unchanged from the code you used, except that it stores the results in @output instead of e.g. @@DiscludeTables. You would call it in the stored procedure as such:

INSERT @@DiscludeTables (tableName)
    SELECT s FROM [dbo].[fn_splitString] (@DiscludeTables, N';');
INSERT @@DiscludeColumns (columnName)
    SELECT s FROM [dbo].[fn_splitString] (@DiscludeColumns, N';');

The last thing I would advise you, is to avoid loops if possible in SQL code, and use set based operations such as queries with joins. SQL Server was made to do set based operations, and you should take advantage of that.

For instance, instead of querying the INFORMATION_SCHEMA for each table to get the name, then querying it again to get the primary key column (only one?) and then repeatedly querying it to get the names of all the columns that you don't want to ignore, consider a larger query that takes all that information for all tables at once:

DECLARE @COLUMN_INFORMATION TABLE (tableName sysname, columnName sysname, isPrimaryKey bit, primary key (tableName, columnName));

INSERT @COLUMN_INFORMATION (tableName, columnName, isPrimaryKey)
SELECT [tableName] = (QUOTENAME(t.TABLE_SCHEMA) + N'.' + QUOTENAME(t.TABLE_NAME))
     , [columnName] = c.COLUMN_NAME
     , [isPrimaryKey] = CASE WHEN tc.TABLE_NAME IS NULL THEN 0 ELSE 1 END 
  FROM INFORMATION_SCHEMA.TABLES t
 INNER JOIN INFORMATION_SCHEMA.COLUMNS c
         ON c.TABLE_NAME = t.TABLE_NAME
        AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
      INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
              ON tc.CONSTRAINT_NAME = ccu.Constraint_name
         ON tc.CONSTRAINT_TYPE = 'Primary Key'
        AND tc.TABLE_NAME = t.TABLE_NAME
        AND tc.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND c.COLUMN_NAME = ccu.COLUMN_NAME
  LEFT JOIN @@DiscludeTables dt
         ON dt.tableName = t.TABLE_NAME
  LEFT JOIN @@DiscludeColumns dc
         ON dc.columnName = c.COLUMN_NAME
 WHERE t.TABLE_TYPE = 'BASE TABLE'
   AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(t.TABLE_SCHEMA) + N'.' + QUOTENAME(t.TABLE_NAME) ) , 'IsMSShipped' ) = 0
   AND dt.tableName IS NULL
   AND dc.columnName IS NULL;

(This is not the most usual indentation style for SQL, by the way, so ignore it if it's not your taste).

This will return a table containing all non-ignored table names, combined with all non-ignored (and non-primary-key) column names and a bit that says whether that column is a primary key.

Now we have all the information, we can group the information per table. We want only one script for each table, so we need to combine the column names, which is something that SQL does not yet know how to do (there is no CONCAT function that would work like a MAX or SUM). In T-SQL, we can use FOR XML PATH though, to simulate that behavior:

DECLARE @GROUPED_COLUMN_INFORMATION TABLE (tableName sysname primary key, columns nvarchar(max), primaryColumns nvarchar(max), primaryColumnsCompare nvarchar(max));

INSERT @GROUPED_COLUMN_INFORMATION (tableName, columns, primaryColumns, primaryColumnsCompare)
   SELECT [tableName] = ci.tableName
        , [columns] =
              ( STUFF((SELECT N', t.' + columnName 
                         FROM @COLUMN_INFORMATION c2
                        WHERE ci.tableName = c2.tableName
                          AND c2.isPrimaryKey = 0
                          FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
              )
        , [primaryColumns] =
              ( STUFF((SELECT N', t.' + columnName 
                        FROM @COLUMN_INFORMATION c2
                       WHERE ci.tableName = c2.tableName
                         AND c2.isPrimaryKey = 1
                         FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
              )
        , [primaryColumnsCompare] =
              ( N'ON ' + STUFF((SELECT N' AND t.' + columnName + N' = cte.' + columnName
                                  FROM @COLUMN_INFORMATION c2
                                 WHERE ci.tableName = c2.tableName
                                   AND c2.isPrimaryKey = 1
                                   FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 5, N'')
               )
     FROM @COLUMN_INFORMATION ci
    GROUP BY ci.tableName;

While grouping the information, we also prepare some of the strings that we need in the script to output. For instance, in [columns], we combine all the column names from the @COLUMN_INFORMATION table that are not a primary key, and we join them with the text ', t.', so that they are separated by commas and show up as t.columnName. The STUFF(nvarchar, first, last, N'') function makes sure that positions first through last are replaced with the empty string (N''). This gets rid of the comma in the beginning.

In [primaryColumnsCompare], all primary columns are listed twice, showing up as AND t.primaryColumn = cte.primaryColumn. STUFF removes the inital ' AND ' part for us.

Finally, now we have combined all information into a table with one entry per table to check, we generate the script:

SELECT @output = '';
SELECT @output += '
    ;WITH CTE_META AS (
          SELECT [TableName] = ' + ''''+ gci.[tablename] +''', t.*
               , [Rank] = ROW_NUMBER() OVER(
                              PARTITION BY ' + gci.[columns] + ' 
                              ORDER BY ' + gci.[primaryColumns] + '
                           ) FROM ' + gci.[tablename] + ' t
          ) 
          SELECT cte.*
          --DELETE t
            FROM ' + gci.[tablename] + ' t
           INNER JOIN CTE_META cte ' + gci.[primaryColumnsCompare] + '
           WHERE cte.Rank > 1;
    '
  FROM @GROUPED_COLUMN_INFORMATION gci;
IF @output != ''
BEGIN
    SELECT @output = 'USE [' + db_name() + ']' + @output;
    PRINT @output;
END

(In this case, I didn't bother with the FOR XML PATH, because we are summing up one single string. Although the behavior of SELECT @output += '...' is not guaranteed to work with future versions of SQL Server, so you may want to do an extra step to combine it using a FOR XML PATH.)

Also, the output script changed a bit: the DELETE is commented out in favor of the SELECT to verify which data you are deleting. And also here, the IS IN has been replaced by a simple INNER JOIN.