Post reply

Retrieving default column value from Sysobject, syscolumns

  • May 16, 2005 at 3:36 pm

    #67696

    Hi all -

    I am building a program to script tables.  I missed picking up the default value assigned to a column.

    I see where syscolumns has an id for the default in cdefault.  I believe that it points to sysobjects but I don't see where sysobjects stores the default value.

    Can anyone help me here?

    TIA

  • May 16, 2005 at 4:42 pm

    #559145

    Half help:

    Old defaults' (not necessarily declared constraints--I'm going from memory) source are/were stored in syscomments because they were like compiled code snippets.

    Half jab:  Go with the "round" concept with that newfangled "wheel" thing you're inventing...

  • May 16, 2005 at 7:59 pm

    #559159

    c:\program files\microsoft sql server\mssql\upgrade\scptxfr.exe /s <servername> /P <password> /d northwind /f northwind.sql

  • May 17, 2005 at 6:41 am

    #559233

    this is from the middle of a loop which joined sysobjects and syscolumns....

    i've basically done what you are doing now...scripting out the table definition, complete with defaults, contraints etc.

     

    I stick mine in an XML document, and then have an application which reads the XML and compares it to a database, returning any differences.because the XML is published to a web site, the clients can use my executable to validate there schema at there end, and see if they are missing any objects.

     

    --DEFAULT CONSTRAINT: can be only one per column

          SELECT  @DEFAULTVALUE=ISNULL([text],'') FROM syscomments WHERE id = (SELECT id FROM sysobjects WHERE xtype='D' AND parent_obj=@TABLE_ID AND info=@CURRCOLUMN) ;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 17, 2005 at 7:15 am

    #559245

    Thanks all.  I've got it...

Viewing 5 posts - 1 through 4 (of 4 total)

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