Solved

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

Posted on 2008-10-23
7
528 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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot resolve the collation - tempdb..#Carriers 3 50
Need help creating a stored procedure 4 56
Query - which index being used? 2 47
Stored procedure 4 32
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore 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.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

21 Experts available now in Live!

Get 1:1 Help Now