sql server : get default value of a column

Question

I execute a select to get the structure of a table. I want to get info about the columns like its name or if it's null or if it's primary key.. I do something like this

....sys.columns c...
c.precision,
c.scale,
c.is_nullable as isnullable,
c.default_object_id as columndefault,
c.is_computed as iscomputed,

but for default value i get the id..something like 454545454 but i want to get the value "xxxx". What is the table to search or what is the function to convert that id to the value. Thanks

AdaTheDev · Accepted Answer · 2010-04-07 09:00:25Z

11

You can do this (done a SELECT * just so you can see all the info available):

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE....

This includes a "COLUMN_DEFAULT" column in the resultset.

answered Apr 7, 2010 at 9:00

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

codingbadger · Accepted Answer · 2010-04-07 09:01:34Z

7

Use

Select * From INFORMATION_SCHEMA.COLUMNS

there is a column called COLUMN_DEFAULT

answered Apr 7, 2010 at 9:01

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

Comments

AlexC · Accepted Answer · 2010-04-07 09:00:43Z

1

The property you want is called "cdefault".

http://sql-server-performance.com/Community/forums/p/20588/114944.aspx

answered Apr 7, 2010 at 9:00

AlexC

3092 silver badges11 bronze badges

Comments

Dan Menes · Accepted Answer · 2013-05-02 14:14:26Z

1

'bills' is an example table

select 
COLUMN_DEFAULT            --default
,IS_NULLABLE              -- is nullable
,NUMERIC_PRECISION        --number of digits (binary or decimal depending on radix)
,NUMERIC_PRECISION_RADIX  --decimal places
,NUMERIC_SCALE            --number of digits to right of decimal point
,COLUMNPROPERTY(OBJECT_ID('bills'),COLUMN_NAME,'Iscomputed') AS ISCOMPUTED --is computed
 from INFORMATION_SCHEMA.columns where TABLE_name='bills'

 select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='bills' and CONSTRAINT_TYPE='PRIMARY KEY'

edited May 2, 2013 at 14:14

Dan Menes

6,8572 gold badges37 silver badges39 bronze badges

answered Apr 7, 2010 at 9:16

sergiol

111 bronze badge

1 Comment

Dan Menes Over a year ago

This originally said that NUMERIC_RADIX is the number of decimal places. That is incorrect. The radix is the base of the number system in which the value is stored (2 or 10). The NUMERIC_PRECISION is the total number of stored digits (decimal or binary, depending on the radix), and the NUMERIC_SCALE is the number of digits to the right of the decimal point

Erick de Vathaire · Accepted Answer · 2023-03-08 00:01:42Z

If you want to focus on the DEFAULT values, the built-in function GETDEFAULT can help, but as long as it requires to use numbers (you can't use a DB_ID(), a column or even a variable with the function) to escape this I made a procedure:

CREATE OR ALTER PROC PRC_GETDEFAULT(@TABLE_NAME VARCHAR(200)=NULL) AS BEGIN

SELECT
    PK=ROW_NUMBER()OVER(ORDER BY COLUMN_ID)
    ,COLUMN_NAME = C.NAME
    ,DEFAULT_NAME = DC.NAME
    ,COLUMN_ID
    ,DEFAULT_OBJECT_ID = DC.OBJECT_ID
    ,DC.DEFINITION
    ,DB_ID=CONVERT(INT,DB_ID())
    ,DEFAULT_VALUE = CONVERT(SQL_VARIANT, NULL)
INTO #GET_DEFAULT
FROM SYS.DEFAULT_CONSTRAINTS DC
JOIN SYS.COLUMNS C ON DC.PARENT_OBJECT_ID = C.OBJECT_ID AND DC.PARENT_COLUMN_ID = C.COLUMN_ID
WHERE PARENT_OBJECT_ID = OBJECT_ID(@TABLE_NAME)

DECLARE @END INT = @@ROWCOUNT
DECLARE @I INT = 0
DECLARE @QORIGINAL VARCHAR(MAX) = '
    UPDATE #GET_DEFAULT
    SET DEFAULT_VALUE = GETDEFAULT(' + LTRIM(DB_ID()) + ','
DECLARE @QEXECUTE VARCHAR(MAX)
WHILE @I < @END BEGIN
    SET @I += 1
    SELECT @QEXECUTE = CONCAT(@QORIGINAL, DEFAULT_OBJECT_ID, ')
        WHERE PK = ', @I)
    FROM #GET_DEFAULT
    WHERE PK = @I

    EXEC(@QEXECUTE)
END

SELECT
    *
FROM #GET_DEFAULT
END

This procedure only needs the table name and returns all the columns from the table that have default values and the active default values, example:

exec PRC_GETDEFAULT TB_EXAMPLE
--or
exec PRC_GETDEFAULT 'DBO.TB_EXAMPLE'

Collectives™ on Stack Overflow

sql server : get default value of a column

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related