CODEWITHSUNDEEP
sql-serverdatabasescripting
Ray
Ray

Reputation: 192256

In SQL Server, how do I identify *all* dependencies for a specific table using system tables/views?

I am writing a DDL script to drop a number of tables but need to identify all dependencies for those tables first. Those dependencies include foreign key constraints, stored procedures, views, etc. Preferably, I want to programmatically script out dropping those dependencies using the system tables/views before dropping the dependent table.

Upvotes: 2

Views: 1075

Answers (6)

Joseph 'Joey' Morgan
Joseph 'Joey' Morgan

Reputation: 1

I know the view is deprecated, but I like it while it works. I wrote a messy proc that is working. I am posting it without prettying it up so it is out there, but feel free to pretty it up and uncomment the stuff I don't use in this version. Making the parameter nullable lets me specify a schema or get all schemas. Omitting the MS_Shipped objects eliminates stuff I didn't build and don't need to track.

CREATE OR ALTER PROC util.spSelectDependenciesForSchemaObjects (@Schema NVARCHAR (25) = NULL)
AS
    BEGIN;
        WITH
            T1
        AS
            (
                SELECT  DISTINCT
                                object_id AS TObjectId, '' + SCHEMA_NAME(T.schema_id) + '.' + name AS ObjectName, T.type_desc AS ObjectType, schema_id,T.create_date, T.modify_date
                    FROM    sys.objects AS T
                    WHERE (SCHEMA_NAME(schema_id) = @Schema OR @Schema IS NULL) AND T.is_ms_shipped=0
            )
        ,
            T2
        AS
            (
                SELECT
                T1.ObjectName AS DependentObject
            , T1.ObjectType
            , OBJECT_NAME(SD.referenced_major_id) AS ReferencedObject
            , C.name AS ReferencedColumn
            , cmd.fnFullDataTypeName(C.system_type_id, C.max_length, C.precision, C.scale) AS ColumnDataType
            , CASE
                    WHEN SD.is_updated = 1
                        THEN
                        'UPDATES'
                    ELSE
                        'SELECTS FROM'
                    END AS CRUDType
            , SD.object_id AS DependentObjectId
            , SD.referenced_major_id AS ReferencedMajorId
            , SD.referenced_minor_id AS ReferencedMinorId
            ,SCHEMA_NAME(T1.schema_id) SchemaName
            ,T1.schema_id

            , SD.class AS DepClass
            ,T1.create_date Created
            ,T1.modify_date Modified
                    FROM
                sys.sql_dependencies AS SD
                 INNER JOIN
                     T1
                     ON
                     SD.object_id = T1.TObjectId

                 LEFT JOIN
                     sys.all_columns AS C
                     ON
                     C.object_id = SD.referenced_major_id
                     AND C.column_id = SD.referenced_minor_id
             
            )
        SELECT  DISTINCT
                    T2.SchemaName,  T2.ReferencedObject, T2.DependentObject AS NeededBy, T2.ObjectType, T2.Created,T2.Modified,T2.DependentObjectId, T2.ReferencedMajorId
            FROM    T2
            ORDER BY
    T2.SchemaName,T2.ObjectType,    T2.ReferencedObject, NeededBy;
    END;

I am also keeping the IDs because I can then use this same approach with more recursions. Right now I just want to find out what we can omit when moving to the new server.

The data type function is another lazy-man hack, but I am including it because it makes for easier reading in the output.

    CREATE FUNCTION FullDataTypeName
(
    @SystemTypeId INT,
    @MaxLength      INT,
    @Precision      INT,
    @Scale              INT
)
RETURNS NVARCHAR(50)
AS
    BEGIN
        DECLARE @TypeName NVARCHAR(50);
        SET @TypeName =
            TYPE_NAME(@SystemTypeId)
            + CASE
                         WHEN @SystemTypeId IN
                             (
                                 173,
                                 175,
                                 239,
                                 231,
                                 165,
                                 167
                             )
                                    THEN '(' + CASE
                                                                    WHEN @MaxLength = -1
                                                                             THEN 'MAX'
                                                                    ELSE CONVERT(NVARCHAR(8), @MaxLength)
                                                         END + ')'
                         WHEN @SystemTypeId IN
                             (
                                 106,
                                 108
                             )
                                    THEN '(' + CONVERT(NVARCHAR(6), @Precision) + ',' + CONVERT(NVARCHAR(6), @Scale) + ')'
                         ELSE ''
                END;
        RETURN @TypeName;

    END;

Have at it!

Joey Morgan

Upvotes: 0

glasnt
glasnt

Reputation: 2973

Could you reference sysreferences?

select 'if exists (select name from sysobjects where name = '''+c.name+''') '
+' alter table ' + t.name +' drop constraint '+ c.name 
 from sysreferences sbr, sysobjects c, sysobjects t, sysobjects r
 where c.id = constrid 
  and t.id = tableid 
  and reftabid = r.id
  and r.name = 'my_table'

That will generate a whole lot of conditional drop constraint calls. Should work.

Upvotes: 0

Mauro
Mauro

Reputation: 4511

you could always search through the syscomments table....that might take a while though...

Upvotes: 0

Eric
Eric

Reputation: 11662

This is extremely messy to write from scratch. Have you considered a 3rd party tool like Red-Gate SQL Dependency Tracker?

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 135021

sp_depends is not reliable see: Do you depend on sp_depends (no pun intended)

Upvotes: 1

Almond
Almond

Reputation: 1603

You can use the sp_depends stored procedure to do this:

USE AdventureWorks GO EXEC sp_depends @objname = N'Sales.Customer' ;

http://msdn.microsoft.com/en-us/library/ms189487(SQL.90).aspx

Upvotes: -2

Related Questions