SQL Server實用技巧集（二）：刪除重複資料、變更物件擁有者

刪除重複資料三法

法一：游標逐組刪除（保留1筆）

DECLARE @max INT, @id INT
DECLARE cur CURSOR LOCAL FOR
  SELECT 主鍵, COUNT(*) FROM 表名 GROUP BY 主鍵 HAVING COUNT(*) > 1
OPEN cur
FETCH cur INTO @id, @max
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @max = @max - 1
  SET ROWCOUNT @max
  DELETE FROM 表名 WHERE 主鍵 = @id
  FETCH cur INTO @id, @max
END
CLOSE cur; SET ROWCOUNT 0

法二：完全重複行（所有欄位相同）

-- 查詢去重結果
SELECT DISTINCT * FROM tableName

-- 實際刪除重複（保留1筆）
SELECT DISTINCT * INTO #Tmp FROM tableName
DROP TABLE tableName
SELECT * INTO tableName FROM #Tmp
DROP TABLE #Tmp
-- 根因：表缺唯一索引，補加即可防重

法三：關鍵欄位重複（如Name、Address），保留第一筆

SELECT IDENTITY(INT,1,1) AS autoID, * INTO #Tmp FROM tableName
SELECT MIN(autoID) AS autoID INTO #Tmp2 FROM #Tmp GROUP BY Name, Address
SELECT * FROM #Tmp WHERE autoID IN (SELECT autoID FROM #Tmp2)
-- 最後SELECT即得去重結果集（含autoID，正式查詢可在選取清單排除）

變更資料表/物件擁有者（Owner）

單一表

EXEC sp_changeobjectowner 'tablename', 'dbo'

批次變更所有表/檢視/觸發器/預存程序

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
  @OldOwner NVARCHAR(128), @NewOwner NVARCHAR(128)
AS
DECLARE @Name NVARCHAR(128), @Owner NVARCHAR(128), @OwnerName NVARCHAR(128)
DECLARE cur CURSOR FOR
  SELECT name FROM sysobjects WHERE uid = USER_ID(@OldOwner)
OPEN cur
FETCH cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @OwnerName = @OldOwner + '.' + @Name
  EXEC sp_changeobjectowner @OwnerName, @NewOwner
  FETCH cur INTO @Name
END
CLOSE cur; DEALLOCATE cur

適用於資料庫還原至異機導致Owner不匹配、無法存取物件的情境。