Listing Tables and Columns for SQL Server Views, Triggers and Stored Procedures

If you want to know which server objects (Views, Procedures, Triggers or Functions) use which tables and columns then you’ve come to the right place. Maybe you need to replace or deprecate a column or two.

To create this list we need –

• a View to list all our server objects
• a SP (Stored Procedure) that returns a list of Tables and Columns for a specified server object
• a PT (Passthrough Query) to pass the server object name to the SP and executes the SP
• a couple of VBA procedures, one to iterate the view and call the other for each server object
• a Table to store the results with columns for: ServerObject_Name, Table_Name, Column_Name

Setup

I’ll assume at this point you know how to link Views and PTs. Otherwise why would you want to list their dependencies?

The Table

Create your table in your application with the columns listed below and save it as oSysServerObjectReference.

• ServerObjectReference_ID – Autonumber Primary Key
• ServerObject_Name – Text with a non unique index
• Table_Name – Text
• Column_Name – Text

The oSys prefix is one I use for application data that is kept in the Application rather than in the backend database. The ‘o’ is for Osel and by using oSys as a prefix I can hide my application tables independently of Access’ Msys or Usys system tables. Feel free to change the prefix to suit you own naming conventions.

The View

Add the View in your database using the script below and link to it in your Access FE.

CREATE OR ALTER     VIEW [dbo].[vwServerObject]
AS
SELECT	
			Object_ID 
			, ServerObject_Name = Name 
			, Schema_ID

		FROM sys.Objects
		WHERE type IN('V', 'P', 'TR', 'FN')

The Stored Procedure

Add your SP to your database using this script

CREATE OR ALTER PROCEDURE [dbo].[uspServerObject_Dependent]
@ServerObjectName Varchar(100)
AS
	SELECT 
			Table_ID = referenced_id
			, Table_Name = referenced_entity_name
			, Ordinal_Position = referenced_minor_id
			, Column_Name = referenced_minor_name 
			, Select_All = is_select_all
	FROM sys.dm_sql_referenced_entities('dbo.' + @ServerObjectName, 'OBJECT')

The Passthrough Query

Create your PT, let’s call it oPtServerObject_Dependent. It needs the connection string to your database and to be set to return records.

The VBA Procedures

The VBA procedures to iterate the server objects listed by the view, and the code to pass the server object name to the SP and return the list of tables and columns –

Public Sub SOReferences()
Dim rst As DAO.Recordset
Dim strServerObjectName As String
Dim strSQL As String

    strSQL = "SELECT *" _
                & " FROM vwServerObject" _
                & " ORDER BY ServerObject_Name"
    Set rst = oDB.OpenRecordset(strSQL, dbReadOnly)
    With rst
        Do Until .EOF
            strServerObjectName = !ServerObject_Name
            Reference_Count strServerObjectName
            .MoveNext
        Loop
    End With
End Sub
Public Function Reference_Count(strServerObjectName As String) As Long
Dim qdf As DAO.QueryDef
Dim strSQL As String

    Set qdf = oDB.QueryDefs("oPtServerObject_Dependent")
    qdf.SQL = "EXEC uspServerObject_Dependent @ServerObjectName = '" & strServerObjectName & "'"
    
    strSQL = "DELETE * FROM" _
                & " oSysServerObjectReference" _
                & " WHERE ServerObject_Name = '" & strServerObjectName & "'"
    oDB.Execute strSQL, dbFailOnError + dbSeeChanges
    
    strSQL = "INSERT INTO oSysServerObjectReference" _
                        & "(ServerObject_Name" _
                        & ", Table_Name" _
                        & ", Column_Name" _
                        & ")" _
                & " SELECT" _
                        & " '" & strServerObjectName & "' AS ServerObject_Name" _
                        & ", Table_Name" _
                        & ", Column_Name" _
                & " FROM oPtServerObject_Dependent" _
                & " WHERE Column_Name IS NOT NULL"
    oDB.Execute strSQL, dbFailOnError + dbSeeChanges

End Function

Summary

Now you have a list of all your dependencies, and you know which tables or columns not to delete without breaking something, sweet!