Solved

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

Posted on 2008-10-23
7
532 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL help 5 55
Why do I get extra rows when I do inner join? 12 39
Problem with SqlConnection 4 171
Selection from table2 where criteria for table1 10 46
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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