One of my pet peeves with Microsoft’s SQL Server is that it requires you to know the name of constraints and indexes that you’re dropping. This isn’t a problem when you’re working with the management studio, because it looks at the database metadata and generates code like
ALTER TABLE [dbo].[someTable] DROP CONSTRAINT [PK__someTabl__3214EC07271AA44F]
It’s less convenient, however, when you’re writing a set of migration scripts in SQL to implement changes that you make over the database in time. Specifically, if you create the table twice in two different databases, the hexadecimal string in the name of the key will be different — the ALTER TABLE statement will fail when you try to drop the index later, since the name of the key won’t match.
Here’s a stored procedure that looks up the name of the primary key in the system catalog and uses dynamic SQL to drop the index:
CREATE PROCEDURE [dbo].[DropPrimaryKey] @tableName Varchar(255) AS /* Drop the primary key on @TableName http://gen5.info/q/ Version 1.1 June 9, 2008 */ DECLARE @pkName Varchar(255) SET @pkName= ( SELECT [name] FROM sysobjects WHERE [xtype] = 'PK' AND [parent_obj] = OBJECT_ID(N'[dbo].['+@tableName+N']') ) DECLARE @dropSql varchar(4000) SET @dropSql= 'ALTER TABLE [dbo].['+@tableName+'] DROP CONSTRAINT ['+@PkName+']' EXEC(@dropSql) GO
Once you've loaded this stored procedure, you can write
EXEC [dbo].[DropPrimaryKey] @TableName='someTable'
It’s just that simple. Similar stored procedures can be written to convert fields from NOT NULL to NULL and do other operation which required a named constraint.
Paul Houle on June 6th 2008 in SQL