如何最好地在SQL Server存储过程中找到硬编码的英语字符串?

另一种想法:Microsoft通过Visual Studio提供了可以解析SQL的程序集.我已经用过了，而且使用起来很简单.您也许可以使用它来解析存储过程的文本；它可以返回语句中各种标记的列表，包括标记的类型.因此，它应该能够帮助您区分您可能会感兴趣的文本字符串与注释中哪些内容可以忽略.这里有更多详细信息: http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx .

Another thought: Microsoft provides, with Visual Studio, an assembly that can parse SQL. I've used it, and it's fairly simple to use. You might be able to use it to parse the text of your stored procedures; it can return a list of the various tokens in your statements, including the type of the token. So, it should be able to help you differentiate between what is a string of text you might be interested in vs. what is part of a comment and can be ignored. There are more details here: http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx.

基本上，从.NET，您将打开与数据库的连接，并在syscomments中查询存储过程的文本.您将遍历每个过程，并使用这些解析器对其进行解析.然后，您将使用Sql100ScriptGenerator从解析的文本中获取标记，遍历标记并查找其类型为ASCII或Unicode字符串文字的标记.对于那些字符串，请检查其长度以查看其长度是否超过20，如果存在，请标记字符串和procs，以供进一步检查.

Basically, from .NET, you'd open a connection to your database and query syscomments for your stored procedures' text. You'd loop through each procedure and parse it using these parser. Then you'd use the Sql100ScriptGenerator to get the tokens out of the parsed text, loop through the tokens and look for tokens whose types are either ASCII or Unicode string literals. For those strings, check their length to see if it's 20+, and if it is, flag the strings and the procs as needing further review.

我玩了一下，这是一个非常原始示例，用来说明基本原理:

I played around with it a bit, and here is a very raw example to illustrate the basic principle:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Data.Schema;
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;

namespace FindHardCodedStrings
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
                bldr.DataSource = "localhost\\sqlexpress";
                bldr.InitialCatalog = "msdb";
                bldr.IntegratedSecurity = true;

                conn.ConnectionString = bldr.ConnectionString;

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select [text] from syscomments";

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

                TSql100Parser parser = new TSql100Parser(false);
                Sql100ScriptGenerator gen = new Sql100ScriptGenerator();
                gen.Options.SqlVersion = SqlVersion.Sql100;

                foreach (DataRow proc in ds.Tables[0].Rows)
                {
                    string txt = proc[0].ToString();
                    using (System.IO.TextReader sr = new System.IO.StringReader(txt))
                    {
                        IList<ParseError> errs;
                        IScriptFragment frag = parser.Parse(sr, out errs);

                        if (null == frag)
                            continue;

                        IList<TSqlParserToken> tokens = gen.GenerateTokens((TSqlFragment)frag);

                        foreach (TSqlParserToken token in tokens)
                        {
                            if (token.TokenType == TSqlTokenType.UnicodeStringLiteral || token.TokenType == TSqlTokenType.AsciiStringLiteral)
                            {
                                if (token.Text.Length >= 20)
                                    Console.WriteLine("String found: " + token.Text);
                            }
                        }
                    }
                }

            }
        }
    }
}