May 22, 2009 at 11:41 pm
Hi
I have a DB with Compatibility level as 2000 (say CL 2000).
The difference between CL 2000 and CL 2005 while dropping a table with constraints is : CL 2000 drops table without checking for constraints but CL 2005 wont drops the table as constraints exist.
Since my DB's CL is 2000, i need to find all the junk constraints in the DB that has no tables in DB. Like, i need to find the orphan constraints in the DB and delete.
Thanks in advance.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
May 26, 2009 at 2:41 am
Why do you think that SQL Server 2000 doesn't delete the constraint? Below is a small script that I just ran on SQL Server 2000 to check it and it did delete the constraints. Is there any documentation that you have that says that SQL Server 2000 doesn't delete the constraints when you drop the table? If not do you have a script to reproduce it? Could it be that someone edited the sysobjects table in your database manually and deleted records from the table instead of dropping the table?
create database test
go
use test
--Get count of records in sysobjects before creating table with constraints
select count(*) from sysobjects
create table MyTable (i int not null primary key,
j int default(1),
c char(20),
z int unique,
w int)
go
alter table MyTable add constraint CK_MyTable_w check (w > 100)
go
select count(*) from sysobjects
go
drop table MyTable
go
select count(*) from sysobjects
--cleanup
use master
go
drop database test
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 26, 2009 at 2:55 am
Run DBCC CheckCatalog or, if you're on SQL 2005, just run CheckDB as it includes CheckCatalog from 2005 onwards.
If you drop a table the constraints will be dropped as well. Only way to get 'orphaned' constraints is to manually delete from sysobjects (on SQL 2000). Obviously that's a very bad thing to do and, if someone has done that and left orphaned constraints, there's no easy way to drop them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply