October 20, 2015 at 1:25 pm
Can someone help me with this script.. I am stuck..
What I plan to do is follows:
I have a database with many tables and each table has a foreign key ( or many ).
One of the tables only has a primary key.
The idea here is to drop all the tables without getting any error messages.
You know what I mean ....:)
I like to come up with a SQL script in some order where I can drop table "D" first and then table "X" .... etc.
The idea here is to avoid and error message that says 'CANNOT DROP TABLE B because there are other tables refencing table B "
IGNORE my code below.. you may have a great way to do it.
if object_id('tempdb..#children') IS NOT NULL DROP TABLE #children;
if object_id('tempdb..#parent') IS NOT NULL DROP TABLE #parent;
create table #parent( ID INT IDENTITY(1,1) , name VARCHAR(100) );
create table #children( ID INT IDENTITY(1,1) , name VARCHAR(100) );
INSERT INTO #parent(name)
select distinct
object_name(fk.referenced_object_Id)
from sys.foreign_keys fk
inner join sys.objects o
on fk.referenced_object_id = o.object_id;
INSERT INTO #children(name)
select distinct
object_name(fk.parent_object_Id)
from sys.foreign_keys fk
inner join sys.objects o
on fk.referenced_object_id = o.object_id;
select
@sql = @sql + 'DROP TABLE ' + c.name + '; DELETE FROM #children where name = ''' + c.name + ''';'
from #children c
left join #parent p
on c.name = p.name
where
p.name is null
EXEC sp_executeSQL @sql;
October 20, 2015 at 1:57 pm
It is unclear what you are trying to do, your seem to be trying to drop local temporary tables, why not simply close the session?
Looks like homework to me!
...
October 20, 2015 at 2:00 pm
Why not just drop and recreate the database?
October 20, 2015 at 2:03 pm
actually, MS provides a stored procedure that will give you all objects(procs, views, tables, etc) in dependency order:
this works great as long as you don't have any circular references.
try this in SSMS:
EXEC sp_msdependencies @intrans = 1
if you insert the results into a temp table, you could then filter it to be just tables, just views, or use the other, alternative parameters for the proc to do the same thing
EXEC sp_msdependencies @intrans = 1 ,@objtype=8 --8 = tables
Lowell
October 20, 2015 at 2:15 pm
The following will drop all foreign key constraints in your database. Be sure to remove the space between the d and e in the first statement.
d eclare @SQLCmd nvarchar(max);
set @SQLCmd = stuff((select N'ALTER TABLE ' + SchemaName + N'.' + TableName + N' DROP CONSTRAINT ' + stuff((select N',' + fk.name from sys.foreign_keys fk where fk.parent_object_id = object_id(fkt.SchemaName + N'.' + fkt.TableName) order by fk.name for xml path(''),TYPE).value('.','nvarchar(max)'),1,1,'') + ';' + nchar(13) + nchar(10)
from (select distinct object_schema_name(parent_object_id) SchemaName, object_name(parent_object_id) TableName from sys.foreign_keys) fkt(SchemaName,TableName)
order by fkt.SchemaName, fkt.TableName
for xml path(''),TYPE).value('.','nvarchar(max)'),1,0,'');
print @SQLCmd;
exec sys.sp_executesql @SQLCmd;
From there you can do something similar to drop all the tables.
Any reason you can't just create a new empty database?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply