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.
As various groups develop against the same database and the database changes with time, one
might run into small potholes.
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.
Let us add a few simple records
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.
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.
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:
- SYSCAT.INVALIDOBJECTS catalog view
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:
- sys.sql_modules ( http://msdn.microsoft.com/en-us/library/ms175081.aspx )
- sys.procedures ( http://msdn.microsoft.com/en-us/library/ms188737.aspx )
But, none of them indicate whether the object is valid or not.
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.
Let us do so against one of our programmable object – dbo.vw_PersonID:
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 columns. This is either because the entity references an object that does not exist or because of an errorin 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
Again, 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 seem that Microsoft’s SQL Server does not currently have 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
- sys.dm_sql_referenced_entities (Transact-SQL)
- sys.sql_dependencies (Transact-SQL)
Trouble-shooting & Identifying SQL Dependencies
- Troubleshooting SQL Dependencies
- Different Ways to Find SQL Server Object Dependencies
Others Database – DB/2
- IBM DB2 9.7 Advanced Application Developer Cookbook, page 27
3rd Part Tooling
- Finding Invalid Objects