How to Identify Views with Broken Dependencies in SQL Server

How do I identify views with broken dependencies in SQL Server?

DECLARE @stmt nvarchar(max) = ''
DECLARE @vw_schema  NVARCHAR(255)
DECLARE @vw_name varchar(255)

IF OBJECT_ID('tempdb..#badViews') IS NOT NULL DROP TABLE #badViews
IF OBJECT_ID('tempdb..#nulldata') IS NOT NULL DROP TABLE #nulldata

CREATE TABLE #badViews 
(    
    [schema]  NVARCHAR(255),
    name VARCHAR(255),
    error NVARCHAR(MAX) 
)

CREATE TABLE #nullData
(  
    null_data varchar(1)
)

DECLARE tbl_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT name, SCHEMA_NAME(schema_id) AS [schema]
        FROM sys.objects 
        WHERE type='v'

OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor
INTO @vw_name, @vw_schema

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @stmt = 'SELECT TOP 1 * FROM [' + @vw_schema + N'].[' + @vw_name + ']'
    BEGIN TRY
        INSERT INTO #nullData EXECUTE sp_executesql @stmt
    END TRY 

    BEGIN CATCH
        IF ERROR_NUMBER() != 213 BEGIN
            INSERT INTO #badViews (name, [schema], error) values (@vw_name, @vw_schema, ERROR_MESSAGE())     
        END
    END CATCH

    FETCH NEXT FROM tbl_cursor 
    INTO @vw_name, @vw_schema
END

CLOSE tbl_cursor -- free the memory
DEALLOCATE tbl_cursor

SELECT * FROM #badViews

DROP TABLE #badViews
DROP TABLE #nullData

Update 2017

Updated the answer as per @robyaw's answer.

I've also fixed a bug in it for the computed values in the select statements. It seems SELECT TOP 1 NULL from vwTest doesn't throw an error when vwTest contains a column like let's say 1/0 as [Col1], but SELECT TOP 1 * from vwTest it does throw an exception.

Update 2018
Fix false positives for views and or schema that contain special characters in their name. Thanks to @LucasAyala

Find broken objects in SQL Server

I'm actually using sys.refreshmodule procedure now wrapped in a powershell script with the SQL Server Powershell add ins.

This works better because this handy little sys function gets rid of the CREATE vs ALTER stuff. Some other answers here use this approach as well, but I prefer this one that's wrapped in Powershell and maybe some will find it useful.

$server = "YourDBServer"
cls
Import-Module “sqlps” -DisableNameChecking

$databases = Invoke-Sqlcmd -Query "select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')" -ServerInstance $server
foreach ($db in $databases) {
    $dbName = $db.name
    $procedures = Invoke-Sqlcmd -Query "select SCHEMA_NAME(schema_id) as [schema], name from $dbName.sys.procedures" -ServerInstance $server
    foreach ($proc in $procedures) {
        if ($schema) {
            $shortName = $proc.schema + "." + $proc.name
            $procName =  $db.name + "." + $shortName
            try {
                $result = Invoke-Sqlcmd -Database $dbName -Query "sys.sp_refreshsqlmodule '$shortName'" -ServerInstance $server -ErrorAction Stop
                Write-Host "SUCCESS|$procName"
            }
            catch {
                $msg = $_.Exception.Message.Replace([Environment]::NewLine, ",")
                Write-Host "FAILED|$procName|$msg" -ForegroundColor Yellow
            }
        }
    }
}

How to get column-level dependencies in a view

This solution could answer your question only partially. It won't work for columns that are expressions.

You could use sys.dm_exec_describe_first_result_set to get column information:

@include_browse_information

If set to 1, each query is analyzed as if it has a FOR BROWSE option on the query. Additional key columns and source table information are returned.

CREATE TABLE txu(id INT, first_name VARCHAR(10), last_name VARCHAR(10));
CREATE TABLE txd(id INT, id_fk INT, address VARCHAR(100));

CREATE VIEW v_txu
AS
SELECT t.id AS PK_id,
       t.first_name  AS name,
       d.address,
       t.first_name + t.last_name AS name_full
FROM txu t
JOIN txd d
  ON t.id = d.id_fk

Main query:

SELECT name, source_database, source_schema,
      source_table, source_column 
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM v_txu', null, 1) ;  

Output:

+-----------+--------------------+---------------+--------------+---------------+
|   name    |   source_database  | source_schema | source_table | source_column |
+-----------+--------------------+---------------+--------------+---------------+
| PK_id     | fiddle_0f9d47226c4 | dbo           | txu          | id            |
| name      | fiddle_0f9d47226c4 | dbo           | txu          | first_name    |
| address   | fiddle_0f9d47226c4 | dbo           | txd          | address       |
| name_full | null               | null          | null         | null          |
+-----------+--------------------+---------------+--------------+---------------+

DBFiddleDemo

Stored procedure dependencies in SQL Server Management Studio

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

SQL Server 2005 search views for certain database objects

You can use sys.sql_dependencies:

select object_name(object_id),* 
from sys.sql_dependencies
where referenced_major_id = object_id('<tablename>');

This will list all objects that depend on your table, you can restrict this to views by joining against sys.views:

select v.* 
from sys.sql_dependencies d
join sys.views v on d.object_id = v.object_id
where referenced_major_id = object_id('<tablename>');

Find DISTINCT Missing SQL Dependencies

GROUP BY the actual field names not the alias.

SELECT TOP (100) PERCENT
    QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) AS [this Object...],
        o.type_desc,
    ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name) AS [... depends ON this missing entity name]
    ,sed.referenced_class_desc
    ,case when o.type_desc in( 'SQL_STORED_PROCEDURE' ,'SQL_SCALAR_FUNCTION' ,'SQL_TRIGGER' ,'VIEW')
          then 'EXEC sys.sp_refreshsqlmodule ''' + QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.' + QuoteName(OBJECT_NAME(referencing_id)) + ''';'
          else null
       end as [Refresh SQL Module command]
FROM sys.sql_expression_dependencies as sed
LEFT JOIN sys.objects o
            ON sed.referencing_id=o.object_id
WHERE (is_ambiguous = 0)
    AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
    + ISNULL(QuoteName(referenced_database_name) + '.', '')
    + ISNULL(QuoteName(referenced_schema_name) + '.', '')
    + QuoteName(referenced_entity_name)) IS NULL)
    AND NOT EXISTS
       (SELECT * 
        FROM sys.types 
        WHERE types.name = referenced_entity_name 
        AND types.schema_id = ISNULL(SCHEMA_ID(referenced_schema_name), SCHEMA_ID('dbo'))
       )
GROUP BY ISNULL(QuoteName(referenced_server_name) + '.', '') + ISNULL(QuoteName(referenced_database_name) + '.', '') + ISNULL(QuoteName(referenced_schema_name) + '.', '') + QuoteName(referenced_entity_name)
ORDER BY [this Object...],
[... depends ON this missing entity name]
go

How do I find all stored procedures that insert, update, or delete records?

Call sp_refreshsqlmodule on all non-schema bound stored procedures:

DECLARE @template AS varchar(max)
SET @template = 'PRINT ''{OBJECT_NAME}''
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''

'

DECLARE @sql AS varchar(max)

SELECT  @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}',
                                          QUOTENAME(ROUTINE_SCHEMA) + '.'
                                          + QUOTENAME(ROUTINE_NAME))
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                 + QUOTENAME(ROUTINE_NAME)),
                       N'IsSchemaBound') IS NULL
        OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                    + QUOTENAME(ROUTINE_NAME)),
                          N'IsSchemaBound') = 0

        EXEC (
              @sql
            )

---

Related Topics