各位大虾,我一个订单明细表中有一个外键,连到订单表的主键。我如何根据订单明细表得知它的外键,以及这个外键它是连接到那个表上的?
个位帮小弟一个忙啊!
个位帮小弟一个忙啊!
解决方案 »
- 看看这条SQL语句怎么改呢?
- 树形结构求取值写法
- 如何写SQL?
- 问一个ado随机读取数据库记录问题?
- sql server2000为什么安装不上
- reporting service2000如何迁移到reporting service2005
- access转成sql server后,很多表中莫名其妙的多了个upsize_ts字段?
- 关于text字段,有难度,搜不到相关帖子:在存储过程里如何把 一个表的 text字段的内容 写到 另一个表中的text字段,通过存储过程!
- 同一条记录不同字段求和并且输出的问题
- 请教nvarchar(200)类型转换成日期格式。
- 如何在第二排序中加入序列值?
- 利用存储过程搜索数据时的小问题
CREATE PROC PROC_AUTO_SEARCH_R
(
@TABLENAME VARCHAR(64)
)AS
/*
存储过程功能:根据交换机ID 自动查找关系
存储过程名称: PROC_AUTO_SEARCH_R
使用模块: 基础功能类*/
SELECT NAME ,
(SELECT NAME FROM SYSTYPES WHERE XTYPE=T.XTYPE)AS xtype,
length,
(CASE ISNULLABLE WHEN 0 THEN ''
WHEN 1 THEN '√' END ) AS ISNULL,
COLID AS NUM
INTO #TEMPTABLE
FROM SYSCOLUMNS T
WHERE ID=(SELECT ID FROM SYSOBJECTS WHERE XTYPE IN ('U') AND id=OBJECT_ID(@TABLENAME) ) SELECT CONSTID,
(SELECT NAME FROM SYSOBJECTS WHERE ID=CONSTID) AS 外键,
FKEYID,
(SELECT NAME FROM SYSOBJECTS WHERE ID=FKEYID) AS 主表,
RKEYID,
(SELECT NAME FROM SYSOBJECTS WHERE ID=RKEYID)AS 关系,
FKEY1
INTO #TEMPTABLE2
FROM sysreferences
WHERE FKEYID=OBJECT_ID(@TABLENAME) ORDER BY FKEY1SELECT convert(char(20),NAME) AS 名称,
convert(char(20),UPPER(xtype)+'('+convert(varchar(20),length)+')') AS 数据类型,
[ISNULL] AS 是否空值,
'' AS 默认值,
'' AS 主键,
(CASE WHEN 关系 is NULL THEN '' ELSE 关系 end ) as 关系
--外键
FROM #TEMPTABLE LEFT OUTER JOIN #TEMPTABLE2 ON
#TEMPTABLE2.FKEY1=#TEMPTABLE.NUM
DROP TABLE #TEMPTaBLE
DROP TABLE #TEMPTaBLE2
SELECT OBJECT_NAME(sysobjects.parent_obj) AS TableName,
syscolumns.name as ColumnName,
OBJECT_NAME(sysforeignkeys.rkeyid) as ParentTableName
FROM sysobjects INNER JOIN sysforeignkeys
ON sysobjects.xtype='F'
AND sysobjects.id = sysforeignkeys.constid
INNER JOIN syscolumns ON sysforeignkeys.fkeyid = syscolumns.id
and OBJECT_NAME(sysobjects.parent_obj)='order' and sysforeignkeys.fkey = syscolumns.colid and syscolumns.name = 'customerid'