Databases
StackOverflow2013
Postsdbo
POGetting the normalized check constraint text
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGetting the normalized check constraint text
    primarykey
    data
    text
    <p>I am writing an SQL Server schema comparison tool and I need to extract out the text expression of both default &amp; check constraints from tables from any database version (2000, 2005, 2008 and the forthcoming 2012 version).</p> <p>I am using the SQL Server 2000 Northwind database as my source database and SQL Server 2008 as the target database. When I encounter the <code>[CK_Birthdate]</code> check constraint on the <code>Employees</code> table it contains the text <code>([BirthDate] &lt; getdate())</code>. When scripted out via T-SQL and then run in SQL Server 2008 R2 it becomes <code>(([BirthDate]&lt;getdate()))</code> and the spaces are stripped out. </p> <p>I have tried stripping out the spaces, which works great, but was after reading <a href="https://stackoverflow.com/questions/1206514/a-simple-way-to-generate-sql-servers-standard-form-of-an-expression">this</a> I was shocked to see that SQL Server changes the expression so it no longer matches the original expression.</p> <p>I am having a bit of difficulty in getting the <strong>normalized</strong> text from the check/default constraint and I don't really understand why SQL Server performs this normalization rather than just leaving the original text intact? This normalization process is making it virtually impossible do an exact comparison.</p> <p>I have tried the following:</p> <ul> <li>Creating a temporary table with a single sql_variant column, but this causes an exception to be thrown because the column name used in the check constraint expression does not exist in the temporary table. I could parse the expression to extract the column names and create them dynamically, but this would cause a degradation in performance.</li> <li>Updating the check constraint back into the text property and then performing a <code>Refresh()</code> to get the text back out. This doesn't work and just puts the original text back in.</li> </ul> <p>If anyone has any suggestions they will be greatly appreciated.</p> <p><strong>Update</strong></p> <p>I have decided to take the route described in the first option. </p> <p>Rather than just having a single column in the temporary table I will:</p> <ul> <li>parse the check constraint expression using <a href="http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting%20to-the-crown-jewels.aspx" rel="nofollow noreferrer"><code>Microsoft.Data.Schema.ScriptDom</code></a> to extract out the column names from the expression and then dynamically add them to the temporary table.</li> <li>add the check constraint to the temporary table and then read the <code>.Text</code> property back out to get the normalized text.</li> <li>drop the check constraint from the temporary table.</li> </ul> <p>So far this seems to work effectively, albeit with a little trade-off in terms of performance, but I can live with this.</p> <p>I will see how this turns out in the next few weeks and update this question with an answer so other members can use this information.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USIntrepid
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POGetting the normalized check constraint text
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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