搜索存储过程并返回字符串 () 中存在搜索参数的存储过程

Question

我使用下面的代码来获取包含我的单词LO的所有存储过程。

问题是目前它还返回所有表名或列名包含LO的过程

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%LO%'

是否有办法限制它只在字符串''中搜索LO，就像下面的例子。

'Please contact LO and return the ticket number'

也许一个正则表达式会工作?

Answer 1

我明白了。我可以这样做:

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%''%LO%''%'

或

LIKE '%''LO''%'

Answer 2

您可以使用下面的查询来识别包含指定文本的过程的源行，尽管在某些情况下它可能返回误报。

SELECT 
name ProcName
, value as ProcSourceLine
FROM   sys.procedures
CROSS APPLY STRING_SPLIT(OBJECT_DEFINITION(object_id),CHAR(10))
WHERE  value LIKE '%''%LO%''%';

完成此任务的最可靠的方法是使用T-SQL脚本DOM。下面是一个PowerShell示例，它使用来自现有SSMS安装的程序集。Microsoft.SqlServer.TransactSql.ScriptDom.dll也可以作为DacFx NuGet包的一部分使用。

$scriptDomAssemblyPath = "C:Program Files (x86)Microsoft SQL Server Management Studio 18Common7IDEExtensionsApplicationMicrosoft.SqlServer.TransactSql.ScriptDom.dll"
$connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
try {
Add-Type -Path $scriptDomAssemblyPath
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand(
@"
SELECT
OBJECT_SCHEMA_NAME(object_id) AS ProcSchema
, name ProcName
, OBJECT_DEFINITION(object_id) as ProcDefinition
FROM   sys.procedures;
"@, $connection
)
$connection.Open()
$reader = $command.ExecuteReader()
while($reader.Read()) {
$parseErrors.Clear()
$scriptReader = New-Object System.IO.StringReader($reader["ProcDefinition"])
$frament = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0) {
# in case an existing proc has syntax errors
Write-Host "$($parser.GetType().Name): $($parseErrors.Count) parsing error(s): $(($parseErrors | ConvertTo-Json))" -ForegroundColor Yellow
continue
}
foreach($token in $frament.ScriptTokenStream) {
if($token.TokenType -in ([Microsoft.SqlServer.TransactSql.ScriptDom.TSqlTokenType]::AsciiStringLiteral, [Microsoft.SqlServer.TransactSql.ScriptDom.TSqlTokenType]::UnicodeStringLiteral)) {
if($token.Text.ToUpper().Contains("LO")) {
Write-Host "Found literal text in $($reader["ProcSchema"]).$($reader["ProcName"])"
break
}
}
}
$scriptReader.Dispose()
}
$connection.Close()

}
catch {
throw
}