Post reply

Need mechanism to Gen'' DB objects "defaults" DDL

  • March 30, 2005 at 12:33 pm

    #164153

    Anyone have a mechanism for extracting SQL Server Database "defaults" DDL only?  (excluding PK, FK, and Constraint DDL)

    In Ent. Mgr's "Generate SQL Scripts" Options tab, I have the option to gen' PK's, FK's, Defaults, and Check Constraints. 

    Because Snapshot replication is propagating the PK's & FK's (and NOT generating the Default definitions) I need to build and execute the DDL for defaults. (and maybe the Constraint DDL)

    thx in advance.

     

    BT

  • March 30, 2005 at 1:12 pm

    #549640

    There's still work to be done on this but you'll get the idea :

    Select USER_NAME(uid) as Owner,O.Name as TableName, C.Name as ColName, Object_name(C.cDefault) as ConstraintName, substring(Text, 2, len(Text) - 2) as DefaultValue from dbo.SysComments CM inner join dbo.SysColumns C ON CM.id = C.cDefault inner join dbo.SysObjects O on C.id = O.id and O.XType = 'U' order by O.Name, C.Name

    --generate the alter table stmnt with concatenation

    ALTER TABLE [Owner].[TableName] ADD CONSTRAINT [Prefixe + TableName + ColumnName] DEFAULT DefaultValue FOR [ColumnName]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply