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