问题

I have translated the code part as requested. Here is the translated content:

有一个情况，我需要“拆开”SQL视图定义。这样我就能够看到视图中使用了哪些源表和列。我正在使用```SELECT * FROM INFORMATION_SCHEMA.VIEWS```来获取视图定义，并有一个查询来获取源表和列在自己的行和单独的列中。

在处理函数和运算符（+，-，*，/）时遇到了问题。
目前我遇到了一个问题，无法将包含多个源列的视图列拆分为它们自己的行和字段。

非常感谢任何帮助，提前感谢。

我正在处理的示例，当前结果和所需/期望的结果。

SELECT 'COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID' AS q INTO #TEMP

SELECT q,
SUBSTRING(q,CHARINDEX( 'COALESCE(',q,0)+9,CHARINDEX( '.',q,0)-10) AS [First Table],
SUBSTRING(q,CHARINDEX( 'COALESCE(',q,0)+9,CHARINDEX( ',',q,0)-10) AS [First Table And Column],
SUBSTRING(q,CHARINDEX( ',',q,0)+1,(CHARINDEX( ',0)',q,0)-CHARINDEX( ',',q,0))-1)  AS [Second Table And Column],
FROM #TEMP

--DROP TABLE #TEMP

上述查询的结果

|q| First Table| First Table And Column| Second Table And Column|
|--|--|--|--|
|COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID| HEADER| HEADER.ID1| HEADER.ID2|

我想要的是：

|q| Table| Column|
|--|--|--|
|COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID| HEADER| ID1|
|COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID| HEADER| ID2|

以下是我完整的查询，其中包括函数/运算符处理部分。

WITH tmp (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ColumnRow, ViewDefinition) AS
(
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
FROM
(SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
SUBSTRING(VIEW_DEFINITION,CHARINDEX('SELECT',VIEW_DEFINITION)+6,CHARINDEX('FROM',VIEW_DEFINITION)-CHARINDEX('SELECT',VIEW_DEFINITION)-18) AS ViewDefinition
FROM INFORMATION_SCHEMA.VIEWS
) ViewData
UNION all
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
FROM tmp
WHERE
ViewDefinition > ''
)

SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(ColumnRow,
CASE WHEN CHARINDEX('.',ColumnRow) = 0 THEN CHARINDEX('.',ColumnRow)
WHEN
CHARINDEX('CAST(',ColumnRow) > 0
OR CHARINDEX('CASE WHEN',ColumnRow) > 0
OR CHARINDEX('COALESCE',ColumnRow) > 0
OR CHARINDEX('GETDATE',ColumnRow) > 0
OR CHARINDEX('SUM(',ColumnRow) > 0
OR CHARINDEX('+',ColumnRow) > 0
OR CHARINDEX('-',ColumnRow) > 0
OR CHARINDEX('',ColumnRow) > 0
OR CHARINDEX('/',ColumnRow) > 0
THEN
''
ELSE
REPLACE(CHARINDEX('.',ColumnRow)-1,CHAR(9),'')
END
),CHAR(13)+CHAR(10), ''), ' ', ''), 'ISNULL(','',CHAR(9),''),CHAR(32),''),CHAR(10),''),CHAR(13),''),CHAR(160),''))) AS LeanTableName,
CASE
WHEN
CHARINDEX('CAST(',ColumnRow) > 0
OR CHARINDEX('CASE WHEN',ColumnRow) > 0
OR CHARINDEX('COALESCE',ColumnRow) > 0
OR CHARINDEX('GETDATE',ColumnRow) > 0
OR CHARINDEX('SUM(',ColumnRow) > 0
OR CHARINDEX('+',ColumnRow) > 0
OR CHARINDEX('-',ColumnRow) > 0
OR CHARINDEX('',ColumnRow) > 0
OR CHARINDEX('/',ColumnRow) > 0
THEN
''
ELSE
REPLACE(REPLACE(REPLACE (
LEFT(RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)),
CHARINDEX(' AS ',RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)-1))), ',0',''),'',''), ')', '')
END AS LeanColumnName,
CASE
WHEN
CHARINDEX('CAST(',ColumnRow) > 0
or CHARINDEX('nvarchar',ColumnRow) > 0
or CHARINDEX('decimal',ColumnRow) > 0
THEN
LTRIM(REPLACE(SUBSTRING(ColumnRow,CHARINDEX(') AS ',ColumnRow)+4,100),',',''))
ELSE
REPLACE(SUBSTRING(ColumnRow,CHARINDEX(' AS ',ColumnRow)+4,100),',','')
END AS ColumnName,
LTRIM(LEFT( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32)))), LEN( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))) - 1)) AS RowValue
FROM tmp
WHERE (ColumnRow LIKE '% AS %' and ColumnRow not like '%--%')
GO

