Microsoft – SQLServer – Object – IsValid

Microsoft – SQLServer – Object – IsValid

Database Programming is the ultimate design as you go.

It is very easy to DDL (Create \ Modify \ Drop) Objects.

You need it, just do it.

With all that quickness, one needs to be careful to ensure that so called “Database Programmables” ( Views , Stored Procedures and Functions) are consitent.

Oracle has a very good tooling in this area:

Invalid Stored Procedures

http://searchoracle.techtarget.com/answer/Invalid-stored-procedures

There may be some objects that will not compile. For instance, the stored procedure may refer to a table that has been dropped. The best way to find out why the stored procedure will not compile correctly is to figure out which object is invalid with the following query in SQL*Plus:


SELECT owner, object_name, object_type
FROM   dba_objects
WHERE  status='INVALID';

Next, sign on to the schema that owns the stored procedure. Then issue the following:

 
ALTER PROCEDURE procedure_name COMPILE;

Here is a poor man’s MS SQL Server Version:

  select

         SERVERPROPERTY('serverName') 
            as serverName

       , DB_NAME() as databaseName

       , OBJECT_NAME(tblDep.referencing_id)              as objectName

       , tblDep.referencing_id

       , tblDep.referencing_class_desc

       , tblDep.is_schema_bound_reference

       , tblDep.referenced_id as referencedID

       , isNull(tblDep.referenced_database_name, DB_NAME()) 
             as refDB

       , tblDep.referenced_schema_name

       , tblDep.referenced_entity_name

       , tblDep.is_caller_dependent

       , tblDep.is_ambiguous

from sys.sql_expression_dependencies tblDep

where referenced_id is null

and OBJECT_ID(
     QUOTENAME(ISNULL(tblDep.referenced_database_name, db_name()))
   + '.' + QUOTENAME(ISNULL(tblDep.referenced_schema_name, 'dbo'))
   + '.' + QUOTENAME(tblDep.referenced_entity_name)
   ) is null

order by

       SERVERPROPERTY('serverName')
     , DB_NAME()
     , OBJECT_NAME(tblDep.referencing_id)
     , tblDep.referenced_entity_name

There is good possibility that you will get false positives in the following cases:

  • Objects that are not fully referenced – That is objects that are not preceded with schema names (eg sales as opposed to dbo.sales)

Note that you might also get false positives for objects that references:

  • Table variables
  • Temporary Tables

References:


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s