Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    15

    Unanswered: Need help with a query that retrieves DEFAULT constraints

    Hi guys,

    I am trying to write a query that seaches for all DEFAULT constraints in a database and prints the column with the default value, the value of the default, and the name of the default constraint.

    So far I have the two queries that let me get all the information I need.
    Code:
    SELECT Name FROM sysobjects
     WHERE xtype = 'D'
    
    SELECT column_default, column_name FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE column_default IS NOT Null
    I am unable to merge them into one query. I cannot seem to find a commun field to both of them.

    Many thanks in advance.
    Darkneon

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Code:
    SELECT 
    s.name as DefaultName,
    c.COLUMN_NAME,
    c.COLUMN_DEFAULT
    FROM sysobjects s,
    INFORMATION_SCHEMA.COLUMNS c
    WHERE 
    xtype = 'D'
    and COLUMN_DEFAULT IS NOT null
    and c.TABLE_NAME=(SELECT name FROM sysobjects where id=s.parent_obj)
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Sep 2005
    Posts
    15
    Thanks for your input mallier, but it does not give me the right result.

    It actually outputs the same constraint name for each column, for all constraints in a table.

  4. #4
    Join Date
    Sep 2005
    Posts
    15
    Found it !!!

    It took me hours, literally, and it is completely different from the method I proposed first.

    Code:
    SELECT object_name(scol.cdefault) as "ConstraintName",
               object_name(scol.id) as "Table", 
               scol.name as "Column",
               sc.text "DefaultValue" 
       FROM syscomments sc, syscolumns scol
     WHERE sc.id = scol.cdefault

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •