1

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?

slartidan
  • 20,403
  • 15
  • 83
  • 131
  • 1
    Does this answer your question? [SQL Server: Find out default value of a column with a query](https://stackoverflow.com/questions/3817885/sql-server-find-out-default-value-of-a-column-with-a-query) – Heinzi Jul 22 '20 at 08:31
  • No, I do not want to select the default value of one column. I want to select all default values, in the format of a record of that table. – slartidan Jul 22 '20 at 08:32
  • Unfortunately, it's not so simple. You can query some system views; I have linked your question to a duplicate that shows how to do that. An additional caveat is that advanced permissions (`VIEW DEFINITION`) are required for that. – Heinzi Jul 22 '20 at 08:32
  • There's nothing built in for that. You will have to manually get the default value for each column and use some SQL to transform it into the result format that you want. (Our comments overlapped, my previous comment was to your question in general, not to your comment.) – Heinzi Jul 22 '20 at 08:33
  • I know, that I can for example select the default values like that: `SELECT COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pet' ORDER BY ORDINAL_POSITION` - however I want to get the values as a record. Maybe someone else has an idea. – slartidan Jul 22 '20 at 08:35
  • Once I created a function similar to your question but it was nonsense since you can press alt + f1 on any table in your query on management studio and it provides you every info you need. (keys, types of columns, default values etc.) I found out about it after :) – Nissus Jul 22 '20 at 08:35
  • 2
    Another idea would be to (a) start a transaction, (b) insert a new row with a dummy ID (and dummy values for all other non-default-value non-null columns), (c) read out the row and (d) revert the transaction. That's dirty, but it should do what you need. – Heinzi Jul 22 '20 at 08:36
  • I really wish there were a method to do exactly what you need - it would allow us to easily copy default values from the DB layer to the UI layer. – Heinzi Jul 22 '20 at 08:38
  • Application and database defaults can often be different @Heinzi . In SQL Server, the default value is for when the column is omitted. Application default values are prefiled values for the user (who can still delete those values). – Thom A Jul 22 '20 at 08:44
  • This, honestly, feels like an [XY Problem](http://xyproblem.info). Database and application defaults aren'tthe same thing. – Thom A Jul 22 '20 at 08:50
  • @Larnu: I agree, if often makes sense for them to be different, and database defaults are often abused for default values which should rather (only) be specified at the application layer. However, there are cases where there's a good reason for both to be the same (e.g. backwards compatibility with old clients), and, in such cases, such a method could be useful ("do not repeat yourself" applies here as well). – Heinzi Jul 22 '20 at 08:59

4 Answers4

2

You don't "SELECT" the Default values, only insert them. A SELECT returns the rows from a table, you can't SELECT the DEFAULT VALUES as there's no such row inside the table.

You could do something silly, like use a TRANSACTION and roll it back, but as ID doesn't have a default value, and you don't define a value for it with DEFAULT VALUES, it'll fail in your scenario:

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

BEGIN TRANSACTION;

INSERT INTO dbo.pet
OUTPUT inserted.*
DEFAULT VALUES;

ROLLBACK;

Msg 515, Level 16, State 2, Line 13 Cannot insert the value NULL into column 'ID', table 'Sandbox.dbo.pet'; column does not allow nulls. INSERT fails.

You can, therefore, just supply the values for your non-NULL columns:

BEGIN TRANSACTION;

INSERT INTO dbo.pet (ID)
OUTPUT inserted.*
VALUES(1);

ROLLBACK;

Which will output the "default" values:

ID|name  |age
--|------|---
 1|noname|1
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Not really sure what's worst - your workaround or mine... both are ugly but they will get the job done :-) – Zohar Peled Jul 22 '20 at 08:40
  • I like neither, if I am honset, @ZoharPeled . It gets very messy once things like `IDENTITY` and `NEWSEQUENTIALID` are throw in too. Honestly, I suspect what the OP has here is an XY Problem. – Thom A Jul 22 '20 at 08:42
  • An interesting approach - especially the `OUTPUT`-clause seems handy for the rollback. However I would still have to define *magic values* for the non-null columns (which have to be valid, etc.). – slartidan Jul 22 '20 at 08:42
  • Well, if you don't know the definition of your database, who does, @slartidan ? – Thom A Jul 22 '20 at 08:43
2

Selecting the default values of all columns is not very straight-forward, and as Heinzi wrote in his comment - does require a level of permissions you normally don't want your users to have.

That being said, a simple workaround would be to insert a record, select it back and display to the user, let the user decide what they want to change (if anything) and then when they submit the record - update the record (or delete the previous record and insert a new one).

That would require you to have some indication if the record was actually reviewed and updated by the user, but that's easy enough to accomplish by simply adding a bit column and setting it to 1 when updating the data.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

As I have commented before. There is no need for this query since you can press alt + f1 on any table in your editor in Management Studio and provide you every information you need for the table.

select sys1.name 'Name',replace(replace(
        case
            when object_definition(sys1.default_object_id) is null then 'No Default Value'
            else object_definition(sys1.default_object_id)
        end ,'(',''),')','') 'Default value',
        information_schema.columns.data_type 'Data type'
        from   sys.columns as sys1
        left join information_schema.columns on sys1.name = information_schema.columns.column_name
        where  
        object_id = object_id('table_name')
        and information_schema.columns.table_name = 'table_name'
Nissus
  • 306
  • 3
  • 13
  • This outputs the default values as rows, not as columns. Maybe a `PIVOT` might help... However the types are lost as well (int values displayed as text). – slartidan Jul 22 '20 at 08:45
  • 1
    *"However the types are lost as well (int values displayed as text)."* Not a lot you can do there. You can't `CONVERT`/`CAST` a value to a dynamic datatype. – Thom A Jul 22 '20 at 08:46
  • That's why there is variable type next column so you can convert in your code. You are right about pivot/unpivot but that's a query I wrote 2 months ago and I posted it raw. – Nissus Jul 22 '20 at 08:48
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.

slartidan
  • 20,403
  • 15
  • 83
  • 131