Easy way to generate a standard SQL Server expression form?

Question

This applies to computed columns and default constraints (and possibly other expressions) in SQL Server 2005 (or higher). Both of them use an arbitrary expression to generate a value, for example. (year+1)for a computed column representing "next year" (this is obviously a simple and silly example).

What I'm trying to make: . I want to determine if an existing computed column (or the default constraint) in any table matches the intended definition, where the latter is defined in which it was created to create the table. I can get the definition of the computed column using sp_helptext, and the definition of the default constraint from the catalog view sys.default_constraints.

I am having a problem with: . The expressions returned from the above sources have a normalized / standard form that does not match the form used to create the column / constraint. In the above example, SQL normalizes the expression to ([year]+(1)). Therefore, a simple comparison of the lines between this form and the original form will not reliably determine whether they are the same.

Solutions I've already thought about:

• Generate source expressions to match the SQL form. This requires knowledge of the rules used by SQL to generate its form, which are not documented, so this is not a great solution.
• Disassemble both forms in AST and compare them. I am already an AST for the original form, but I do not have a parser and, rather, I will not write.
• Create a temporary table and add the computed column using the original expression, and then return the normalized expression. That would be pretty reliable, but it seems dirty, since theoretically this comparison should be a read-only operation.

- ? , , , - - /, / .

Answer 1

, "" , -!

, MS SQL 2008 Express :

year(getdate()) + 1

SQL Server

(datepart(year,getdate())+(1))

, , 100% ,

1) , , , . , getdate() (0), (1). , .

2) , [] (), + 1, ([] + (1)). , .

3) , 1- 2- , , , - .

---

EDIT 04.Aug:

, . , ? , , , (, , )

/ , , # Microsoft.SqlServer.Management.Smo. IntTest int, VarcharTest varchar (1), DateTimeTest datetime .. - . / , drop , .

# ( Microsoft.SqlServer.Management.Smo;)

        Server server = new Server("localhost\\SQLEXPRESS");
        Database db = server.Databases["test"];
        Table t = db.Tables["test_defaults"];
        //here should be some logic to select column name depending on default data type
        //for example for DateTime defaults we will use "DateTimeTest" column
        Column c = t.Columns["DateTimeTest"];

        //clean up previous results if they exist
        DefaultConstraint constr = c.DefaultConstraint;
        if (constr != null) constr.Drop();

        //create new constraint
        constr = c.AddDefaultConstraint();
        constr.Text = "getdate()";
        constr.Create();
        //after refresh we will have a new text
        constr.Refresh();
        string result = constr.Text;

        //drop it if we don't need it
        constr.Drop();

Answer 2

, , ( ), , object_definition.

- Kalen Delaney, , , .

Rob

Answer 3

( ) - temp, , , 'd , . , , , , , , , .

, ( , ), , .