Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to remove foreign key constraints?

Author  Topic 

exergo
Starting Member

5 Posts

Posted - 2004-09-10 : 09:35:52
Hello,
I need a SQL query to drop all user tables in a database, and for that purpose I need to remove first the foreign key constraints. Anybody can help???
Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 09:52:01
look at alter table in bol:

-- Disable the constraint
ALTER TABLE TableNAme NOCHECK CONSTRAINT ColumnName


Go with the flow & have fun! Else fight the flow
Go to Top of Page

exergo
Starting Member

5 Posts

Posted - 2004-09-10 : 10:06:02
Thanks for your quick response.

However, I think this does not solve my problem. The question is that I need to create a database and the 'model' database is designed for another application, so I need to remove all tables. I have write this script:

=============================================================

IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'test_drop_new')
DROP DATABASE test_drop_new
GO

CREATE DATABASE test_drop_new
GO

DECLARE table_cursor CURSOR FOR
SELECT name FROM prueba_jjv_drop_new2..sysobjects WHERE type = 'U'
OPEN table_cursor
DECLARE @nombre_tabla sysname
FETCH NEXT FROM table_cursor INTO @nombre_tabla
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @nombre_tabla
EXEC('ALTER TABLE ' + @nombre_tabla + ' NOCHECK CONSTRAINT ALL')

EXEC('DROP TABLE ' + @nombre_tabla)
FETCH NEXT FROM table_cursor INTO @nombre_tabla
END
CLOSE table_cursor
DEALLOCATE table_cursor
GO

================================================================

The result of its execution is that is removes almost all tables, except a few, and it show the next message:
"Could not drop object 'FR_FaultDescription' because it is referenced by a FOREIGN KEY constraint."
It seems that line EXEC('ALTER TABLE ' + @nombre_tabla + ' NOCHECK CONSTRAINT ALL') disables but not removes the constraint.

If somebody can help...
Thanks in advance.

Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 11:06:00
well have u looked at BOL = Books OnLine = Sql help?

ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]

| { [ WITH CHECK | WITH NOCHECK ] CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}



Go with the flow & have fun! Else fight the flow
Go to Top of Page

exergo
Starting Member

5 Posts

Posted - 2004-09-10 : 11:19:16
Thanks again.

Yes, I have been looking at Books On Line. The question is that I have not much experience with SQL Server, and I want to make a genericscript to drop all user table of a database, so I am not going to know their names and for that reason I am not going to know the FOREING KEY constraints that could exists in any of the tables.
So I need to get each table, check if it has a constraint and if this is the case get the constraint name, so I can use
ALTER TABLE tablename DROP CONSTRAINT constraint

I am now reading about sp_helpconstraints stored procedure, but I don't know yet how to use it to get the desired result.

Thanks again.
Go to Top of Page

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-10 : 11:31:57
I generally use something ilke this script. It uses sysforeignkeys to generate a table of DROP and ADD commands that I use to generate an sp_ExecuteSQL command:

-- Variables
DECLARE @Loop INT, @cmd VARCHAR(1000)
DECLARE @FKeys TABLE(id INT IDENTITY, rtable VARCHAR(100), rcol VARCHAR(100), ftable VARCHAR(100), fcol VARCHAR(100), fkname VARCHAR(100), dropcmd VARCHAR(1000), addcmd VARCHAR(1000))

-- Get a list of all t_Case foreign keys
INSERT INTO @FKeys
SELECT
rtable = OBJECT_NAME(rkeyid),
rcol = (select column_name from information_schema.columns where table_name = object_name(rkeyid) and ordinal_position = rkey),
ftable = OBJECT_NAME(fkeyid),
fcol = (select column_name from information_schema.columns where table_name = object_name(fkeyid) and ordinal_position = fkey),
fkname = OBJECT_NAME(constid),
dropcmd = '',
addcmd = ''
FROM sysforeignkeys
WHERE OBJECT_NAME(rkeyid) = 't_Case'

UPDATE @FKeys SET dropcmd = 'ALTER TABLE ' + ftable + ' DROP CONSTRAINT ' + fkname
UPDATE @FKeys SET addcmd = 'ALTER TABLE ' + ftable + ' WITH NOCHECK ADD CONSTRAINT [' + fkname + '] FOREIGN KEY ([' + fcol + ']) REFERENCES ' + rtable + ' ([' + rcol + '])'



Raymond
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 11:34:03
wouldn't this be something similar??


select distinct 'exec(''alter table ' + t1.Table_Name + ' drop constraint ' + t2.Column_Name + ''')'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2 on t1.Table_Name = t2.Table_Name
and t2.constraint_name like 'FK_%'


Go with the flow & have fun! Else fight the flow
Go to Top of Page

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-10 : 11:36:54
Yes absolutely. Couple that with the add command and you've got it. I've used the table idea because I've found that occasionally I need to do certain other actions based on table names which I can process one at a time.


Raymond
Go to Top of Page

exergo
Starting Member

5 Posts

Posted - 2004-09-10 : 11:39:06
Thank you very much for your help!!!!!
Regards.
Go to Top of Page
   

- Advertisement -