Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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: <a href="http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx" rel="nofollow">http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx</a>.</p> <p>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.</p> <p>I played around with it a bit, and here is a <em>very</em> raw example to illustrate the basic principle:</p> <pre><code>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&lt;ParseError&gt; errs; IScriptFragment frag = parser.Parse(sr, out errs); if (null == frag) continue; IList&lt;TSqlParserToken&gt; tokens = gen.GenerateTokens((TSqlFragment)frag); foreach (TSqlParserToken token in tokens) { if (token.TokenType == TSqlTokenType.UnicodeStringLiteral || token.TokenType == TSqlTokenType.AsciiStringLiteral) { if (token.Text.Length &gt;= 20) Console.WriteLine("String found: " + token.Text); } } } } } } } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow best to find hard-coded English language strings in SQL Server stored procedures?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMatthew Burr
    1. USMatthew Burr
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow best to find hard-coded English language strings in SQL Server stored procedures?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVERY nice! Thank you. I'm going to try this. I had some intuition that this was possible and your idea gets me much closer
      singulars
      1. PO
      1. USTomK
    2. COGave it a try, and it's working great. Even your very raw code got me 98% of the way there. It gets lots of false positives, but that's what I was expecting anyway. The English phrases stand out to the eye pretty clearly when I scan the results.
      singulars
      1. PO
      1. USTomK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload