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 constraintALTER TABLE TableNAme NOCHECK CONSTRAINT ColumnNameGo with the flow & have fun! Else fight the flow |
|
|
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_newGOCREATE DATABASE test_drop_newGODECLARE table_cursor CURSOR FOR SELECT name FROM prueba_jjv_drop_new2..sysobjects WHERE type = 'U'OPEN table_cursorDECLARE @nombre_tabla sysnameFETCH NEXT FROM table_cursor INTO @nombre_tablaWHILE @@FETCH_STATUS = 0BEGIN PRINT @nombre_tabla EXEC('ALTER TABLE ' + @nombre_tabla + ' NOCHECK CONSTRAINT ALL') EXEC('DROP TABLE ' + @nombre_tabla) FETCH NEXT FROM table_cursor INTO @nombre_tablaENDCLOSE table_cursorDEALLOCATE table_cursorGO================================================================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. |
|
|
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 |
|
|
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 constraintI 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. |
|
|
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:-- VariablesDECLARE @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 keysINSERT INTO @FKeysSELECT 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 sysforeignkeysWHERE OBJECT_NAME(rkeyid) = 't_Case'UPDATE @FKeys SET dropcmd = 'ALTER TABLE ' + ftable + ' DROP CONSTRAINT ' + fknameUPDATE @FKeys SET addcmd = 'ALTER TABLE ' + ftable + ' WITH NOCHECK ADD CONSTRAINT [' + fkname + '] FOREIGN KEY ([' + fcol + ']) REFERENCES ' + rtable + ' ([' + rcol + '])'Raymond |
|
|
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 |
|
|
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 |
|
|
exergo
Starting Member
5 Posts |
Posted - 2004-09-10 : 11:39:06
|
Thank you very much for your help!!!!!Regards. |
|
|
|