Microsoft – SQL Server – Transact SQL – “Invalid Object”

Background

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.

Interesting Scenario

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.

 

 

Lab

Let us quickly setup a very small lab workshop.

 

Create Objects – Table & Programmable Objects

 

Create Object and programmable object.

 


use [tempdb]
go

/*
	drop table [dbo].[personIO]
*/
if object_id('dbo.personIO') is null
begin

	create table [dbo].[personIO]
	(

		  [firstname]   nvarchar(100) not null
		, [lastname]    nvarchar(100) not null

		, [middlename]  nvarchar(100) not null
			constraint constraintDBOPersonIDMiddleName 
				default ''

		, [dateOfBirth] datetime null

		, constraint PK_DBO_PERSONIO primary key 
		(
			  [lastname]
			, [firstname]
			, [middlename]
		)
	)


end
go

--drop view dbo.vw_PersonID
if object_id('dbo.vw_PersonIO') is null
begin

	exec('create view dbo.vw_PersonIO as select 1/0 as [null]')

end
go

alter view dbo.vw_PersonIO
as
	select 
		  lastname
		, firstname
		, middlename
                , dateofBirth
	from    dbo.personIO

go


if object_id('dbo.usp_PersonID_List') is null
begin

	exec('create procedure dbo.usp_PersonID_List as select 1/0 as [null]')

end
go

alter procedure dbo.usp_PersonID_List
as
begin
	select 
		  lastname
		, firstname
		, middlename
                , dateofBirth
	from    dbo.personIO
end
go

 

 

 

 

Add data

Let us add a few simple records


set nocount on
go

use tempdb
go

truncate table [dbo].[personIO]
go

insert into [dbo].[personIO]
([firstname], [lastname], middlename)
values
('Betsy', 'Johnson', 'T')
go


insert into [dbo].[personIO]
([firstname], [lastname])
values
('Mark', 'McGuire')
go

 

Query Data

Query data


use [tempdb]
go

select *
from   [dbo].[vw_personIO]
go

exec dbo.usp_PersonID_List
go

 

 Output:

dataFetched

 

Get Dependencies

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.


SELECT  
        DB_NAME() AS dbname
        ,  o.type_desc AS referenced_object_type
        , d1.referenced_entity_name
        , d1.referenced_id, 
        STUFF( 
                (
                    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

 

Output:

listObjectDependencies

Disrupt

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.

 


use [tempdb]
go

if object_id('dbo.personIO') is not null
begin

	if exists
	(
		select *
		from   sys.columns tblC
		where   tblC.object_id = object_id('dbo.personIO')
		and    tblC.name = 'dateOfBirth'
	)
	begin

		print 'dropping column dbo.personIO.dateOfBirth ...'

			alter table dbo.personIO
				drop column [dateofBirth]
		print 'dropping column dbo.personIO.dateOfBirth'

	end

end
go


 

Side Effects

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”.

dataFetchedErrors

 

 

Other Relational Databases

In other databases, we can get a list of in-limbo objects by querying system meta-tables.

ORACLE

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.

Workarounds?

Let us check whether there are worthy workarounds …

Refresh object

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.

 

sys.dm_sql_referenced_entities

We are able to determine all the objects and columns that a specific object reference by querying the sys.dm_sql_referenced_entities view.

 

use tempdb
go

SELECT 
         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');

GO

 
 
 

Output:

vw_personIOR_references

 

Once we drop the dateofBirth column, we run afoul a bit.

 

Output Textual:

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 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.

 

Output Image:

Msg207AndMsg2020

Commendation

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
http://blogs.msdn.com/b/sqltips/archive/2005/07/05/435882.aspx

 

I have posted  a slightly modified version of Umachandar’s beautiful and eternally useful code @ https://github.com/DanielAdeniji/IdentifyObjectColumnDependency.

 

Summary

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 .

 

References

 

Object Catalog Views

 

 

Trouble-shooting & Identifying SQL Dependencies

 

Others Database – DB/2

 

3rd Party Tooling