3

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.

Jacek
  • 194
  • 1
  • 11

3 Answers3

2

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

Community
  • 1
  • 1
Darren
  • 68,902
  • 24
  • 138
  • 144
  • As is my understanding, the script lists related tables. Is there also a way to list dependent stored procedures and views? I mean a way to find what stored procedures, views (and maybe triggers and jobs) are using (referring) the table in question. – Jacek Mar 04 '13 at 17:36
0

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

SP007
  • 1,871
  • 1
  • 22
  • 31
0

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.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39