Reputation: 194
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
Reputation: 70786
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
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
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