MSSQL List of Table's Referencing Objects

Similarly as we did in our Oracle past post, we now propose a simple but useful MSSQL query, that let us to know which objects (for example, stored procedures) reference a specified table (or any other object):


SELECT DISTINCT
   referenced_schema = d.referenced_schema_name
   , referenced_object_name = d.referenced_entity_name
   , referenced_object_type = o1.type_desc
   , referring_object_schema = s.name
   , referring_object_name = o.name
   , referring_object_type = o.type_desc
FROM
   sys.sql_expression_dependencies d
   INNER JOIN sys.objects o ON d.referencing_id=o.object_id
   INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
   INNER JOIN sys.objects o1 ON d.referenced_id=o1.object_id
WHERE
   --d.referenced_entity_name = 'table_name'
   d.referenced_entity_name like '%object_name%'
ORDER BY
       referenced_schema_name
;



But how can MSSQL obtain such a list of referenced table objects, contained inside the code of a stored procedure? Well, everytime you CREATE or ALTER a stored procedure, the compiler dynamically creates a list of syntatic objects, internally implemented as hash table; this list contains also all the code's referenced table, and it´s therefore used to keep the system tables and metadata updated.

LIKE is not always enough

Let´s imagine a situation in which we have, as input, a column s.namelist from a table s containing a comma-separated list of values:

Fabio,Andrea,John,Sara,Sarah...

This could be, for example, a dump from a CSV file or an Oracle External Table, inside a database staging area.

Now suppose we have to perform a filtering operation basing on the corresponding value of another column, s.name; in particular, we would like to identify -and insulate- all the names from our comma-list, whose name is the same in our corresponding s.name column. So, if we have a row like:

  ... | Smith | ... | Sara, Karl, John, Smith, Bill, Smith, Hoppen,... | ....

we wanna obtain:

 | Smith | Smith, Smith |

or just the entire name list.

(this doesn´t seem having much sense, but a similar situation could easly occour in many DWH or data integration scenarios).

Starting with a simple query statement (in this case T-SQL under MSSQL) like this:

select

       s.name

       , a.namelist  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

Doesn´t work correctly. What happen if we have two names like "Sarah" and "Sara", while filtering for "Sara"? With the LIKE operator, we would catch also "Sarah".

The solution is to clearly break and delimiatet the comma list names before performing any additional filtering/denormalization operation, through the use of the REPLACE operator

select

       s.name

       , '|' + REPLACE(UPPER(LTRIM(RTRIM(a.namelist))),' ', '|') + '|'  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

We will then obtain a clear, delimited list of names as |Sara|Sarah|Pippo|... in which we can easly apply the LIKE operator with the name explicitely delimited by the "|" as input

The RTRIM/LTRIM, and the UPPER operators will help us on avoiiding the classical data quality problems involving fields coming from source systems as names, addresses, etc...