declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.const_id integer
declare @.prt_obj integer
declare @.const_name varchar(256)
declare @.parent_name varchar(256)
-- definindo o cursor...
declare constraint_id cursor local fast_forward for
select
id
from
sysobjects
where
XTYPE='PK'
OR
XTYPE='F'
order by
parent_obj
declare parent_obj cursor local fast_forward for
SELECT
PARENT_OBJ
FROM
SYSOBJECTS
WHERE
XTYPE='PK'
OR
XTYPE='F'
order by
parent_obj
-- definindo o cursor...
-- apagando as constraints...
open constraint_id
open parent_obj
fetch next from parent_obj into @.prt_obj
fetch next from constraint_id into @.const_id
set @.parent_name = (select name from sysobjects where id=@.prt_obj )
set @.const_name = (select name from sysobjects where id=@.const_id)
select @.alter_table_statement = 'alter table '+ ltrim(rtrim(@.parent_name)) + ' drop constraint ' + ltrim(rtrim(@.const_name))
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from parent_obj into @.prt_obj
fetch next from constraint_id into @.const_id
set @.parent_name = (select name from sysobjects where id=@.prt_obj )
set @.const_name = (select name from sysobjects where id=@.const_id)
select @.alter_table_statement = ('alter table '+ ltrim(rtrim(@.parent_name)) + ' drop constraint ' + ltrim(rtrim(@.const_name)))
exec(@.alter_table_statement)
end
close constraint_id
close parent_obj
-- desalocando o cursor...
deallocate table_name_cursor
deallocate constraint_id
deallocate parent_obj
The problem is that this script doesn't complete it's action because of constraint reference problem.
The constraint 'PK__justification__0FEC5ADD' is being referenced by table 'justification', foreign key constraint 'FK6F298AF2E0E77479'.
Can I turn off this constraint verification?
Or there is another way to delete this constraints?
you can't drop a unique or
primary constraints if it is being use to reference to other
table via foriegn key
|||OK I doesnt knew it...Very very thanks to you|||
I just posted a set of procs to do this on my website to strip a database clean of all objects (I needed to do this to change collations of all objects: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1333.entry)
The code is here:
http://drsql.org/Documents/utility.drop_objects_procs.zip
After building the procs in the script, just run some or all of these and you can get rid of everything (I hope you are going to put it back though :)
exec utility.checkConstraints$remove @.table_schema = '%'
exec utility.defaultConstraints$remove @.table_schema = '%'
exec utility.relationships$remove @.table_schema = '%'
exec utility.uniqueConstraints$remove @.table_schema = '%', @.table_name = '%', @.index_name = '%'
exec utility.indexes$remove @.table_name = '%',@.index_name = '%'
exec utility.primaryKeyConstraints$remove @.table_schema = '%', @.table_name = '%', @.index_name = '%'
exec utility.synonyms$remove
exec utility.codedObjects$remove @.object_name = '%'
drop procedure utility.codedObjects$remove --doesn't drop itself :)
I clearly make no guarantees that this code covers everything yet, (like Full-Text has not been tested, so it might not drop a PK if you have full text indexes) but all that was left in AdventureWorks after executing these procs was one function because it was being used in a computed column.
Every proc uses Try...Catch blocks when there is an error, then it puts out a message like this:
Error occurred: 3729:Cannot DROP FUNCTION 'dbo.ufnLeadingZeros' because it is being referenced by object 'Customer'.
Statement executed: drop function dbo.ufnLeadingZeros
This way you can retry the command after fixing the dependencies. If you like it, let me know. If you change it, let me know and I will incorporate it :)
No comments:
Post a Comment