新手求助~~

Question

原来有个表被人改了。。
现在不能在企业管理器中设计该表
打开就会报错，然后就退出企业管理器了
这个表中有100多个字段
也不能删除该表
显示为该对象正由一个FOREIGN KEY约束引用
求解决方法数据库为SQL SERVER 2000

Answer 1

找到约束,删除之.
--SQL SERVER 2000中各表外键名，主键名的获取SELECT 
  外键表ID   = b.fkeyid ,
  外键表名称 = object_name(b.fkeyid) ,
  外键列ID   = b.fkey ,
  外键列名   = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,
  主键表ID   = b.rkeyid ,
  主键表名   = object_name(b.rkeyid) ,
  主键列ID   = b.rkey ,
  主键列名   = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,
  级联更新   = ObjectProperty(a.id,'CnstIsUpdateCascade') ,
  级联删除   = ObjectProperty(a.id,'CnstIsDeleteCascade') 
FROM sysobjects a 
  join sysforeignkeys b on a.id = b.constid 
  join sysobjects c on a.parent_obj = c.id 
where a.xtype = 'f' AND c.xtype = 'U' /*
--以下为查询SQL SERVER 2000中自带库PUBS的结果
外键表ID   外键表名称   外键列ID  外键列名   主键表ID    主键表名    主键列ID 主键列名  级联更新 级联删除 
---------- ------------ --------- ---------  ----------- ----------- -------- --------- -------- ---------
2121058592 titles       4         pub_id     2057058364  publishers  1        pub_id    0        0
53575229   titleauthor  1         au_id      1977058079  authors     1        au_id     0        0
53575229   titleauthor  2         title_id   2121058592  titles      1        title_id  0        0
149575571  sales        1         stor_id    117575457   stores      1        stor_id   0        0
149575571  sales        6         title_id   2121058592  titles      1        title_id  0        0
213575799  roysched     1         title_id   2121058592  titles      1        title_id  0        0
245575913  discounts    2         stor_id    117575457   stores      1        stor_id   0        0
357576312  pub_info     1         pub_id     2057058364  publishers  1        pub_id    0        0
405576483  employee     5         job_id     277576027   jobs        1        job_id    0        0
405576483  employee     7         pub_id     2057058364  publishers  1        pub_id    0        0（所影响的行数为 10 行）
*/

Answer 2

查询一个表的所有外键
SELECT 主键列ID=b.rkey 
    ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) 
    ,外键表ID=b.fkeyid 
    ,外键表名称=object_name(b.fkeyid) 
    ,外键列ID=b.fkey 
    ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) 
    ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade') 
    ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade') 
FROM sysobjects a 
    join sysforeignkeys b on a.id=b.constid 
    join sysobjects c on a.parent_obj=c.id 
where a.xtype='f' AND c.xtype='U' 
    and object_name(b.rkeyid)='titles'

Answer 3

/*----------------------------------------------------------------
-- Author  :feixianxxx(poofly)
-- Date    :2010-04-22 19:03:14
-- Version:
--      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Mar 29 2009 10:27:29 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
-- Content：查询指定表为主表的从表信息 
----------------------------------------------------------------*/--测试
create table test_1(id_1 int primary key)
create table test(id int primary key ,id_1 int references test_1(id_1) )
create table test_2(id_2 int references test(id))
create table test_3(id_2 int references test(id))
create table test_4(id_2 int references test(id))
--查询出指定主表的从表
declare @tabname sysname;
set @tabname='test'
select OBJECT_NAME(f.parent_object_id) 从表名,
@tabname  主表名,
COL_NAME(OBJECT_ID(@tabname),f.parent_column_id) 从表外键列名,
fk.name  外键名
from sys.foreign_key_columns f join sys.foreign_keys fk on f.parent_object_id=fk.parent_object_id
where f.referenced_object_id=OBJECT_ID(@tabname) and fk.referenced_object_id=OBJECT_ID(@tabname)
/*
从表名                                                                                                                              主表名                                                                                                                              从表外键列名                                                                                                                           外键名
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
test_2                                                                                                                           test                                                                                                                             id                                                                                                                               FK__test_2__id_2__4316F928
test_3                                                                                                                           test                                                                                                                             id                                                                                                                               FK__test_3__id_2__44FF419A
test_4                                                                                                                           test                                                                                                                             id                                                                                                                               FK__test_4__id_2__46E78A0C(3 行受影响)*/