More on dependecies

It's not possible to drop tables which violate FOREIGN KEY
CONSTRAINTS. SQL Server, however, is completely blase about dropping
tables that are referenced by either sprocs or views.
Short of writing unit tests for every stored procedure and view are
there any tools to test integrity? I suspect that someone will ask if
I've considered the "Tools -> Check Integrity" option. So where is it?have you consider building your databases from source code. This will find a
ll breakages.
check out www.dbghost.com|||kofisarfo@.gmail.com (Kofi) wrote in message news:<5c157557.0405130510.4c98fce@.posting.google
.com>...
> It's not possible to drop tables which violate FOREIGN KEY
> CONSTRAINTS. SQL Server, however, is completely blase about dropping
> tables that are referenced by either sprocs or views.
> Short of writing unit tests for every stored procedure and view are
> there any tools to test integrity? I suspect that someone will ask if
> I've considered the "Tools -> Check Integrity" option. So where is it?
Have a look at sp_depends, which uses the sysdepends system table.
However, dependency metadata in MSSQL isn't completely reliable, due
to issues like deferred name resolution (you can create a proc which
references a table you haven't created yet), and dynamic SQL (the
table name may not even be in the procedure). And indeed there may be
SQL code hidden in client applications also.
You can create views WITH SCHEMABINDING, to prevent a table referenced
by the view from being dropped, but there's no equivalent for stored
procedures.
Finally, it could be argued that creating unit tests for all your code
is a good idea anyway.
Simon|||I thought of that only moments after posting and that's exactly what I
did in fact do.
I found out about DBGhost in the process though so it was still worth
asking
KS
http://www.single-blend.net/dotnet/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Indeed. Just when I thought I'd nailed all dependencies I found some
implicit dependencies in EXEC statements run within some stored
procedures. They won't be picked up either.
"sp_depends" ain't all that. There's "sp_MSdependencies" which goes
beyond first level dependencies.
http://www.microsoft.com/sql/techin...splayingdepende
ncies.asp
Unit tests would be ideal but there are just way too many units and I
almost certainly wouldn't be able to get the go-ahead.
Cheers
KS
http://www.single-blend.net/dotnet/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!