Get dependant Objects

,

/*===============================================================

Example : EXEC

dbo.usp_GetDependantObjects

@varTableName = 'sysdiagrams', @varSPName

= NULL

===============================================================*/

ALTER PROCEDURE
[dbo].[usp_GetDependantObjects]

(

      @varTableName      VARCHAR(100)      = NULL,

      @varSPName        VARCHAR(255)      = NULL

)
AS
BEGIN

     SET NOCOUNT  ON

      DECLARE @varSQL               VARCHAR(8000)

      SET @varSQL = 'SELECT      DISTINCT sysO.id, sysO.name as Procedure_Name, '
        + '               sysO2.name as Table_Name, sysD.depid, '
        + '               sysD.depnumber '
        + 'FROM       sysdepends  sysD'
        + '               INNER JOIN      sysobjects  sysO '
        + '                           ON sysO.id           = sysD.id '
        + CASE WHEN @varSPName IS NULL THEN '' ELSE ' AND sysO.name = ''' + @varSPName + ''' ' END
        + '               INNER JOIN      sysobjects sysO2 '
        + '                           ON      sysO2.id    = sysD.depid '
        + '                                 AND      sysD.depnumber      = 1      '
        + CASE WHEN @varTableName IS NULL THEN '' ELSE ' AND sysO2.name = ''' + @varTableName +
           ''' ' END
        + ' ORDER BY sysO.name, sysO2.name '

      --PRINT @varSQL 

      EXECUTE ( @varSQL )

END

Rate

4 (3)

Share

Share

Rate

4 (3)