x

How to check Invalid Objects in database

I got a database in which I there are several procedures/views/functions that refer to tables that are no more there in the database or refer fields that were dropped. Typically, before dropping a table I would expect to make sure all the referring objects be re-coded. But this is a database that I inherited and this problem was there for a long time. I want to know if there is any quick way to get a list of all the objects are invalid. So that I can fix the issues before some one bumps into one of those invalid or obsolete objects.

Any light shed on this will be greatly appreciated.

more ▼

asked Nov 08 '09 at 12:05 AM in Default

Raghu gravatar image

Raghu
14 1 1 2

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

You can identify broken table and view dependencies by setting showplan on, setting noexec on, and executing each stored procedure (since you are using noexec, you don't need to provide any parameters)

Here is a sample that creates a procedure with a missing dependency, then executes it without supplying parameters to identify the missing dependency.

create procedure tests @bob int as 
select * from missing_table_or_view
go 
set showplan_text on; 
go 
set noexec on 
exec tests 
set noexec off 
go 
set showplan_text off 
go 
drop procedure tests 
go

It identifies the missing dependency with the following error:

Msg 208, Level 16, State 1, Procedure tests, Line 2
Invalid object name 'missing_table_or_view'.

[Edit] I forgot to mention that this will not identify dependencies within dynamic SQL - a problem noted in other answers

more ▼

answered Nov 09 '09 at 04:55 PM

KenJ gravatar image

KenJ
19.1k 1 3 11

Looks like this is the optimal solution to my problem. Easy to do and catches most of the issues (except dynamic SQL which is not possible to detect until runtime, because that is the nature of the beast a Dynamic SQL is :) )
Nov 10 '09 at 11:27 AM Raghu
(comments are locked)
10|1200 characters needed characters left

KenJ's solution above has minor problem. It reports #TEMP tables used in the stored procedures as well. I guess that is still ok. As I understand it is not really possible to get all that I want at single shot and have to work to earn my salary for the day.

Thanks to all for the help. This is what I have finally...

------------------- First Cleanup Operation ---------------- --Redirect the results of the following query to a text file

PRINT 'set showplan_text on;' PRINT 'go' PRINT 'set noexec on' PRINT 'go'

SET NOCOUNT ON

SELECT --'PRINT ''' + name + '''' + CHAR(10) + 'Go' + CHAR(10) + 'EXEC ' + QUOTENAME(SCHEMA_NAME(schema_id))+ '.' + QUOTENAME(name) + CHAR(10) + 'GO' + CHAR(10) FROM sys.objects obj WHERE type In ('P') -- , 'FN', 'TF', 'IF') AND obj.is_ms_shipped = 0 ORDER BY obj.create_date ASC go PRINT 'set noexec off' PRINT 'GO' PRINT 'set showplan_text off' PRINT 'GO'

--COPY the results from the text output to a new query window and again redirect the results to a text file --Inspect the results manually and filter out possible culprit stored procs to get a list of stored procs

--Note that this list would include any #TEMP table used those should be ok and could be excluded from inspection some how ------------------- First Cleanup Operation ----------------

------------------- Second Cleanup Operation ---------------- SELECT 'PRINT ''' + name + '''' + CHAR(10) + 'Go' + CHAR(10) + 'EXEC sp_refreshsqlmodule ' + QUOTENAME(SCHEMA_NAME(schema_id))+ '.' + QUOTENAME(name) + CHAR(10) + 'GO' + CHAR(10) AS refresh_sql FROM sys.objects WHERE type In ('P', 'FN', 'TF', 'IF') go --Do as before ------------------- Second Cleanup Operation ----------------

------------------- Third Cleanup Operation ---------------- SELECT 'PRINT ''' + name + '''' + CHAR(10) + 'Go' + CHAR(10) + 'EXEC sp_refreshview ' + QUOTENAME(SCHEMA_NAME(schema_id) + '.' + name) + CHAR(10) + 'GO' + CHAR(10) AS refresh_sql FROM sys.objects WHERE type = 'V' AND OBJECTPROPERTY(object_id, 'IsSchemaBound') = 0 go --Do as before ------------------- Third Cleanup Operation ----------------

more ▼

answered Nov 10 '09 at 01:06 PM

Raghu gravatar image

Raghu
14 1 1 2

(comments are locked)
10|1200 characters needed characters left

Getting a complete list like that is challenging, especially if Dynamic SQL is used in any of the procedures. You can use sp_depends (or the feature in SSMS which calls it for you and displays it graphically) to get a fairly good idea though.

This article may help somewhat: http://www.mssqltips.com/tip.asp?tip=1294

more ▼

answered Nov 08 '09 at 02:46 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

Thanks for the reply.

sp_depends output does not give any indication of mising objects dependency. However on the SSMS's view dependencies dialog box... it does show the type as "Unresolved Entry" So, I am guessing there is a way to get a list of objects that end up with having "Unresolved Entry" in the graphical display
Nov 09 '09 at 02:48 PM Raghu
(comments are locked)
10|1200 characters needed characters left

That's a touch situation to be in. I'd suggest scripting out the database and attempting to build it in a second location. You'll very quickly find the scripts that won't build due to missing or changed objects. There's no other easy way to do this without involving 3rd party tools.

Assuming you're not on 2000, instead of sp_depends, check out the views sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. They provide much better data than sp_depends.

However, if you do want to get a 3rd party tool, I'd suggest Red Gate's SQL Dependency Tracker. It does a great job of walking all the objects.

more ▼

answered Nov 08 '09 at 09:41 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Bear in mind, also, that because a script compiles doesn't mean it will run. There is no tool out there (as far as I know) that will look at a query involving dynamic SQL, iterate through all the possible execution paths, and work out what the dependencies might be. Especially seeing as it's perfectly possible to write dynamic SQL which would query a table whose name was stored in another table.

Another potential issue is with non-qualified objects. SELECT * FROM myTable can mean different things for different users depending on whether their default schemas are different.

more ▼

answered Nov 08 '09 at 09:55 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x7

asked: Nov 08 '09 at 12:05 AM

Seen: 6381 times

Last Updated: Nov 08 '09 at 12:30 AM