SQL Server: Find out default value of a column with a query
You can find the stored definition with the below (remember to adjust the column and table name to find to be ones relevant to your environment!)
SELECT object_definition(default_object_id) AS definition
FROM sys.columns
WHERE name ='colname'
AND object_id = object_id('dbo.tablename')
sql server : get default value of a column
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.
How to select the default values of a table?
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.
How to return default value from SQL query
Assuming the name is not nullable and that Id
is unique so can match at most one row.
SELECT
ISNULL(MAX(Name),'John Doe')
FROM
Users
WHERE
Id = @UserId
Related Topics
Update Query Using Subquery in SQL Server
SQL Query to Obtain Value That Occurs More Than Once
Oracle:Select Maximum Value from Different Columns of the Same Row
Select Distinct from Multiple Fields Using SQL
The Network Adapter Could Not Establish the Connection - Oracle 11G
How to Create a Decimal Field in Access with Alter Table
What's the Difference Between Charfield and Textfield in Django
How to Insert Table Values from One Database to Another Database
Get Previous and Next Row from Rows Selected with (Where) Conditions
Oracle 'Partition By' and 'Row_Number' Keyword
How in H2Db Get SQL Dump Like in MySQL
SQL Server 2005 and Temporary Table Scope
Removing Duplicate Rows (Based on Values from Multiple Columns) from SQL Table
Eliminate and Reduce Overlapping Date Ranges
SQL Server: Find Out Default Value of a Column with a Query