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 as 'Table', 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.



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


Banking Software said...

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

Anvesh Patel 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.