Please note that the translation may not be perfect, so it's essential to review it carefully, especially if you plan to use it for coding purposes.

<details>
<summary>英文:</summary>

I have a case where I need to &quot;unpack&quot; sql view definitions. So that I would be able to see which source tables and columns are in use in the view. I am using ```SELECT * FROM INFORMATION_SCHEMA.VIEWS``` to get view definitions and have a query to get source tables and columns in own rows and separate columns.

I encountered a problem when dealing with functions and operators (+,-,*,/).
Currently I am stuck with getting a view column with function/operator that includes multiple source columns into their own rows and fields.

Any help would be greatly appreciated, thanks in advance.


Example of what I am dealing with, current results and required/expected results.    

    SELECT &#39;COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID&#39; AS q INTO #TEMP
 
    SELECT q,
    SUBSTRING(q,CHARINDEX( &#39;COALESCE(&#39;,q,0)+9,CHARINDEX( &#39;.&#39;,q,0)-10) AS [First Table],
    SUBSTRING(q,CHARINDEX( &#39;COALESCE(&#39;,q,0)+9,CHARINDEX( &#39;,&#39;,q,0)-10) AS [First Table And Column],
    SUBSTRING(q,CHARINDEX( &#39;,&#39;,q,0)+1,(CHARINDEX( &#39;,0)&#39;,q,0)-CHARINDEX( &#39;,&#39;,q,0))-1)  AS [Second Table And Column],
    FROM #TEMP
    
    --DROP TABLE #TEMP

Results from above query

|q|	First Table|	First Table And Column|	Second Table And Column|
|--|--|--|--|
|COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID|	HEADER|	HEADER.ID1|	HEADER.ID2|

What I am after is:

|q|	Table|Column|
|--|--|--|
|COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID|	HEADER|	ID1|
|COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID|	HEADER|	ID2|


Here is the complete query that I have, in which the function/operator handling would be included.

WITH tmp (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ColumnRow, ViewDefinition) AS
(
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
FROM
(SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
SUBSTRING(VIEW_DEFINITION,CHARINDEX('SELECT',VIEW_DEFINITION)+6,CHARINDEX('FROM',VIEW_DEFINITION)-CHARINDEX('SELECT',VIEW_DEFINITION)-18) AS ViewDefinition
FROM INFORMATION_SCHEMA.VIEWS
) ViewData
UNION all
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
FROM tmp
WHERE
ViewDefinition > ''
)

SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(ColumnRow,
CASE WHEN CHARINDEX('.',ColumnRow) = 0 THEN CHARINDEX('.',ColumnRow)
WHEN
CHARINDEX('CAST(',ColumnRow) > 0
OR CHARINDEX('CASE WHEN',ColumnRow) > 0
OR CHARINDEX('COALESCE',ColumnRow) > 0
OR CHARINDEX('GETDATE',ColumnRow) > 0
OR CHARINDEX('SUM(',ColumnRow) > 0
OR CHARINDEX('+',ColumnRow) > 0
OR CHARINDEX('-',ColumnRow) > 0
OR CHARINDEX('',ColumnRow) > 0
OR CHARINDEX('/',ColumnRow) > 0
THEN
''
ELSE
REPLACE(CHARINDEX('.',ColumnRow)-1,CHAR(9),'')
END
),CHAR(13)+CHAR(10), ''),' ', ''),'ISNULL(',''),CHAR(9),''),CHAR(32),''),CHAR(10),''),CHAR(13),''),CHAR(160),''))) AS LeanTableName,
CASE
WHEN
CHARINDEX('CAST(',ColumnRow) > 0
OR CHARINDEX('CASE WHEN',ColumnRow) > 0
OR CHARINDEX('COALESCE',ColumnRow) > 0
OR CHARINDEX('GETDATE',ColumnRow) > 0
OR CHARINDEX('SUM(',ColumnRow) > 0
OR CHARINDEX('+',ColumnRow) > 0
OR CHARINDEX('-',ColumnRow) > 0
OR CHARINDEX('',ColumnRow) > 0
OR CHARINDEX('/',ColumnRow) > 0
THEN
''
ELSE
REPLACE(REPLACE(REPLACE (
LEFT(RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)),
CHARINDEX(' AS ',RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)-1))), ',''0'')',''),','''')',''), ')', '')
END AS LeanColumnName,
CASE
WHEN
CHARINDEX('CAST(',ColumnRow) > 0
or CHARINDEX('nvarchar',ColumnRow) > 0
or CHARINDEX('decimal',ColumnRow) > 0
THEN
LTRIM(REPLACE(SUBSTRING(ColumnRow,CHARINDEX(') AS ',ColumnRow)+4,100),',',''))
ELSE
REPLACE(SUBSTRING(ColumnRow,CHARINDEX(' AS ',ColumnRow)+4,100),',','')
END AS ColumnName,
LTRIM(LEFT( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32)))), LEN( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))) - 1)) AS RowValue
FROM tmp
WHERE (ColumnRow LIKE '% AS %' and ColumnRow not like '%--%')
GO

</details>


# 答案1
**得分**: 1

If you are on SQL Server 2016 or newer, you can use the [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16) function to easily split your columns into separate rows. If you are on an older version, you can search for custom split string functions (there are plenty).

Here is a working example with your provided #TEMP table.

```sql
WITH CTE_temp AS
(
	SELECT q
	, SUBSTRING(q, CHARINDEX('COALESCE(', q, 0) + 9, (CHARINDEX(',', q, 0)) - CHARINDEX('COALESCE(', q, 0) - 9) as AllColumns
	FROM #TEMP
)
, CTE_Split AS 
(
	SELECT q, AllColumns, value AS table_column
	FROM CTE_temp  
	CROSS APPLY STRING_SPLIT(AllColumns, ',')
)
SELECT *
, LEFT(table_column, CHARINDEX('.', table_column) - 1) AS [Table]
, RIGHT(table_column, CHARINDEX('.', REVERSE(table_column)) - 1) AS [Column]
FROM CTE_Split;

Please note that this code demonstrates how to split columns using the STRING_SPLIT function in SQL Server 2016 or newer.

WITH CTE_temp AS
(
	SELECT q
	, SUBSTRING(q,CHARINDEX('COALESCE(',q,0)+9, (CHARINDEX( ',0)',q,0))-CHARINDEX( 'COALESCE(',q,0) - 9) as AllColumns
	FROM #TEMP
)
, CTE_Split AS 
(
	SELECT q, AllColumns, value AS table_column
	FROM CTE_temp  
	CROSS APPLY STRING_SPLIT(AllColumns, ',')
)
SELECT *
, LEFT(table_column, CHARINDEX('.', table_column) - 1) AS [Table] -- you can still user SUBSTRING instead of LEFT/RIGHT 
, RIGHT(table_column, CHARINDEX('.', REVERSE(table_column)) - 1) AS [Column] --note reserved words
FROM CTE_Split; 

答案2

得分: 0

以下是您需要翻译的内容：

这是我在这种情况下最终需要的实际查询。
返回所有视图中的所有源表和列。

select DISTINCT  
	dependencies.referenced_entity_name as 源表,
	entities.referenced_minor_name as 源列
from sys.sql_expression_dependencies as dependencies
join sys.objects as objects on object_id=referencing_id
join sys.schemas as schemas on schemas.schema_id=objects.schema_id
cross apply sys.dm_sql_referenced_entities(schemas.name+'.'+objects.name,'OBJECT') as entities
where entities.referenced_entity_name=dependencies.referenced_entity_name
  and (is_schema_bound_reference=0 or entities.referenced_minor_id=dependencies.referenced_minor_id)
  AND entities.referenced_minor_name is not null

这是我找到答案的原始帖子链接：
https://stackoverflow.com/questions/46552358/sql-server-column-dependencies-without-deprecated-sys-view

select DISTINCT  
	dependencies.referenced_entity_name as SourceTable,
	entities.referenced_minor_name as SourceColumn
from sys.sql_expression_dependencies as dependencies
join sys.objects as objects on object_id=referencing_id
join sys.schemas as schemas on schemas.schema_id=objects.schema_id
cross apply sys.dm_sql_referenced_entities(schemas.name+'.'+objects.name,'OBJECT') as entities
where entities.referenced_entity_name=dependencies.referenced_entity_name
  and (is_schema_bound_reference=0 or entities.referenced_minor_id=dependencies.referenced_minor_id)
  AND entities.referenced_minor_name is not null