Find what's referencing specific object id

Build 1501 on 14/Nov/2017  This topic last edited on: 13/Jan/2016, at 11:48

You can use the following stored procedure to find what is referencing a certain object ID:

DROP PROCEDURE dbo.SearchAllTables

GO

 

CREATE PROC SearchAllTables (

      @SearchInt int

)

AS

BEGIN

      CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(20))

 

      SET NOCOUNT ON

 

      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr nvarchar(20)

      SET  @TableName = ''

      SET @SearchStr = STR(@SearchInt)

 

      WHILE @TableName IS NOT NULL

      BEGIN

            SET @ColumnName = ''

            SET @TableName = 

            (

                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

                  FROM INFORMATION_SCHEMA.TABLES

                  WHERE             TABLE_TYPE = 'BASE TABLE'

                        AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

                        AND   OBJECTPROPERTY(

                                    OBJECT_ID(

                                          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                                           ), 'IsMSShipped'

                                           ) = 0

            )

 

            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

            BEGIN

                  SET @ColumnName =

                  (

                        SELECT MIN(QUOTENAME(COLUMN_NAME))

                        FROM INFORMATION_SCHEMA.COLUMNS

                        WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)

                              AND   TABLE_NAME  = PARSENAME(@TableName, 1)

                              AND   DATA_TYPE IN ('int')

                              AND   QUOTENAME(COLUMN_NAME) > @ColumnName

                  )

      

                  IF @ColumnName IS NOT NULL

                  BEGIN

                        INSERT INTO #Results

                        EXEC

                        (

                              'SELECT " + @TableName + '.' + @ColumnName + ", LEFT(' + @ColumnName + ', 20) 

                              FROM ' + @TableName + ' (NOLOCK) ' +

                              ' WHERE ' + @ColumnName + ' = ' + @SearchStr

                        )

                  END

            END   

      END

 

      SELECT ColumnName, ColumnValue FROM #Results

END

 

To execute it:

EXEC dbo.SearchAllTables 16172 

where 16172 is the id of the element to find.

Before taking any action, send the results to Tera support