Hi all,
How do i drop all tables (with constraints) in a database using one queries?
one easy way is using the ms_foreachtable.
exec sp_msforeachtable 'Drop table ?'
Note: This sp is undocumented sp. You can use it for one time activity, but don't stick on this sp. It may be get removed with out any notification.
|||Could not drop object 'dbo.TRN' because it is referenced by a FOREIGN KEY constraint.
Msg 3726, Level 16, State 1, Line 1
This is what i get..
|||Execute the same query multiple times|||Would not it be easier if you drop the database?
AMB
|||Another possible approach:
if object_id('[dbo].[sp_drop_all_tables]') is not null drop proc [dbo].[sp_drop_all_tables]
go
create proc [dbo].[sp_drop_all_tables]
@.pSchemaName sysname = null
as
set xact_abort on
set nocount on
declare
@.ParentTableSchemaName sysname,
@.ParentTableName sysname,
@.ChildTableSchemaName sysname,
@.ChildTableName sysname,
@.FKName sysname,
@.SQLCommandText nvarchar(max),
@.ErrorMessageText nvarchar(max)
if db_name() = 'master'
begin
raiserror('Cannot be run in the master database.', 16, 1)
return
end
if @.pSchemaName = 'sys'
begin
raiserror('Cannot be run for tables in the [sys] schema.', 16, 1)
return
end
select
@.ParentTableSchemaName = @.pSchemaName
begin tran
select
@.SQLCommandText = '
declare curObject cursor global static for
select
fk_po_s.name as ParentTableSchemaName,
fk_po.name as ParentTableName,
fk_co_s.name as ChildTableSchemaName,
fk_co.name as ChildTableName,
fk.name as FKName
from sys.foreign_keys as fk
join sys.objects as fk_po
on fk_po.object_id = fk.referenced_object_id
join sys.schemas as fk_po_s
on fk_po_s.schema_id = fk_po.schema_id
join sys.objects as fk_co
on fk_co.object_id = fk.parent_object_id
join sys.schemas as fk_co_s
on fk_co_s.schema_id = fk_co.schema_id
where fk_po.type = ''u''
and fk_po_s.name <> ''sys''' +
case when @.ParentTableSchemaName is null then '' else '
and fk_po_s.name = ''' + @.ParentTableSchemaName + '''' end + '
order by
fk_po_s.name,
fk_po.name,
fk_co_s.name,
fk_co.name,
fk.name'
--print @.SQLCommandText
exec(@.SQLCommandText)
open curObject
fetch next from curObject into
@.ParentTableSchemaName,
@.ParentTableName,
@.ChildTableSchemaName,
@.ChildTableName,
@.FKName
while @.@.fetch_status = 0
begin
select
@.SQLCommandText = 'alter table [' + @.ChildTableSchemaName + '].[' + @.ChildTableName +
'] drop constraint [' + @.FKName + ']'
-- print @.SQLCommandText
exec(@.SQLCommandText)
fetch next from curObject into
@.ParentTableSchemaName,
@.ParentTableName,
@.ChildTableSchemaName,
@.ChildTableName,
@.FKName
end
close curObject
deallocate curObject
select
@.ParentTableSchemaName = @.pSchemaName
select
@.SQLCommandText = '
declare curObject cursor for
select
s.name as ParentTableSchemaName,
o.name as ParentTableName
from [' + db_name() + '].sys.objects as o
join [' + db_name() + '].sys.schemas as s
on s.schema_id = o.schema_id
where o.type = ''u''
and left(o.name, 1) <> ''#''
and s.name <> ''sys''' +
case when @.ParentTableSchemaName is null then '' else '
and s.name = ''' + @.ParentTableSchemaName + '''' end + '
order by
o.name'
--print @.SQLCommandText
exec(@.SQLCommandText)
open curObject
fetch next from curObject into
@.ParentTableSchemaName,
@.ParentTableName
if @.@.fetch_status <> 0
begin
print 'No user tables exist.'
end
while @.@.fetch_status = 0
begin
select
@.SQLCommandText = 'drop table [' + @.ParentTableSchemaName + '].[' + @.ParentTableName + ']'
print @.SQLCommandText
exec(@.SQLCommandText)
fetch next from curObject into
@.ParentTableSchemaName,
@.ParentTableName
end
close curObject
deallocate curObject
commit
NormalReturn:
return 0
ErrorReturn:
if @.@.trancount > 0
begin
rollback
end
if cursor_status('global','curObject') = 1
begin
close curObject
end
if cursor_status('global','curObject') = -1
begin
deallocate curObject
end
select
@.ErrorMessageText = coalesce(@.ErrorMessageText, 'The stored procedure sp_drop_all_tables failed.')
raiserror(@.ErrorMessageText, 16, 1)
return 1
go
Ron Rice
No comments:
Post a Comment