Excerpt
DBA has a number of duties that are primarily targeted at supporting of database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify the data consistency; however, in case they need to find an invalid object in a database schema, some difficulties may occur.
ORACLE for instance, allows you to get a list of invalid objects:
SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'
SQL Server doesn’t allow to do that directly. In most cases, you need to execute a script to see that an object is invalid. This is very inconvenient…
So let’s create a script that will search invalid objects:
SELECT
obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
, obj_type = o.type_desc
, d.referenced_database_name
, d.referenced_schema_name
, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.[object_id]
WHERE d.is_ambiguous = 0
AND d.referenced_id IS NULL
AND d.referenced_server_name IS NULL -- ignore objects from Linked server
AND CASE d.referenced_class -- if doesn’t exist
WHEN 1 -- object
THEN OBJECT_ID(
ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' +
ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' +
QUOTENAME(d.referenced_entity_name))
WHEN 6 – or user datatype
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name)
WHEN 10 -- or XML schema
THEN (
SELECT 1 FROM sys.xml_schema_collections x
WHERE x.name = d.referenced_entity_name
AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
)
END IS NULL
The script is useful for the primary analysis. However, there are some gaps in it. The main problem is that the script does not show objects with invalid columns or parameters.
CREATE VIEW dbo.vw_View
AS SELECT ID = 1
GO
CREATE PROCEDURE dbo.usp_Procedure
AS BEGIN
SELECT ID FROM dbo.vw_View
END
GO
ALTER VIEW dbo.vw_View
AS SELECT New_ID = 1
GO