Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: Query which system table to answer this

    I have a DB with 1 default defined: UW_Zerodefault

    It simply puts a 0 into particular fields upon new record creation.

    Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    SqlSpec will give you this information, but I can't remember now what table it's hitting to get it. it may be as simple as querying sysdepends.

    I'll take a look at the code tonight and post again.

  3. #3
    Join Date
    Sep 2004
    Location
    Birmingham, UK
    Posts
    18
    If I understand your query correctly (It's late and I'm tired!!) you can run exec sp_mshelpcolumns 'enter table name' and the col_dridefname column is the contraint name and the first text column is the default value.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    this give you all default usage by columns:
    Code:
    select 
    	s.name as colname
    	,o1.name as tablename
    	,o2.name as defaultname
    from 
    	syscolumns s
    inner join 
    	sysobjects o1 on o1.id=s.id
    inner join
    	sysobjects o2 on o2.id=s.cdefault
    and this will give you default usage by udts:
    Code:
    exec sp_mshelptype @typename=null, @flags='uddt'

Posting Permissions

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