List all Default Values in a SQL Server Database

I had earlier written a query to Find a Column Default value using T-SQL. Here’s how to find the default value of all columns in all tables of a database

SELECT obj.name as 'Table', col.name as 'Column',
object_definition(default_object_id) AS [DefaultValue]
FROM sys.objects obj INNER JOIN sys.columns col
ON obj.object_id = col.object_id
where obj.type = 'U'

The sys.objects and sys.columns provides us with the metadata needed to find the default values of all columns in a database.

OUTPUT

image


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

3 comments:

Banking Software said...

I'm the beginner for SQL...
Wonderful Practical idea... nice blog...

Unknown said...



Nice Article !

Really this will help to people of SQL Server Community.
I have also prepared small note on this, How to find default value of columns in SQL Server.

http://www.dbrnd.com/2015/10/sql-server-script-to-find-all-default-values-with-columns/

Unknown said...

Just one question. How can we print this default values or assign default values to declared variables dynamically? The problem is , when we try retrieving the default values from the table, using Object_definition, it gives string/varchar with parenthesis. Any idea how to get rid of that?