Recently, I had to clean up a large data base. Some of the tables in that database were obsolete and needed to get deleted. Obsolete tables were not used in any stored procedures, so I needed to list all the tables used in stored procedures and subtract them from the set of all tables in the dataset to get the ones I could delete. So the starting point is to get a list of all objects used in a USP:

WITH cte 
AS 
(
    SELECT 
        source.name AS source, 
        target.name AS target,
        CASE target.xtype 
            WHEN 'U'  THEN 'Table'
            WHEN 'P'  THEN 'Store Procedure'
            WHEN 'FN' THEN 'Function'
            WHEN 'V'  THEN 'View'
        END AS type
    FROM sysdepends d 
    INNER JOIN sysobjects source ON source.id = d.id
    INNER JOIN sysobjects target ON target.id = d.depid
    WHERE source.xtype = 'P' 
)
SELECT DISTINCT target, source, type
FROM cte
ORDER BY target, type DESC, source

Now the script above returns all objects that ARE in use. But I need to know the objects that ARE NOT in use. Here’s how I do that (note: other than in the script above, below I am only interessted in table objects, not views or other USPs):

-- declare a tables-typed variable to store the result set

DECLARE @tables_in_use TABLE
(
    name VARCHAR(MAX) NOT NULL
)

-- add all user tables in the database

INSERT @tables_in_use
SELECT name 
FROM sysobjects 
WHERE xtype = 'U'

-- delete all tables that are in use

;WITH cte 
AS 
(
    SELECT 
        source.name AS source, 
        target.name AS target
    FROM sysdepends d 
    INNER JOIN sysobjects source ON source.id = d.id
    INNER JOIN sysobjects target ON target.id = d.depid
    WHERE source.xtype = 'P' 
)
DELETE t
FROM cte
INNER JOIN  @tables_in_use t ON t.name = cte.target

-- the tables not in use remain in the result set

SELECT * 
FROM @tables_in_use
ORDER BY name