Solved

How to find (and recompile) all invalid (dependant) database objects caused by data structure change?

Posted on 2008-10-23
7
525 Views
Last Modified: 2008-10-23
Hello,

I've worked with Oracle for a long time and just come to MS SQL Server 2005 recently.

We are going to make a lot of structural changes to our database, it is a large finance system with literally over 1000 tables and views.

When I worked with Oracle, when a referenced object (e.g. a table) is changed, all the objects that depend on it, like views, procedures or triggers, are marked invalid, and in the development tool I used (PL/SQL Developer), there's an option to recompile all these invalid objects. Even if due to the structural change a recompile doesn't succeed, it's easy to keep track what objects we should look at.

Could someone shed a light, what's the best way to go about it for MS SQL Server 2005? I haven't found anything in the Management Studio, is there any specific development tool supporting this?

Many thanks in advance!

Billy
0
Comment
Question by:Bidan Zhu
  • 5
  • 2
7 Comments
 
LVL 23

Expert Comment

by:adathelad
Comment Utility
Hi,

Dependent objects do not get marked invalid in SQL Server, and there's no need to recompile to carry on using them (ok, if you've make structural changes to tables such as renaming/removing columns then you will get errors when you try to use stored procedures etc that reference those.

A quick way to tell which objects depend on a given table is using the sp_depends system sproc.

e.g. in your database
EXECUTE sp_depends 'MyTable'

http://msdn.microsoft.com/en-us/library/ms189487(SQL.90).aspx
0
 
LVL 23

Expert Comment

by:adathelad
Comment Utility
You'd have to run that for each table, which may not be what you want....so you could go direct to the sysdepends system table.

e.g.
SELECT DISTINCT o2.name 'ObjectDependentOn', o.name 'ObjectWithDependency'
FROM sysdepends d
      JOIN sysobjects o on d.id = o.id
      JOIN sysobjects o2 ON d.depid = o2.id
ORDER BY o2.name
0
 
LVL 23

Expert Comment

by:adathelad
Comment Utility
I think the thing to be careful of is (e.g.) stored procedures that use dynamic SQL -

e.g. sproc a:
EXECUTE('SELECT * FROM SomeTable WHERE SomeField=1')

In these cases, I'm pretty certain that querying the dependencies on "SomeTable" would NOT list this sproc
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Bidan Zhu
Comment Utility
Thanks for the reply!

I think I should maybe rephrase my question - in Oracle, the objects are marked "invalid" only because the underlying objects get changed, there is no saying whether these objects are really "broken".
But after you run "Compile all invalid objects" a few times, the remaining ones that are marked "invalid" are usually really "broken" by the structural change.

That's what I'm trying to find out - is there any way to find out all the "broken" objects in the SQL 2005 database caused by a upgrade script, before I actually try to access the object in production?

I guess it'd probably work if I try to compile everything, and look at the error log, but since this is a huge database, I'd like something more specific.
0
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
Comment Utility
I don't believe there is a tool that will do this - there isn't anything built in to SQL Server.

The only thing I can suggest, is to try to recreate your stored procedures (either DROP + CREATE or use an ALTER). This will fail on the sprocs that are broken.

i.e. I have a script containing DROP + CREATE statements for every sproc in my database which I update as the central copy and can be used to completely refresh the sprocs. When rolling out changes (dev environment), running this script allows me to identify any problems so I can then go in and update the affected sprocs.
0
 

Author Comment

by:Bidan Zhu
Comment Utility
OK, sounds like this should be my approach as well.

So basically, I have to get all the dependant objects into script and just re-run them after the upgrade. Really a pity that there's no way in SQL Server to get the objects that are actually affected by any structural changes.

Thanks for the help!
0
 
LVL 23

Expert Comment

by:adathelad
Comment Utility
No worries!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now