如何查找用于连接表的列名？

发布于 2025-01-17 10:25:20 字数 563 浏览 0 评论 0原文

我有一个用于选择查询的存储过程，并且在查询中剩下的join和内在命令。我正在使用下面的脚本来用于存储过程的依赖关系。

select  
    referenced_database_name,
    referenced_entity_name,
    referenced_minor_name
from 
    sys.dm_sql_referenced_entities ('dbo.sp_test', 'OBJECT')

如何获得该SP中用于连接表的列名？或有什么简单的方法可以从此脚本获取/解析列名：

select column1, column2
from tableA
inner join tableB on tableB.id = tableA.id

我需要tableb.id and tablea.id 键盘从

我尝试过的脚本中使用substring，<代码>左等功能，但它们对我来说是不安全的。使用Union All，有很多选择的查询。

原文

I have a stored procedure for a select query and there are left join and inner join commands in the query. I'm using the script below for dependencies of the stored procedure.

select  
    referenced_database_name,
    referenced_entity_name,
    referenced_minor_name
from 
    sys.dm_sql_referenced_entities ('dbo.sp_test', 'OBJECT')

How can I get the column name which used for joining tables in this SP? Or is there any simple way to get/parse column names from this script:

select column1, column2
from tableA
inner join tableB on tableB.id = tableA.id

I need tableB.id and tableA.id keywords from the script

I've tried parse that script with substring, left etc functions but they're insecure for me. There are lots of piece of select query using union all.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痴骨ら 2025-01-24 10:25:20

我解决了解析查询的问题。起初，我留下了一个分隔线（ - *），用于分裂包含加入的部分，然后使用该代码来解析查询（我知道它不是专业的，但对我有用）：

select distinct
referenced_database_name,
referenced_entity_name,
referenced_minor_name,
SUBSTRING(c.value,CHARINDEX('on',value)+3,len(value)) split
,IIF(SUBSTRING(c.value,CHARINDEX('on',value)+3,len(value)) like '%'+CONCAT(referenced_entity_name,'.',referenced_minor_name)+'%',1,0) as clm
from sys.dm_sql_referenced_entities ('dbo.sp_test', 'OBJECT') a
left join sys.sysdepends b on OBJECT_NAME(depid) = a.referenced_entity_name
outer apply string_split(REPLACE(OBJECT_DEFINITION(id),'--*','/'),'/') c
where OBJECT_NAME(id) = 'sp_test' and c.value like '%join%' and referenced_minor_name is not null

I'v solved my problem parsing the query. At first, i left a delimiter (--*) for splitting the section which contains joins then used that code for parsing the query (i know its not professional but it has worked for me) :

select distinct
referenced_database_name,
referenced_entity_name,
referenced_minor_name,
SUBSTRING(c.value,CHARINDEX('on',value)+3,len(value)) split
,IIF(SUBSTRING(c.value,CHARINDEX('on',value)+3,len(value)) like '%'+CONCAT(referenced_entity_name,'.',referenced_minor_name)+'%',1,0) as clm
from sys.dm_sql_referenced_entities ('dbo.sp_test', 'OBJECT') a
left join sys.sysdepends b on OBJECT_NAME(depid) = a.referenced_entity_name
outer apply string_split(REPLACE(OBJECT_DEFINITION(id),'--*','/'),'/') c
where OBJECT_NAME(id) = 'sp_test' and c.value like '%join%' and referenced_minor_name is not null

回复收藏 0 原文

~没有更多了~