Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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

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
Bidan Zhu
Asked:
Bidan Zhu
  • 5
  • 2
1 Solution
 
adatheladCommented:
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
 
adatheladCommented:
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
 
adatheladCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bidan ZhuAuthor Commented:
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
 
adatheladCommented:
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
 
Bidan ZhuAuthor Commented:
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
 
adatheladCommented:
No worries!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now