Saturday, August 3, 2013

Finding Dependent Tables

DECLARE @TempDepends Table(Name varchar(200),typeName varchar(20),updated varchar(10),selected varchar(10),columnName varchar(50))
INSERT INTO @TempDepends EXEC sp_depends @objname = N'vwAccount' ;
SELECT distinct Name FROM @TempDepends
DECLARE @TempDepend Table(oTypeName varchar(20),oObjName varchar(200),oOwner varchar(10),oSequence varchar(10))
INSERT INTO @TempDepend EXEC sp_MSdependencies N'Account', null, 1315327
SELECT distinct oObjName FROM @TempDepend
SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
    having MAX(si.rows)=0
ORDER BY
    2 DESC