SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to remove foreign key constraints?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

exergo
Starting Member

5 Posts

Posted - 09/10/2004 :  09:35:52  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 09/10/2004 :  09:52:01  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 - 09/10/2004 :  10:06:02  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 09/10/2004 :  11:06:00  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 - 09/10/2004 :  11:19:16  Show Profile  Reply with Quote
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

United Kingdom
367 Posts

Posted - 09/10/2004 :  11:31:57  Show Profile  Reply with Quote
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

Slovenia
11750 Posts

Posted - 09/10/2004 :  11:34:03  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

United Kingdom
367 Posts

Posted - 09/10/2004 :  11:36:54  Show Profile  Reply with Quote
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 - 09/10/2004 :  11:39:06  Show Profile  Reply with Quote
Thank you very much for your help!!!!!
Regards.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000