sql server - with - 檢索存儲過程結果集的列定義




stored procedure sql server (4)

一種不太複雜的方法(在某些情況下可能就足夠了):在最終SELECT之後和FROM子句之前編輯原始SP,添加INSERT INTO tmpTable以將SP結果保存在tmpTable中。

運行修改後的SP,最好使用有意義的參數,以獲得實際數據。 恢復該過程的原始代碼。

現在,您可以從SQL Server management studio獲取tmpTable腳本或查詢sys.columns以獲取字段描述。

我正在使用SQL Server 2008中的存儲過程,並且我已經了解到我必須INSERT INTO已經預定義的臨時表以便處理數據。 這很好,除瞭如何定義我的臨時表,如果我不是那個編寫存儲過程而不是列出其定義和讀取代碼的人?

例如,我的臨時表對於'EXEC sp_stored_procedure'是什麼樣的? 這是一個簡單的存儲過程,我可能猜測數據類型,但似乎必須有一種方法來只讀取執行過程返回的列的類型和長度。


如果你在一個受限制權限的環境中工作,其中環回鏈接服務器之類的東西似乎是黑魔法並且絕對是“沒辦法!”,但你對模式有一些權利,只有幾個存儲過程要處理,這是非常簡單的解。

您可以使用非常有用的SELECT INTO語法,該語法將創建一個包含查詢結果集的新表。

假設您的過程包含以下選擇查詢:

SELECT x, y, z
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...

而是將其替換為:

SELECT x, y, z
INTO MyOutputTable
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...

當您執行它時,它將創建一個新表MyOutputTable,其中包含查詢返回的結果。

您只需右鍵單擊其名稱即可獲得表定義。

就這樣 !

SELECT INTO只需要能夠創建新表並且也可以使用臨時表(SELECT ... INTO #MyTempTable),但是檢索定義可能更難。

但是當然如果你需要檢索數千SP的輸出定義,那不是最快的方法:)



這是我寫的一些代碼。 這個想法(正如其他人所說的)是獲取SP代碼,修改它並執行它。 但是,我的代碼不會更改原始SP。

第一步,獲取SP的定義,剝離“創建”部分,並在聲明參數後刪除“AS”(如果存在)。

Declare @SPName varchar(250)
Set nocount on

Declare @SQL Varchar(max), @SQLReverse Varchar(MAX), @StartPos int, @LastParameterName varchar(250) = '', @TableName varchar(36) = 'A' + REPLACE(CONVERT(varchar(36), NewID()), '-', '')

Select * INTO #Temp from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME = 'ADMIN_Sync_CompareDataForSync'

if @@ROWCOUNT > 0
    BEGIN
        Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', 'Declare') 
        from INFORMATION_SCHEMA.ROUTINES 
        where ROUTINE_NAME = @SPName

        Select @LastParameterName = PARAMETER_NAME + ' ' + DATA_TYPE + 
            CASE WHEN CHARACTER_MAXIMUM_LENGTH is not null THEN '(' + 
                CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END 
        from #Temp 
        WHERE ORDINAL_POSITION = 
            (Select MAX(ORDINAL_POSITION) 
            From #Temp)

        Select @StartPos = CHARINDEX(@LastParameterName, REPLACE(@SQL, '  ', ' '), 1) + LEN(@LastParameterName)
    END
else
    Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', '') from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = @SPName

DROP TABLE #Temp

Select @StartPos = CHARINDEX('AS', UPPER(@SQL), @StartPos)

Select @SQL = STUFF(@SQL, @StartPos, 2, '')

(注意基於唯一標識符創建新表名)現在找到代碼中的最後一個'From'字,假設這是執行返回結果集的select的代碼。

Select @SQLReverse = REVERSE(@SQL)

Select @StartPos = CHARINDEX('MORF', UPPER(@SQLReverse), 1)

更改代碼以將結果集選擇到表中(基於uniqueidentifier的表)

Select @StartPos = LEN(@SQL) - @StartPos - 2

Select @SQL = STUFF(@SQL, @StartPos, 5, ' INTO ' + @TableName + ' FROM ')

EXEC (@SQL)

結果集現在在一個表中,表是否為空無關緊要!

讓我們得到表的結構

Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

你現在可以用這個做你的魔術

別忘了丟掉那張獨特的桌子

Select @SQL = 'drop table ' + @TableName

Exec (@SQL)

希望這可以幫助!





stored-procedures