T-SQL筆記40_OPENROWSET與sp_helptext的使用_如何去查看一支預存程序,Trigger,Function的指令並只保留關鍵字該行命令rowset
如何去查看一支預存程序,Trigger,Function的指令
龐大的商業邏輯2千行起跳的預存程序
在這麼大串程式中
若要去查看其完整定義
透過T-SQL的
exec sp_helptext {SP名稱|Trigger名稱|Function名稱}
就可陳列出來
而若要針對此段，保留有用到特定某關鍵字的篩選
類似Visual Studio IDE的尋找參考
可使用到之前用過的2個技巧
這邊用openrowset來去接exec 預存程序的回傳
1 2 3 4 5 6 7 8 9 10 11 | IF Object_id('tempdb..#tmp_table') IS NOT NULL BEGIN DROP TABLE #tmp_table END select a.Text as cmd into #tmp_table from openrowset('SQLNCLI', 'Server={DB主機};Trusted_Connection=yes;', 'SET NOCOUNT ON;SET FMTONLY OFF;exec sp_helptext {SP名|trigger名|function名}') AS a; select LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cmd, CHAR(0), CHAR(32)), CHAR(9), CHAR(32)) , CHAR(10), CHAR(32)),CHAR(11), CHAR(32)),CHAR(12), CHAR(32)),CHAR(13), CHAR(32)),CHAR(14), CHAR(32)))) from #tmp_table where cmd like '%{關鍵字}%' |
Ref:
"SELECT * INTO table FROM" a stored procedure? Possible?
OPENROWSET with Windows Authentication
https://www.sqlservercentral.com/forums/topic/openrowset-with-windows-authentication
Insert results of a stored procedure into a temporary table
https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
[SQL]讓 Execute 可以搭配 Select Into，而不再只有 Insert into
https://dotblogs.com.tw/rainmaker/2015/02/02/148355
[T-SQL]-小心sp_helptext的雷
https://www.sqlservercentral.com/forums/topic/openrowset-with-windows-authentication
Insert results of a stored procedure into a temporary table
https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
[SQL]讓 Execute 可以搭配 Select Into，而不再只有 Insert into
https://dotblogs.com.tw/rainmaker/2015/02/02/148355
[T-SQL]-小心sp_helptext的雷
openrowset to connect to SQL server with a password containing single quotes
New SQL Server sp_helptext to avoid line splits of code
script all stored procedures into one file
留言
張貼留言