[quoted text, click to view]

You can certainly create a script (in perl or VBScript for example) that
tries to alter sprocs, and take back the errors if there are. This is easy
using DMO. That would check only a part of what you want though. You cannot
check non existing tables and columns on non-existing tables, due to
deferred name resolution.

You can find at http://www.babaluga.com/cwick.pl?SQL_Server examples of
perl code I did using DMO.
If you really want to make a complete tool, and if you happen to like/know
Perl, I recommend "Real World SQL Server Administration with Perl" by
Linchi Shea at Apress. There are modules to parse SQL code, and maybe
achieve after some work what you want. Look at the book here:
http://www.apress.com/book/bookDisplay.html?bID=171 , you might be able to
download some code.

hth

Veerle
Well , for views you can use sp_refreshview stored procedure
Look at IF OBJECT_ID("SomeTable") IS NULL you can determine whether or not
the table was droped.
Perhaps you want to do some search on internet to find any third part tools
to solve your poblem.


[quoted text, click to view]

For views, you can use sp_refreshview stored procedure,
for stored procedures, you can try to recreate them if setting permissions
is not a problem (the same goes for triggers) and see which ones generate
errors.




[quoted text, click to view]

[quoted text, click to view]
If you are not going to write a BIG app that'll parse all your db objects,
(or buy one) you probably have to execute them.

[quoted text, click to view]
You can use SET PARSEONLY ON to avoid that.


--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


[quoted text, click to view]

I wrote a program in java that retrieves all the views, stored
procedures, triggers and user defined functions from the sysobjects
table. Then I collect the create statement for each of these from the
syscomments table. I change the word CREATE in the word ALTER and then
try to execute it within a transaction that is rolled back. If the
execution fails, my program lists the view, stored procedure, trigger
or function as invalid.

This already gets me pretty far, although it detects not all errors.
Eg: a procedure call with the wrong number of arguments doesn't
generate an error. But as I said, most problems are detected, so I
guess the program will turn out to be pretty usefull. I doubt that
most of the tools on the market will do better than this...

Here's what I'd do:

1.) I use a stored procedure called sp_grep. It'll search through the
syscomments table and look for references to your target string. You
can get it here:

http://examples.oreilly.com/wintrnssql/

(d/l the file called "example.zip" and look for sp_grep)

I would use this in a cursor to:

2.) Loop through all the objects in the sysobjects table and do an
sp_grep
on each object's name. This will create a list of all stored procs,
user functions, views, etc. that contain a reference to this object.
Since you're looping through sysobjects, you can filter out any object
types you don't want to include (maybe, for instance, you're not
interested in stored procedures that call the system tables).

3.) Insert this list into a temp table or some other suitable object.
If you're concerned about the table size, then you can use another
table (perhaps a memory table) to get the raw results, then insert
into the temp table where memory table entry is not in the temp table.

4.) Once this sucker has finshed executing (I suspect it'd take some
time to run, i.e. maybe 10 minutes or more), you have a table of all
the objects that are getting called by stored procedures, views, user
functions, etc.

5.) Finally, do something like "select * from sysobjects where name
not in (select distinct name from #tempUsed)". This will give you a
list of objects in your database that aren't being called by stored
procs, user functions, etc. You may want to exclude system objects
just so there's no confusion (system objects have their own xtypes, I
believe, for all the different types of objects).



This is a very rough method and not very fast, but if I had to do a
quick-and-dirty check on my database, that's how I'd do it. I'd be
happy to post example code (I can't post the sp_grep code, though --
but you can download that yourself) if any of this is unclear.

-Andrew-

[quoted text, click to view]