Creating Default columns in SQL 2008 using Policy management (DMF)

Question

Hi,

Could someone tell me if its possible to add a policy to create default columns in a table schema using the SQL 2008 Policy management features

So for example I may have a desire to have a Date column in all of my tables called CreateDate and no DBA could create a table without a column of type datetime with name "CreateDate"

Is that possible?

Answer 1

Im not familiar with using PBM to do this but you could definitely do such a check by using a database level DDL trigger. See BOL here.

Answer 2

The Declarative Management Framework won't create a column for you (although that could be nifty!)

Also there isn't a facit for Column properties, like there is for Table. You might be able to make the Multipart Name facit do it, but nothing comes to me off the top of my head.

One thing to note about DMF Policys to prevent actions is it only seems useful (unless I am missing something) when used with scheduling the policies for regular checking as well, as I could create a table with the CreateDate column just to pass policy, then sp_rename it to something else straight after.