删除外键的问题 ,在线等,解决立马给分

[caitian6]

数据库为sybase, 因为哪边人气太少,所以转到这来问
表名为 ts_front
外键字段为 farwsid
不知道外键名

怎么删除外键? 在线等,解决立马给分

[水族杰纶]

sybase
不是太懂
应该先到系统表里查到外键名
然后删除

[caitian6]

我就不知道如何查到外键名
多谢楼上,继续等答案

[王向飞]

--查询外键约束
select FK_Name as [外键名],Parent_Tab_Name as [外键表],
    [外键列]=stuff((select '，'+[Parent_Col_Name] from (
        select    FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
                Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
        from sys.foreign_keys FK
        inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
        inner join sys.objects Parent_Tab ON  Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
        inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id 
                                            and  Col.parent_column_id = Parent_Col.column_id
        inner join sys.objects Referenced_Tab ON  Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
        inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id 
                                            and  Col.referenced_column_id = Referenced_Col.column_id
    )t where FK_Name=tb.FK_Name  and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name   for xml path('')), 1, 1, ''),
    Referenced_Tab_Name as  [主键表],
    [主键列]=stuff((select '，'+[Referenced_Col_Name] from (
        select    FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
                Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
        from sys.foreign_keys FK
        inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
        inner join sys.objects Parent_Tab ON  Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
        inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id 
                                            and  Col.parent_column_id = Parent_Col.column_id
        inner join sys.objects Referenced_Tab ON  Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
        inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id 
                                            and  Col.referenced_column_id = Referenced_Col.column_id
    )t where FK_Name=tb.FK_Name  and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name   for xml path('')), 1, 1, '')
    --as [外键列]
from (
    select    FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
            Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
    from sys.foreign_keys FK
    inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
    inner join sys.objects Parent_Tab ON  Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
    inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id 
                                        and  Col.parent_column_id = Parent_Col.column_id
    inner join sys.objects Referenced_Tab ON  Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
    inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id 
                                        and  Col.referenced_column_id = Referenced_Col.column_id
)tb
group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name

[王向飞]

sybase不会。以上是MSSQL的脚本

[caitian6]

sys.foreign_keys , sybase都没这张表, 感谢楼上的兄弟

[王向飞]

select sysreferences.tableid,a.name as tablename,sysreferences.reftabid,b.name as reftabname 
from sysreferences LEFT OUTER join sysobjects a on sysreferences.tableid = a.id LEFT OUTER join sysobjects b on sysreferences.reftabid = b.id
---where b.name in (*,*,...........)

[Austindatabases]

lz 建议你去问 晴空， 他是SYBASE的数据库管理员

[caitian6]

sysreferences, sybase里也没这样张 ,真郁闷

[caitian6]

[Quote=引用 7 楼 liuhuayang 的回复:]
lz 建议你去问 晴空， 他是SYBASE的数据库管理员
[/Quote]
帮我去叫下他,行吗, 能拉来人解决此问题,一样给分

[王向飞]