delete all constraints for a table
-
hi
is there a way to delete all constriants (primary key , unique, not null , foregn key ) for a given table , also if its possible can they be deleted independently also ie only primary key etc...
Amrita
-
Check ALTER TABLE in bol.
BEGIN TRANSACTION
ALTER TABLE dbo.[Primary]
DROP CONSTRAINT PK_Primary
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Foreign]
DROP CONSTRAINT FK_Foreign_Primary
GO
COMMIT
It is not possible to drop all constraints in a single statement.
Regards,
gova -
but what if we dont knw the constriants and there name , we jus need to delete alll the constriants associated with a table
i was wondering if such an information is saved in any system tables , from where they can be deleted all at once
thnx
amrita
-
You should never delete them directly from the system tables. You can however create a script that collects the info from INFORMATION_SCHEMA-views (or system tables) and then dynamically removes them with ALTER TABLE.
-
You can get constraint names from sysreferences, sysindexes, sysconstraints table.
Easy way is use sp_help tablename. All constraints will be listed. Then use ALTER TABLE DROP CONSTRAINT to drop them.
or
Use EnterpriseManager to drop all constarints at once. Goto table properties. Delete whatever is listed in Relationships, Indexes/keys and Check Constraints.
Regards,
gova -
Nice call Govinn,
RTFM (Read the Manual)
Sql Server will Do alot for you, but will not Everything!
-
If you want to be selective, here's a few ways to generate scripts for dropping FK's and unique constraints and plain indices.
Note that it's also possible to just disable FK constraints rather than delete them.
-- generate drop statement for all FK's in all tables
select 'alter table ' + object_name(id) + ' drop constraint ' + object_name(constid) + char(13) + char(10) + 'go'
from sysconstraints where ( status & 3 ) = 3
order by id
-- creates drop statements for all indices and defined constraints implementing indices.
-- (PK, Unique)
select case when objectproperty(object_id(i.name), 'IsConstraint') = 1
then 'alter table ' + object_name(id) + ' drop constraint ' + i.name
else 'drop index ' + object_name(id) + '.' + i.name
end
from sysindexes i
where i.indid between 1 and 254
and objectproperty(id, 'IsUserTable') = 1
and indexproperty (id, name, 'IsStatistics') = 0
order by object_name(id), i.indid desc
/Kenneth
-
A quick way to get EM to generate a script with all the DROP CONSTRAINT commands is to go in to Design mode and insert a field somewhere (not at the end). DO NOT SAVE THE CHANGE!! The script to do this will create a new table and copy all the indexes & constraints, but they have to be dropped from the original table first to avoid name conflicts.
Copy the script generated for the change, and cancel design mode without saving changes. If you paste the script in QA it should have all the DROP CONSTRAINT commands you need.
I'm not 100% positive that the script includes everything, but it has always worked for me.
-
Easily done see http://qa.sqlservercentral.com/scripts/contributions/25.asp
Francis
-
The script you pointed to is disabling constraints, not dropping them
* Noel -
Dooh !!
Francis
-
Here's a script I put together that will Generate sql that will drop all the foreign keys:
SET NOCOUNT ON
print 'USE ' + DB_NAME()
print ''
-- Generate Drops for All Foreign Keys in Database
print '-- Drop Foreign Keys'
print ''
select distinct 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
from sysforeignkeys
go
Run it in Query Analyzer (results to text) and save the results as an sql file.
I also have a script that will recreate the foreign keys if you are interested. (It has to be run before you drop them, of course.)
-
Here is the ultimate solution
declare @cmd nvarchar(500)
declare cmds cursor for
SELECT 'ALTER TABLE ' + IST.table_schema + '.' + OBJECT_NAME(SO.parent_obj) + ' DROP CONSTRAINT ' + SO.[name]
FROM dbo.sysobjects SO LEFT OUTER JOIN
information_schema.tables IST ON OBJECT_NAME(SO.parent_obj) = IST.table_name
WHERE (SO.xtype IN ('F'))
union
SELECT 'ALTER TABLE ' + IST.table_schema + '.' + OBJECT_NAME(SO.parent_obj) + ' DROP CONSTRAINT ' + SO.[name]
FROM dbo.sysobjects SO LEFT OUTER JOIN
information_schema.tables IST ON OBJECT_NAME(SO.parent_obj) = IST.table_name
WHERE (SO.xtype IN ('TR'))
open cmds
fetch next from cmds into @cmd
while @@fetch_status = 0
begin
exec sp_executesql @statement = @cmd
fetch next from cmds into @cmd
end
CLOSE cmds
DEALLOCATE cmds
Regards
Faisal Ahmed Farooqui
-
Try this one, there was an error in above code
declare @cmd nvarchar(500)
declare cmds cursor for
SELECT 'ALTER TABLE ' + IST.table_schema + '.' + OBJECT_NAME(SO.parent_obj) + ' DROP CONSTRAINT ' + SO.[name]
FROM dbo.sysobjects SO LEFT OUTER JOIN
information_schema.tables IST ON OBJECT_NAME(SO.parent_obj) = IST.table_name
WHERE (SO.xtype IN ('F'))
union
SELECT 'Drop Trigger ' + IST.table_schema + '.' + SO.[name]
FROM dbo.sysobjects SO LEFT OUTER JOIN
information_schema.tables IST ON OBJECT_NAME(SO.parent_obj) = IST.table_name
WHERE (SO.xtype IN ('TR'))
open cmds
fetch next from cmds into @cmd
while @@fetch_status = 0
begin
exec sp_executesql @statement = @cmd
fetch next from cmds into @cmd
end
CLOSE cmds
DEALLOCATE cmds
Regards
Faisal Ahmed Farooqui
-
Wait a minute... if the first one was the "ulitimate" solution, then ?????
All of this is good, folks, but no one has asked WHY DO YOU WANT TO DO THIS?
Dropping a primary key, for example, is normally a "Bozo No-No" except for the rarest of situations.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply