c# - 如何在 View 查询中找到表别名？

Question

我现在正在使用 C# 为 MS-SQL View 查询实现 View 生成器。

如果 FROM 子句中已经有一个表，我必须将它与现有表别名重用到 SELECT 子句中

例如，如果我输入“PackOrderPouch”，结果将是“A”，或者如果我输入“Client”，结果将是“R”。

是否有简单的方法让 MS-SQL 可以给表别名，或者用 C# 可以很容易地做到这一点？

ALTER VIEW [dbo].[PackOrderRefV]
AS


SELECT 
    A.PackOrderItemOrientedID
,    A.PackOrderItemID
,    A.PouchNo
,    C.PatientNo
,    C.PatientName
,    D.FacilityNo AS LocationNo
,    IIF(D.FacilityName = N'WALK-IN', N'', D.FacilityName) AS LocationName
,    E.FacilityNo AS HospitalNo
,    E.FacilityName AS HospitalName
,    ISNULL(E.FacilityDescription, E.FacilityName) AS HospitalDescription
,    A.AdminDay
,    GETDATE() AS PackedDate
,    ISNULL(H.PrescriptionValue, '') AS BirthDay  --BirthDay (1/2)
,    R.MachineNo  --MachineNo (1/3)
,    Patient_ETC01.PrescriptionItemValue AS Patient_ETC01  --Patient_ETC01 (1/2)

FROM
    PackOrderPouch A
    LEFT OUTER JOIN Prescription B ON A.PrescriptionID = B.RawID 
    LEFT OUTER JOIN Patient C ON B.PatientID = C.RawID 
    LEFT OUTER JOIN Facility D ON A.LocationID = D.RawID
    LEFT OUTER JOIN Facility E ON A.FacilityID = E.RawID
    LEFT OUTER JOIN AdminTime F ON A.AdminTimeID = F.RawID
    LEFT OUTER JOIN PrescriptionDetail H ON A.PrescriptionID = H.PrescriptionID AND H.OCSFormatItemID = 6  --BirthDay (2/2)
    -- * Patient ETC OCSFormatItemID - 01:91, 02:92, 03:93, 04:94, 05:95, 06:96, 07:97, 08:98, 09:99
    LEFT OUTER JOIN PackOrderItemOriented Q ON A.PackOrderItemOrientedID = Q.RawID  --MachineNo (2/3)
    LEFT OUTER JOIN Client R ON Q.ClientID = R.RawID  --MachineNo (3/3)
    LEFT OUTER JOIN PrescriptionDetail Patient_ETC01 ON A.PrescriptionID = Patient_ETC01.PrescriptionID AND Patient_ETC01.OCSFormatItemID = 91  --Patient_ETC01 (2/2)
GO

抱歉解释不充分，我会进一步澄清这个例子。

“PackOrderRefV”已经存在，C# 将使用“sp_helptext PackOrderRefV”读取此 View 脚本，并希望通过读取 XML 文件和更改 View 在 SELECT 子句上添加“Patient.RoomNo”列。但是，“Patient”表已经存在于 FROM 子句中，因此如果 Patient 表已经存在，则不应在 FROM 子句中添加。如果FROM子句中已经没有Patient表，则需要在FROM子句中添加。

    <?xml version="1.0" encoding="utf-8" ?>
<ArrayOfViewElement xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ViewElement ViewName="PackOrderRefV" ColumnName="RoomNo" Description="Patient's RoomNo">
        <ViewElementDetail Type="SELECT">
,   {Patient}.RoomNo
        </ViewElementDetail>
        <ViewElementDetail Type="FROM">
    LEFT OUTER JOIN Patient {0} ON {Prescription}.PatientID = {0}.RawID 
        </ViewElementDetail>
    </ViewElement>
    <ViewElement ViewName="PackOrderRefV" ColumnName="DayOfWeek_MultiLanguage" Description="Day Of Week text with its patient language">
        <ViewElementDetail Type="SELECT">
,   FORMAT(A.AdminDay, N'ddd',
    (
        CASE MR5.PrescriptionItemValue
            WHEN N'Korean'              THEN N'ko-KR'
            WHEN N'English'             THEN N'en-US'
            WHEN N'German'              THEN N'de-DE'
            WHEN N'French'              THEN N'fr-FR'
            WHEN N'Japanese'            THEN N'ja-JP'
            WHEN N'Danish'              THEN N'da-DK'
            WHEN N'Spanish'             THEN N'es-ES'
            WHEN N'Italian'             THEN N'it-IT'
            WHEN N'Dutch'               THEN N'nl-NL'
            WHEN N'Portuguese'          THEN N'pt-PT'
            WHEN N'Finnish'             THEN N'fi-FI'
            WHEN N'Swedish'             THEN N'sv-SE'
            WHEN N'Hungarian'           THEN N'hu-HU'
            WHEN N'Polish'              THEN N'pl-PL'
            WHEN N'Turkish'             THEN N'tr-TR'
            WHEN N'Traditional Chinese' THEN N'zh-Hant'
            WHEN N'Simplified Chinese'  THEN N'zh-Hans'
            WHEN N'Arabic'              THEN N'ar-SA'
            WHEN N'Thai'                THEN N'th-TH'
            WHEN N'Bokmål'              THEN N'nb-NO'
            ELSE N'en-US'
        END
    )) DayOfWeek_MultiLanguage  --DayOfWeek_MultiLanguage (1/3)
        </ViewElementDetail>
        <ViewElementDetail Type="FROM">
    LEFT OUTER JOIN PrescriptionItem K1 ON K1.RawID = (SELECT TOP (1) RawID FROM PrescriptionItem p1 where p1.PrescriptionID = A.PrescriptionID)  --DayOfWeek_MultiLanguage (2/3)
    LEFT OUTER JOIN PrescriptionItemDetail MR5 ON K1.RawID = MR5.PrescriptionItemID AND MR5.OCSFormatItemID = 71  --DayOfWeek_MultiLanguage (3/3)
        </ViewElementDetail>
    </ViewElement>
    <ViewElement ViewName="PackOrderRefV" ColumnName="Patient_ETC01" Description="Patient Random 01 Field in OCS file">
        <ViewElementDetail Type="SELECT">
,   PR1.PrescriptionItemValue AS Patient_ETC01  --Patient_ETC01 (1/2)
        </ViewElementDetail>
        <ViewElementDetail Type="FROM">
    LEFT OUTER JOIN PrescriptionDetail PR1 ON A.PrescriptionID = PR1.PrescriptionID AND PR1.OCSFormatItemID = 91  --Patient_ETC01 (2/2)
        </ViewElementDetail>
    </ViewElement>
</ArrayOfViewElement>

Answer 1

这可以用 T-SQL 来完成。首先获取您需要的 View 的定义:

DECLARE @view nvarchar(max) = N'',
        @x xml

SELECT  @view = sm.[definition]
FROM sys.views v
INNER JOIN sys.sql_modules sm
    ON v.[object_id] = sm.[object_id]
WHERE v.[name] = 'PackOrderRefV'

然后:

SELECT @view = SUBSTRING(@view,CHARINDEX('FROM',@view)+4,CASE WHEN CHARINDEX('GO',@view) > 0 THEN CHARINDEX('GO',@view)-2 ELSE LEN(@view) END)

SELECT @x = '<t>'+REPLACE(@view,'JOIN','</t><t>')+'</t>'

;WITH cte AS (
    SELECT LTRIM(t.c.value('.','nvarchar(max)')) as p
    FROM @x.nodes('/t') as t(c)
)

SELECT CASE WHEN CHARINDEX(' on ',p) > 0 THEN SUBSTRING(p,1,CHARINDEX(' on ',p)) ELSE p END
FROM cte

输出:

     PackOrderPouch A     LEFT OUTER 
Prescription B 
Patient C 
Facility D 
Facility E 
AdminTime F 
PrescriptionDetail H 
PackOrderItemOriented Q 
Client R 
PrescriptionDetail Patient_ETC01 

正如您首先看到的，我参与了从 FROM 关键字到 GO 的部分。然后，将其设为 XML。并通过搜索 ON 关键字得到我们需要的东西。

这就是在 View 查询中查找表别名的方法吗？