thoughts of a codingbadger

Recently, I have been doing lots of SQL Server stuff so decided to add a few bits and pieces on here. 

First up, finding objects within your stored procedures. In this case, specifically views and tables.

This query is useful to find out if you have tables or views that aren't in any stored procedures. It gives you the object name, type of object and the number of stored procedures it appears in.

;with q as
(
Select   t.Name as [ObjectName],
       t.type_desc as [ObjectType],
      p.Name as [ProcedureName]
        
From sys.tables t
Left Join sys.procedures p on object_definition(p.object_Id) Like '%' + Replace(t.name, '_','!_')  + '%' ESCAPE '!'

    Union
Select  v.Name,
       v.type_desc,
      p.Name
   
From sys.views v
Left Join sys.procedures p on object_definition(p.object_Id) Like '%' + Replace(v.name, '_','!_')  + '%' ESCAPE '!'

)
Select    ObjectName,
       ObjectType,
       Count(ProcedureName) as [NumberOfOccurrences]
From q
Group by ObjectName, ObjectType

To list the names of the procedures that the object appears in you would change it to the following:

;with q as
(
Select   t.Name as [ObjectName],
       t.type_desc as [ObjectType],
      p.Name as [ProcedureName]
         
From sys.tables t
Left Join sys.procedures p on object_definition(p.object_Id) Like '%' + Replace(t.name, '_','!_')  + '%' ESCAPE '!'

    Union

Select  v.Name,
       v.type_desc,
      p.Name

From sys.views v
Left Join sys.procedures p on object_definition(p.object_Id) Like '%' + Replace(v.name, '_','!_')  + '%' ESCAPE '!'

)
Select    ObjectName,
       ObjectType,
       ProcedureName
From q
Where ObjectName = 'YOUR_OBJECT_NAME'