删除所有主键

Question

这听起来像是一个疯狂的请求。我报告的数据库没有任何外键，每个主键都是一个identity_column。这使得使用TOAD等工具变得困难，因为Intellisense通过阅读PK和FK关系来工作。

任何人都有脚本可以从数据库的每个表中删除主键，这样我就可以用“正确的”PK替换它们并添加FK来协助报告？

为了阻止“不要这样做!!!”的雪崩回答，让我清楚地说明，我不打算将其作为我的生产数据库，而是将其复制到另一台服务器上。

任何意见，将不胜感激。

-------编辑这是用正确的信息更新。 ----------------

谢谢你们，但我意识到我犯了一个错误。几乎每个表都有一个具有身份属性的“identity_column”。该身份是一个聚集索引。但是，它并未被指定为主键。

首先，主键和聚集索引之间有什么区别？

其次，我怎样才能编出所有的聚集索引？ 这项工作？

SELECT 
    'ALTER TABLE ' + OBJECT_NAME(OBJECT_ID) + ' DROP CONSTRAINT ' + name 
FROM sys.indexes WHERE type_desc = 'CLUSTERED' 

感谢您的耐心

Answer 1

如何像this？

[未测试]

-- Helper Procedure 
CREATE PROC#DropConstraints 
    @tableSchema nvarchar(max), 
    @tableName nvarchar(max), 
    @constraintType nvarchar(20) 
AS 
BEGIN 
    DECLARE @cName nvarchar(max); 

    DECLARE constraint_cursor CURSOR FOR 
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE 
     CONSTRAINT_TYPE = @constraintType 
     AND TABLE_NAME = @tableName 
     AND TABLE_SCHEMA = @tableSchema 

    OPEN constraint_cursor 

    FETCH NEXT FROM constraint_cursor INTO @cName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    EXEC ('ALTER TABLE ' + @tableSchema + '.' + @tableName + ' DROP CONSTRAINT ' + @cName); 
    FETCH NEXT FROM constraint_cursor INTO @cName 
    END 

    CLOSE constraint_cursor 
    DEALLOCATE constraint_cursor 
END 
GO 

BEGIN TRANSACTION 

    -- Setup Cursor for looping 
    DECLARE table_cursor SCROLL CURSOR FOR 
    SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 

    OPEN table_cursor 

-- Drop Primary Keys 
    FETCH FIRST FROM table_cursor INTO @tableSchema, @tableName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    EXEC#DropConstraints @tableSchema, @tableName, 'PRIMARY KEY'; 

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName 
    END 

    -- Cleanup 
    CLOSE table_cursor 
    DEALLOCATE table_cursor 

COMMIT TRANSACTION 
GO 

DROP PROCEDURE #DropConstraints; 
GO 

Answer 2

另一种选择是一个两步骤的过程：

1. 第一，选择从系统目录视图必要的信息，并使用它们来构建第t -SQL语句，您需要实际删除索引和约束：

   SELECT 
       'ALTER TABLE ' + OBJECT_NAME(OBJECT_ID) + ' DROP CONSTRAINT ' + name 
   FROM sys.indexes WHERE is_primary_key = 1 
   

2. 使用该结果集，复制&粘贴到一个新的查询窗口，并运行它 - 它会删除所有主键约束，从所有的表中运行该数据库中

这样，你”避免游标，并且你得到一个要执行的语句列表，如果你不需要它，你仍然可以使用“原样”，调整，甚至完全丢弃。

Answer 3

要回答你关于differnce betweeen一个PK和一个聚集索引的问题：

主键是保证记录可以被唯一标识，键值。它们与聚簇索引（指示记录物理存储的顺序）无关，只是创建主键的默认值是将其设置为聚簇索引。但是，您不必将其设置为聚簇索引。

请注意，如果您以前没有主键和外键，则可能会彻底清除您的数据，并且在清理之前不应创建外键。

Answer 4

要删除所有聚簇索引，必须区分约束（主或唯一）是聚簇索引还是非约束索引是聚簇索引的情况。您不能使用DROP INDEX删除约束索引，也不能使用DROP约束删除索引。所以，你需要做的是这样的：

Select 'ALTER TABLE ' + QUOTENAME(OBJECT_NAME([object_id])) + ' DROP CONSTRAINT ' + QUOTENAME([name]) 
From sys.indexes 
Where is_primary_key = 1 Or is_unique_constraint = 1 
And type_desc = 'CLUSTERED' 
Union All 
Select 'DROP INDEX ' + QUOTENAME([name]) + ' ON ' + QUOTENAME(OBJECT_NAME([object_id])) 
from sys.indexes 
Where is_primary_key = 0 And is_unique_constraint = 0 
And type_desc = 'CLUSTERED' 

坦率地说，即使这可能不会工作，因为所有的外键的任何主键将被丢弃，然后才能删除主键。要做到这一点，你想要脚本所有的外键，全部放弃，然后放弃所有的集群约束，然后重新创建所有的外键。我不得不问这是否真的是你想要做的。通过删除所有聚集索引，您将强制重建受影响的所有表中的所有索引。

Answer 5

我之前提供的代码示例的工作原理都是主键唯一版本，还有一个还处理其他类型聚簇索引的代码示例。

但是，没有人关心对象可能不存在于默认方案中的事实，也没有控制SQL Server需要的索引和约束不是系统对象。

这是一个简单的版本，只有删除主键：

select 'ALTER TABLE ' + quotename(object_schema_name(object_id)) + '.' 
     + quotename(object_name(object_id)) + ' DROP CONSTRAINT ' + name 
from sys.indexes 
where is_primary_key = 1 

这里是第二个版本，这也是脚本非主键聚集索引的删除：

select 'ALTER TABLE ' + quotename(object_schema_name(object_id)) + '.' 
     + quotename(object_name([object_id])) + ' DROP CONSTRAINT ' 
     + quotename([name]) 
from sys.indexes 
where is_primary_key = 1 
     or is_unique_constraint = 1 
     and type_desc = 'CLUSTERED' 
union all 
select 'DROP INDEX ' + quotename(i.[name]) + ' ON ' 
     + quotename(object_schema_name(i.[object_id])) + '.' 
     + +quotename(object_name(i.[object_id])) 
from sys.indexes as i 
     inner join sys.objects as o on o.object_id = i.object_id 
where is_primary_key = 0 
     and is_unique_constraint = 0 
     and i.type_desc = 'CLUSTERED' 
     and o.[type] not in ('S') 
     and o.is_ms_shipped = 0