CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Jacek
Jacek

Reputation: 194

SQL Server 2005: How to drop selected tables, with all related objects

There is a copy of a huge MS Sql Server 2005 database, with more than 1300 tables, plus hundreds of stored procedures and dozens of views, not counting jobs. It is used by several applications.

I need to slim down this monster, leaving only less than 200 tables and 100 stored procedures and other related objects, required by one application to work properly. Of course there is an exact list of tables and stored procedures used by this application, but there is no way to list objects not invoked directly from the code (like jobs, views, triggers, etc).

So there is a question: please advice the best way to accomplish that task. How to securely drop 1000 tables, listing first related objects (like foreign keys, related views and stored procedures, jobs, triggers, etc), and if those objects do not point to other objects required by the application (tables or stored procedures), delete them and then drop the table. The same goes for stored procedures.

Upvotes: 3

Views: 551

Answers (3)

Darren
Darren

Reputation: 70796

You want to use the system tables which contains all the information regarding constraints, views etc. http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80).aspx

I'd look specifically at sysforeignkeys and sysconstraints etc and join on the correct object_id, to do this you could do something like the following:

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn 
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
order by TableWithForeignKey, FK_PartNo

Which will list the foreign keys from your table, you could replace WHERE name = to use OBJECT_ID IN (.... all your object id's)

This script is based on one of the answers from (How can I list all foreign keys referencing a given table in SQL Server?)

Edit

From @Jacek's comment you can also use the stored procedure sp_depends

http://msdn.microsoft.com/en-us/library/ms189487.aspx

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

It's not brill, but if you use the scripting tools and script a drop and create of all the obejcts in the database. It does do it based on the dependancy order that it knows about.

Upvotes: 0

SP007
SP007

Reputation: 1921

This is my first thought: Use Information_Schema view to find out meta data about tables and take action based on that.

Upvotes: 0

Related Questions