SQL Server检查存储过程是否包含WITH(NOLOCK)

Question

我希望编写一个C#应用程序，以检查所选的存储过程是否符合某些标准。例如，如果存储过程包含5个select查询，则相同的查询必须包含5个with(nolock)（除了临时表的select）。

如何通过C#实现这一点？提前谢谢。

- OmerArslan

---

我不知道为什么你在寻找nolocks提示，但我建议你阅读相关内容，因为nolock可能是TSQL中被误用最多的功能之一。 - jean

你最好阅读一些关于锁和NOLOCK的内容，了解它的优缺点，它所做的“魔法”以及为什么在大多数情况下使用它是没有意义的。 - Ivan Starostin

我认为这是一个有效的问题。我同意使用NOLOCK不是一个好主意，但这只是一个例子。根据@user2979085的问题，他想要针对许多标准验证存储过程，并且他想在C#中完成。这些是非常具体的要求。现在我猜测，但这可能需要在构建期间验证代码。 - Michał Komorowski

Answer 1

不确定问题是什么。

显然需要解析SQL（因为NOLOCK也可能在注释中出现）。

要获取存储过程的源代码 - 可以使用类似以下的东西：

using (SqlConnection sqlConnection = new SqlConnection())
{
    sqlConnection.ConnectionString = yourConnectionStringHere;
    sqlConnection.Open();
    SqlCommand sqlCommand = new SqlCommand("sys.sp_helptext", sqlConnection);
    sqlCommand.CommandType = CommandType.StoredProcedure;
    sqlCommand.Parameters.AddWithValue("@objname", "stored_proc_name_here");
    DataSet ds = new DataSet();
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = sqlCommand;
    sqlDataAdapter.Fill(ds);
    return DataTableToString(ds.Tables[0]);;
}

虽然我从不触碰SQL服务器，因为我将源代码保存在版本控制之外，所以Visual Studio插件是更好的解决方案。

- TomTom

Answer 2

您可以使用sys.objects查询SQL Server的元数据，以便像文本一样分析SP的定义。在您的情况下，您可以创建以下列出的查询，并从C＃应用程序中检查其结果：

SELECT object_definition(object_id) as [sp definition]
, schema_name(schema_id) [schema]
, name
, type_desc
FROM sys.objects
where object_definition(object_id) like '%select%select%select%'
and type_desc = 'SQL_STORED_PROCEDURE'

或者

SELECT object_definition(object_id) as [sp definition]
, schema_name(schema_id) [schema]
, name
, type_desc
FROM sys.objects
where object_definition(object_id) like '%NOLOCK%'
and type_desc = 'SQL_STORED_PROCEDURE'

- Andrey Morozov