How to select the default values of a table?

Question

In my app, when letting the user enter a new record, I want to preselect the database's default values.

Let's for example take this table:

CREATE TABLE pet (
    ID INT NOT NULL,
    name VARCHAR(255) DEFAULT 'noname',
    age INT DEFAULT 1
)

I would like to do something like this:

SELECT DEFAULT VALUES FROM pet -- NOT WORKING

And it should return:

ID   | name   | age
--------------------
NULL | noname | 1

I would then let the user fill in the remaining fields, or let her change one of the defaults, before she clicks on "save".

How can I select the default values of a sql server table using tsql?

Answer 1

It seems like this might be solution:

SELECT * FROM (
   SELECT
    sys1.name AS COLUMN_NAME,
    replace(replace(object_definition(sys1.default_object_id),'(',''),')','') AS DEFAULT_VALUE
FROM sys.columns AS sys1
     LEFT JOIN information_schema.columns ON sys1.name = information_schema.columns.column_name
WHERE object_id = object_id('pet')
  AND information_schema.columns.table_name = 'pet'
) AS SourceTable PIVOT(MAX(DEFAULT_VALUE) FOR COLUMN_NAME IN(ID, name, age)) AS PivotTable;

It returns:

ID  |name  |age
----|------|---
NULL|noname|1

Probably the column types are incorrect - but maybe I can live with that. Thanks for @Nissus to provide an intermediate step to this.