Solved

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

Posted on 2008-10-23
7
536 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 22784177
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
ID: 22784193
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
ID: 22784201
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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:Bidan Zhu
ID: 22784281
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
ID: 22784354
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
ID: 22784711
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
ID: 22784746
No worries!
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

756 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