drop primary key
-
How do you drop the primary key on a table using transact sql? The ansi doesn't seem to work:
alter table <table name> drop primary key
.
-
alter table ... drop constraint < your name > should do
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Well, the problem with that is when the table is created, I do a
create table(
TableIdCol...
primaryKey(TableIdCol)
)
So I never know what the primary key will be named at a particiular customer site.
.
-
CREATE PROCEDURE usp_DropColumn
@TableName Varchar(50),
@ColumnName Varchar(50)
AS
/*====================================================================================================
NAME: usp_Dropcolumn CREATED BY: SD
TYPE: stored procedure (SQL 7) CREATION DATE: 08/01/01
USAGE:
DECLARE @TableName Varchar(50)
DECLARE @ColumnName Varchar(50)
SET @TableName = 'MyTable'
SET @ColumnName = 'MyColumn'
Exec usp_DropColumn @TableName, @ColumnName
PURPOSE : To Drop a Column from a Table irrespective of the constraints
defined on it.
Verifications:
Check existence of table
Check existence of column within table
Check if this is the only column in the table
Check existence of Default constraint on the column
Check existence of Multiple Foreign Key constraints on the column
Check existence of a Primary Key constraint on the column
Check existence of Multiple Unique Constraint/Indexs on the column
NOTE : Only Table Owner or a dbo can Alter the Table.
=====================================================================================================*/
/*___________________________________________________________________________________________________*/
SET NOCOUNT ON
/* Declaring Variables */
Declare @Qry1 Varchar(1000)
Declare @Qry2 Varchar(1000)
Declare @Qry3 Varchar(1000)
Declare @Qry4 Varchar(1000)
Declare @Qry5 Varchar(1000)
Declare @Qry6 Varchar(1000)
Declare @DFName Varchar(500)
Declare @FKName Varchar(500)
Declare @PKFKName Varchar(500)
Declare @Tablename2 Varchar(500)
Declare @PKName Varchar(500)
Declare @UixName Varchar(500)
BEGIN TRAN
-- Check Existence of Table.
if exists (select * from sysobjects where id = object_id(@TableName) and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- Check to see that the column already exist
IF (SELECT COLUMNPROPERTY( OBJECT_ID(@TableName),@ColumnName,'AllowsNull'))
IS NOT NULL
Begin
--Check to see if the column count in the table is more than 1.
IF ( SELECT Count(*) FROM syscolumns sc INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.name = @TableName) <= 1
BEGIN
Raiserror('ERROR : %s is the only column in the table %s. It cannot be
dropped',16,1, @ColumnName, @TableName)
Rollback Tran
Return(@@Error)
END
--Check to see if the column has a Default constraint defined on it.
IF Exists ( SELECT syscolumns.* FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN sysobjects so
ON syscolumns.cdefault = so.id
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
AND syscolumns.cdefault <> 0
)
BEGIN
SELECT @DFName = so.name
FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
INNER JOIN sysobjects so ON syscolumns.cdefault = so.id
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
AND syscolumns.cdefault <> 0
SET @Qry1 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @DFName
Print 'Dropping Default constraint ' + @DFName
Exec(@Qry1)
IF @@Error <> 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@DFName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'Default ' + @DFName + ' Dropped from ' + @TableName + ' Table.'
END
--Check to see if the column has a FK constraint defined on it.
IF Exists ( SELECT so.name FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN sysreferences
ON syscolumns.id = sysreferences.fkeyid
INNER JOIN sysobjects so
ON sysreferences.constid = so.id
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
)
BEGIN
DECLARE FK_Cursor CURSOR FOR
SELECT so.name FROM syscolumns INNER JOIN sysobjects ON syscolumns.id =
sysobjects.id
INNER JOIN sysreferences ON syscolumns.id = sysreferences.fkeyid
INNER JOIN sysobjects so ON sysreferences.constid = so.id
WHERE sysobjects.name = @TableName AND syscolumns.name = @ColumnName
OPEN FK_Cursor
FETCH NEXT FROM FK_Cursor
INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Qry2 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @FKName
Print 'Dropping FK constraint ' + @FKName
Exec(@Qry2)
IF @@Error <> 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@FKName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'FK ' + @FKname + ' Dropped from ' + @TableName + ' Table.'
FETCH NEXT FROM FK_Cursor INTO @FKName
END /* WHILE @@FETCH_STATUS = 0 */
CLOSE FK_cursor
DEALLOCATE FK_cursor
END
--Check to see if the column has a PK constraint defined on it.
--This even takes care of Unique Constraints defined on the column.
IF Exists ( SELECT so2.name
FROM sysobjects so1
INNER JOIN sysobjects so2
ON so1.id = so2.Parent_Obj
INNER JOIN sysindexes
ON so2.name = sysindexes.name
INNER JOIN sysindexkeys
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
INNER JOIN syscolumns
ON so1.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
WHERE so1.name = @TableName
AND so2.xtype = 'PK'
AND syscolumns.name = @ColumnName
)
BEGIN
-- Check to see if the PK is being Referenced by a FK
IF Exists ( SELECT so.name
FROM sysreferences
INNER JOIN syscolumns
ON sysreferences.rkeyid = syscolumns.id
INNER join sysobjects
ON sysreferences.rkeyid = sysobjects.id
INNER JOIN sysobjects so
ON sysreferences.constid = so.id
WHERE syscolumns.name = @ColumnName
AND sysobjects.name = @TableName
)
BEGIN
DECLARE PKFK_Cursor CURSOR FOR
SELECT so.name, so2.name
FROM sysreferences INNER JOIN syscolumns
ON sysreferences.rkeyid = syscolumns.id
INNER join sysobjects ON sysreferences.rkeyid = sysobjects.id
INNER JOIN sysobjects so ON sysreferences.constid = so.id
INNER JOIN sysobjects so2
ON sysreferences.fkeyid = so2.id
WHERE syscolumns.name = @ColumnName AND sysobjects.name = @TableName
OPEN PKFK_Cursor
FETCH NEXT FROM PKFK_Cursor
INTO @PKFKName, @TableName2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Qry3 = 'ALTER TABLE ' + @TableName2 + ' DROP Constraint ' +
@PKFKName
Print 'Dropping FK constraint ' + @PKFKName
Exec(@Qry3)
IF @@Error <> 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@PKFKName, @TableName2)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'FK ' + @PKFKname + ' Dropped from ' + @TableName2 + ' Table.'
FETCH NEXT FROM PKFK_Cursor INTO @PKFKName, @TableName2
END /* WHILE @@FETCH_STATUS = 0 */
CLOSE PKFK_cursor
DEALLOCATE PKFK_cursor
END
-- Drop the PK now
SELECT @PKName = so2.name
FROM sysobjects so1
INNER JOIN sysobjects so2
ON so1.id = so2.Parent_Obj
INNER JOIN sysindexes
ON so2.name = sysindexes.name
INNER JOIN sysindexkeys
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
INNER JOIN syscolumns
ON so1.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
WHERE so1.name = @TableName
AND so2.xtype = 'PK'
AND syscolumns.name = @ColumnName
SET @Qry4 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @PKName
Print 'Dropping PK constraint ' + @PKName
Exec(@Qry4)
IF @@Error <> 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@PKName, @TableName)
RollBack Tran
Return(@@Error)
END
ELSE
Print 'PK ' + @PKname + ' Dropped from ' + @TableName + ' Table.'
END
--Check to see if the column has a Unique Index defined on it.
IF Exists ( SELECT sysindexes.name
FROM syscolumns INNER JOIN sysobjects ON syscolumns.id =
sysobjects.id
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
INNER JOIN sysindexkeys
ON sysindexkeys.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
AND sysindexkeys.indid = sysindexes.indid
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
) BEGIN
DECLARE IX_Cursor CURSOR FOR
SELECT sysindexes.name
FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
INNER JOIN sysindexkeys
ON sysindexkeys.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
AND sysindexkeys.indid = sysindexes.indid
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
OPEN IX_Cursor
FETCH NEXT FROM IX_Cursor
INTO @UIXName
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Dropping Index ' + @UIXName + ' from ' + @TableName + ' Table'
-- If the Index Name is there in sysobjects then it's a Unique Constraint
-- Otherwise it's a Unique Index.
IF Exists(SELECT * FROM sysobjects WHERE name = @UIXName)
SET @Qry5 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' +
@UIXName
ELSE
SET @Qry5 = 'DROP INDEX ' + @TableName + '.' + @UIXName
Exec(@Qry5)
IF @@Error <> 0
BEGIN
Raiserror('ERROR : Failed to Drop Index %s from %s Table',16,1,
@UIXName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'Index ' + @UIXName + ' removed from ' + @TableName + ' table'
FETCH NEXT FROM IX_Cursor INTO @UIXName
END
CLOSE IX_cursor
DEALLOCATE IX_cursor
END
-- Drop the Column Now. All the constraints have been removed.
SET @Qry6 = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName
Exec(@Qry6)
IF @@Error <> 0
BEGIN
Raiserror('ERROR : Failed to Drop column %s from %s Table',16,1,
@ColumnName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'Column ' + @ColumnName + ' removed from ' + @TableName + ' table'
End
Else
BEGIN
Raiserror('ERROR : Column %s does not exist in %s Table',16,1,
@ColumnName, @TableName)
Rollback Tran
Return(@@Error)
END
END
ELSE
BEGIN
Raiserror('ERROR : Table %s does not Exist',16,1, @TableName)
Rollback Tran
Return(@@Error)
END
COMMIT TRAN
SET NOCOUNT OFF
GO
My Blog:
http://dineshasanka.spaces.live.com/ -
This generates the script you need.
SET nocount on
create table #tmpPKeys(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname not null,
TABLE_NAME sysname not null,
COLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
PK_NAME sysname null )
-- Get PK-info
insert into #tmpPKeys
exec sp_pkeys @table_name = yourTbName
, @table_owner = yourTbOwner
-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName
print '-- drop PK-constraint'
select 'Alter Table [' + TABLE_OWNER + '].[' + TABLE_NAME + '] drop constraint [' + PK_NAME + ']' + char(13) + 'GO '
from #tmpPKeys
where Key_SEQ = 1
-- order by TABLE_OWNER, TABLE_NAME
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Well, the problem with that is when the table is created, I do a
create table(
TableIdCol...
primaryKey(TableIdCol)
)
So, now you've got a good reason to care for the names of your object yourself.
There are more than one way to skin that cat. You can use one of the above or
sp_helpconstraint or
USE NORTHWIND
SELECT
T1.TABLE_NAME
, T2.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLES AS T1
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T2
ON
T1.TABLE_NAME = T2.TABLE_NAME
WHERE
CONSTRAINT_TYPE = 'PRIMARY KEY'
AND
TABLE_TYPE = 'BASE TABLE'
AND T1.TABLE_NAME = 'Orders'
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks guys, great solutions. I appreciate Franks "set base" solution. Frank your advice dead on; "name the object yourself". Had I known in advance that TSQL didn't support "alter table drop primary key", I certainly would have named it myself, and will from now on!
.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply