Find broken objects in SQL Server
Is there a tool that will find all objects in SQL Server (functions, procs, views) th开发者_运维技巧at cannot possibly work because they refer to objects that don't exist?
You may be interested in checking out the following articles:
- Michael J. Swart: Find Missing SQL Dependencies
- eggheadcafe.com: Find broken stuff
You can test Michael J. Swart's solution as follows:
CREATE PROCEDURE proc_bad AS
SELECT col FROM nonexisting_table
GO
SELECT
OBJECT_NAME(referencing_id) AS [this sproc or VIEW...],
referenced_entity_name AS [... depends ON this missing entity name]
FROM
sys.sql_expression_dependencies
WHERE
is_ambiguous = 0
AND OBJECT_ID(referenced_entity_name) IS NULL
ORDER BY
OBJECT_NAME(referencing_id), referenced_entity_name;
Which returns:
+------------------------+------------------------------------------+
| this sproc or VIEW... | ... depends ON this missing entity name |
|------------------------+------------------------------------------|
| proc_bad | nonexisting_table |
+------------------------+------------------------------------------+
The two previous solutions here are interesting, but both failed on my test databases.
The original Michael J Swart script produced a huge number of false positives for me, far too many to wade through. Rick V.'s solution here was better - the only false positives it gave were for cross-database references.
There's a comment on the Michael J Swart article by RaduSun which gives a solution that I can't yet break though! This is it, tweaked mildly for readability and my purposes, but credit to RaduSun for the logic.
SELECT
QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.'
+ QuoteName(OBJECT_NAME(referencing_id)) AS ProblemObject,
o.type_desc,
ISNULL(QuoteName(referenced_server_name) + '.', '')
+ ISNULL(QuoteName(referenced_database_name) + '.', '')
+ ISNULL(QuoteName(referenced_schema_name) + '.', '')
+ QuoteName(referenced_entity_name) AS MissingReferencedObject
FROM
sys.sql_expression_dependencies 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)
ORDER BY
ProblemObject,
MissingReferencedObject
First querywill give you broken objects name includesStored Procedure,View,Scalar function,DML trigger,Table-valued-functiontype
/*
/////////////
////ERROR////
/////////////
All error will be listed if object is broken
*/
DECLARE @AllObjectName TABLE (
OrdinalNo INT IDENTITY
,ObjectName NVARCHAR(MAX)
,ObjectType NVARCHAR(MAX)
,ErrorMessage NVARCHAR(MAX)
)
INSERT INTO @AllObjectName (
ObjectName
,ObjectType
)
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + NAME + ']' ObjectName
,CASE [TYPE]
WHEN 'P'
THEN 'Stored Procedure'
WHEN 'V'
THEN 'View'
WHEN 'FN'
THEN 'Scalar function'
WHEN 'TR'
THEN 'DML trigger'
WHEN 'TF'
THEN 'Table-valued-function'
ELSE 'Unknown Type'
END
FROM sys.objects
WHERE [TYPE] IN (
'P'
,'V'
,'FN'
,'TR'
,'TF'
)
ORDER BY NAME
DECLARE @i INT = 1
DECLARE @RowCount INT = (
SELECT count(1)
FROM @AllObjectName
)
DECLARE @ObjectName VARCHAR(MAX)
WHILE @i <= @RowCount
BEGIN
BEGIN TRY
SET @ObjectName = (
SELECT ObjectName
FROM @AllObjectName
WHERE OrdinalNo = @i
)
EXEC sys.sp_refreshsqlmodule @ObjectName
END TRY
BEGIN CATCH
DECLARE @message VARCHAR(4000)
,@xstate INT;
SELECT @message = ERROR_MESSAGE()
,@xstate = XACT_STATE();
IF @xstate = - 1
ROLLBACK;
UPDATE @AllObjectName
SET ErrorMessage = @message
WHERE OrdinalNo = @i
END CATCH
SET @i = @i + 1
END
SELECT ObjectName
,ObjectType
,ErrorMessage
FROM @AllObjectName
WHERE ErrorMessage IS NOT NULL
And the
below onesearch for unresolved references .. Generally which treated aswarning, is still may causeerrorsometime
/*
/////////////
///Warning///
/////////////
Here all warning will come if object reference is not stated properly
*/
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
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'))
)
ORDER BY [this Object...]
,[... depends ON this missing entity name]
Thanks @SQLMonger .. for providing me the clue to make the
First querywhich was my actual requirement
Red Gate Software's SQL Prompt 5 has a Find Invalid Objects feature that might be useful in this situation. The tool goes through the database finding objects that will give an error when executed, which sounds exactly what you want.
You can download a 14-day trial for free, so you can give it a try and see if it helps.
Paul Stephenson
SQL Prompt Project Manager
Red Gate Software
As of SQL Server 2008, a much simpler method is here:
SELECT OBJECT_NAME(referencing_id) AS 'object making reference' ,
referenced_class_desc ,
referenced_schema_name ,
referenced_entity_name AS 'object name referenced' ,
( SELECT object_id
FROM sys.objects
WHERE name = [referenced_entity_name]
) AS 'Object Found?'
FROM sys.sql_expression_dependencies e
LEFT JOIN sys.tables t ON e.referenced_entity_name = t.name;
As mentioned in the source article (Microsoft MSDN Article on Finding Missing Dependencies), "A 'NULL' value in the 'Object Found?' column indicates the object was not found in sys.objects."
Example output:
╔═══════════════════════════════════════════════╦═══════════════════════╦════════════════════════╦═══════════════════════════════════════╦═══════════════╗
║ object making reference ║ referenced_class_desc ║ referenced_schema_name ║ object name referenced ║ Object Found? ║
╠═══════════════════════════════════════════════╬═══════════════════════╬════════════════════════╬═══════════════════════════════════════╬═══════════════╣
║ usvConversationsWithoutServerNotices ║ OBJECT_OR_COLUMN ║ dbo ║ ConversationLinesWithID ║ NULL ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN ║ dbo ║ ConversationLinesWithID ║ NULL ║
║ usvFormattedConversationLines_WithSpeakerName ║ OBJECT_OR_COLUMN ║ dbo ║ FormattedConversationLines_Cached ║ NULL ║
║ udpCheckForDuplicates ║ OBJECT_OR_COLUMN ║ dbo ║ FormattedConversationLines_WithChatID ║ NULL ║
║ usvFormattedConversationsCombined ║ OBJECT_OR_COLUMN ║ dbo ║ GROUP_CONCAT_D ║ 178099675 ║
║ usvSequenceCrossValidationSetStudents ║ OBJECT_OR_COLUMN ║ dbo ║ usvSequenceCrossValidationSet ║ 1406628054 ║
╚═══════════════════════════════════════════════╩═══════════════════════╩════════════════════════╩═══════════════════════════════════════╩═══════════════╝
If you get an error that says the subquery returned more than one value, then you have more than one object with the name equaling the [referenced_entity_name], and you will need the subquery to be more specific by adding another where clause.
You can get more information by checking sys.objects, like this:
SELECT *
FROM sys.objects
WHERE name = [referenced_entity_name]
If that information alone isn't helpful enough to figure out how to distinguish your multiple results, you may need to join sys.objects to one of the other metadata views (which are mostly documented here: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql?view=sql-server-ver15 ) or to sys.schemas (documented here: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/schemas-catalog-views-sys-schemas?view=sql-server-ver15 ) to get more information.
/*
modified version of script from http://michaeljswart.com/2009/12/find-missing-sql-dependencies/
Added columns for object types & generated refresh module command...
filter out user-define types: http://stackoverflow.com/questions/2330521/find-broken-objects-in-sql-server
*/
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'))
)
ORDER BY [this Object...],
[... depends ON this missing entity name]
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
}
}
}
}
I wrote a script some years ago that will find Stored Procedures that won't compile by pulling the text of the proc and attempting to recompile it with a try/catch block. It's pretty simple and effective at finding at least procedures that can be dropped. You could easily expand it for views.
Note that you should only run this against a DEV or TEST environment since it's actually attempting to recompile the procedures.
SET NOCOUNT ON
DECLARE @ProcedureName VARCHAR(2048)
DECLARE @ProcedureBody VARCHAR(MAX)
DECLARE @RoutineName varchar(500)
DECLARE procCursor CURSOR STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT
--TOP 1
SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
OBJECT_DEFINITION(o.[object_id]) AS ProcedureBody
FROM sys.objects AS o
WHERE o.[type] = 'P'
ORDER BY o.[name]
OPEN procCursor
FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
WHILE @@FETCH_STATUS = 0
BEGIN
-- Might have to play with this logic if you don't have discipline in your create statements
SET @ProcedureBody = REPLACE(@ProcedureBody, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
BEGIN TRY
EXECUTE(@ProcedureBody)
PRINT @ProcedureName + ' -- Succeeded'
END TRY
BEGIN CATCH
PRINT @ProcedureName + ' -- Failed: ' + ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM procCursor INTO @ProcedureName, @ProcedureBody
END
CLOSE procCursor
DEALLOCATE procCursor
https://brettwgreen.wordpress.com/2012/12/04/find-stored-procedures-that-wont-compile/
Your best bet is to start using a tool like Visual Studio Database Edition. It's role is to manage a database schema. One of the many things it will do is to throw an error when you attempt to build the database project and it contains broken objects. It will of course do much more than this. The tool is free to any user of Visual Studio Team Suite or Visual Studio Developer Edition.
Note the query in this thread finds missing objects, not invalid ones.
SQL Server doesn't find a referencing object is invalid until you execute it.
Enhancement to that query to handle objects in other schemas as well as types:
SELECT
'[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']'
AS [this sproc, UDF or VIEW...],
isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']'
AS [... depends ON this missing entity name]
FROM
sys.sql_expression_dependencies
WHERE
is_ambiguous = 0 AND
(
(
[referenced_class_desc] = 'TYPE' and
TYPE_ID(
isnull('[' + referenced_schema_name + '].', '') +
'[' + referenced_entity_name + ']'
) IS NULL
) or
(
[referenced_class_desc] <> 'TYPE' and
OBJECT_ID(
isnull('[' + referenced_schema_name + '].', '') +
'[' + referenced_entity_name + ']'
) IS NULL
)
)
ORDER BY
'[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']',
isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']'
create table #BrokenObjects (Name nvarchar(500), Error nvarchar(max))
select * into #objects from(
select name from sys.views
union select name from sys.procedures
union select name from sys.tables
)x
declare @name nvarchar(500),@err nvarchar(max)
while exists(select top 1 * from #objects)
begin
select top 1 @name = name from #objects
begin try
EXEC sys.sp_refreshsqlmodule @name
end try
begin catch
select @err = ERROR_MESSAGE()
insert into #BrokenObjects (name,error) values (@name,@err)
end catch
delete from #objects
where name = @name
end
drop table #objects
select * from #BrokenObjects
where Error not like 'Could not find object % or you do not have permission.'
drop table #BrokenObjects
加载中,请稍侯......
精彩评论