Microsoft SQL Server allows for pretty fast development. One is able to create Programmable Objects Stored Procedures without first creating the referenced tables. One is also able to add new columns and remove existing ones without much impedance.
Yet as various groups develop against the same database and the structure changes, there are some small potholes that can make for bit bumpy rides.
In the rest of this post we will discuss possible side effects of renaming or removing columns.
Let us quickly setup a very small lab workshop.
Create Objects – Table & Programmable Objects
Create Object and programmable object.
drop table [dbo].[personIO]
if object_id('dbo.personIO') is null
create table [dbo].[personIO]
[firstname] nvarchar(100) not null
, [lastname] nvarchar(100) not null
, [middlename] nvarchar(100) not null
, [dateOfBirth] datetime null
, constraint PK_DBO_PERSONIO primary key
--drop view dbo.vw_PersonID
if object_id('dbo.vw_PersonIO') is null
exec('create view dbo.vw_PersonIO as select 1/0 as [null]')
alter view dbo.vw_PersonIO
if object_id('dbo.usp_PersonID_List') is null
exec('create procedure dbo.usp_PersonID_List as select 1/0 as [null]')
alter procedure dbo.usp_PersonID_List
Let us add a few simple records
set nocount on
truncate table [dbo].[personIO]
insert into [dbo].[personIO]
([firstname], [lastname], middlename)
('Betsy', 'Johnson', 'T')
insert into [dbo].[personIO]
List each object ddependencies
Nicely formatted sql code for listing each object’s dependency.
I will come back and properly credit the original source as it came from web through goggling.
DB_NAME() AS dbname
, o.type_desc AS referenced_object_type
SELECT ', ' + OBJECT_NAME(d2.referencing_id)
FROM sys.sql_expression_dependencies d2
WHERE d2.referenced_id = d1.referenced_id
ORDER BY OBJECT_NAME(d2.referencing_id)
FOR XML PATH('')
), 1, 1, ''
) AS dependent_objects_list
FROM sys.sql_expression_dependencies d1
JOIN sys.objects o
ON d1.referenced_id = o.[object_id]
GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name
ORDER BY o.type_desc, d1.referenced_entity_name
Let us disrupt things a bit.
Drop date of Birth column.
So Aunt Sallie finds out we are creating a table that is going to contain data for everyone in the family.
She is happy until she finds out it will contain each person’s date of birth, as well.
And, she says “she is having none of it“.
And, that she will call the authorities on us if we store everyone’s date of Birth.
So we have to drop the date of Birth column.
if object_id('dbo.personIO') is not null
from sys.columns tblC
where tblC.object_id = object_id('dbo.personIO')
and tblC.name = 'dateOfBirth'
print 'dropping column dbo.personIO.dateOfBirth ...'
alter table dbo.personIO
drop column [dateofBirth]
print 'dropping column dbo.personIO.dateOfBirth'
Once we drop the date of Birth column, our applications start to break.
For now we will not worry about the front end application code, and just concentrate on the database stuff.
Programmable Database Objects
When we query against our view we get “could not use view of function … because of binding errors“.
We get an error as well when we attempt to execute our Stored Procedure. The error goes like “Invalid column name”.
Other Relational Databases
In other databases, we can get a list of in-limbo objects by querying system meta-tables.
In Oracle, we can query *_objects ( user_objects, dba_objects, all_objects ) and filter for objects that have status equal to INVALID.
Please read more here:
IBM – DB2
In DB2, we can query SYSCAT.INVALIDOBJECTS.
You can also read more here:
Microsoft SQL Server
Let us see what is available in SQL Server.
Metadata – DMV – Catalog Views
SQL Server has a wealth of useful data in its catalog views.
A couple of contender views are:
But, none of them indicate whether the object is valid or not.
Let us check whether there are worthy workarounds …
SQL Server allows one to revisit code definitions using a couple of statements.
sp_refreshsqlmodule targets “stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger“.
And, sp_refreshview targets views.
Please follow the links below to read up a bit more.
We are able to determine all the objects and columns that a specific object reference by querying the sys.dm_sql_referenced_entities view.
referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('dbo.vw_PersonIO', 'OBJECT');
Once we drop the dateofBirth column, we run afoul a bit.
Msg 207, Level 16, State 1, Procedure vw_PersonIO, Line 10
Invalid column name 'dateofBirth'.
Msg 2020, Level 16, State 1, Line 9
The dependencies reported for entity "dbo.vw_PersonIO" might not include references to all
This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.
Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
Microsoft’s Umachandar Jayachandran has a very worthy post for listing Column level dependencies.
SQL Server Engine Tips
Guidelines, Best Practices, TSQL and SQL Programming Tips & Tricks
MSDN Blogs > SQL Server Engine Tips > Direct dependencies on a column…
Direct dependencies on a column
I have posted a slightly modified version of Umachandar’s beautiful and eternally useful code @ https://github.com/DanielAdeniji/IdentifyObjectColumnDependency.
I got to go.
And, quite a bit far from being Trey Songz, “I really got to go” ( https://www.youtube.com/watch?v=QzPHWkksP7Y ), as I do have a day job.
But, it does not seem that Microsoft SQL Server has database scoped tooling for identifying so called “Invalid Objects”.
I opened up a Connect Item – ID 1014712 – “Add attribute to sys.sql_modules that flags no longer valid objects” ( https://connect.microsoft.com/SQLServer/feedback/details/1014712/add-attribute-to-sys-sql-modules-that-flags-no-longer-valid-objects .
Object Catalog Views
Trouble-shooting & Identifying SQL Dependencies
Others Database – DB/2
3rd Party Tooling