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

at

3 comments:

  1. Banking SoftwareNovember 16, 2010 at 10:19 PM

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

    ReplyDelete
  2. UnknownFebruary 18, 2016 at 12:57 PM



    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/

    ReplyDelete
  3. UnknownAugust 29, 2016 at 9:51 AM

    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?

    ReplyDelete