0

I'm doing some tests on a particular table MyTable, and I need to drop all tables / properties of a database that aren't relevant to MyTable.

How would I do that with a script and/or in SQL Server Management Studio (2012)?

Disclaimer: This is only in local test environment, no need for anyone to worry about prod concerns.

alksdjg
  • 1,019
  • 2
  • 10
  • 26

1 Answers1

0

You could write a quick cursor to drop everything not in the results of this handy script:

WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel)
AS 
(
SELECT  o.[object_id] AS referenced_id , 
 o.name AS referenced_name, 
 o.[object_id] AS referencing_id, 
 o.name AS referencing_name,  
 0 AS NestLevel
FROM  sys.objects o 
WHERE o.name = 'dim_table_name'

UNION ALL

SELECT  d1.referenced_id,  
 OBJECT_NAME( d1.referenced_id) , 
 d1.referencing_id, 
 OBJECT_NAME( d1.referencing_id) , 
 NestLevel + 1
 FROM  sys.sql_expression_dependencies d1 
JOIN DepTree r ON d1.referenced_id =  r.referencing_id
)
SELECT DISTINCT referenced_id, referenced_name, referencing_id, referencing_name, NestLevel
FROM DepTree WHERE NestLevel > 0
ORDER BY NestLevel, referencing_id;
LordBaconPants
  • 1,404
  • 1
  • 19
  • 22
  • Can you provide some explanation about what this script is doing? Should the name of the table I'm asking about be included in here somewhere? – alksdjg Oct 23 '15 at 00:51
  • Yep, so if you put your table name in the first select, I've put 'dim_table_name' there. The script will go through and find any dependencies on that table, and then any dependencies on that dependancy. So, if your table is used in a view, it will tell you what is depending on that view. That will give you a list of things not to drop. From there you'll need to pull a list on what you can drop, and iterate through that and do the actual drop – LordBaconPants Oct 23 '15 at 01:09