Get Field List which is Set with A Default Value in a Database

In this blog, by using a script I want to display a field list, which is set with a default value in a database. Suppose, we have a table called Countries and the structure, given as follows:

CREATE TABLE [dbo].[Countries](
[CountryID]
[INT] IDENTITY(1,1) NOT NULL,
[CountryCode]
[NVARCHAR](5) NULL,
[CountryCodeLong]
[NVARCHAR](5) NULL,
[CountryName]
[NVARCHAR](50) NULL,
[CountryDescription]
[NVARCHAR](100) NULL,
[Nationality]
[NVARCHAR](20) NULL,
[CreatedDate]
[DATETIME] NULL,
[IsActive]
[BIT] NOT NULL
CONSTRAINT
[PK_Mindcracker_Countries] PRIMARY KEY CLUSTERED

(

[CountryID]
ASC

)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]

)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[Countries] ADD CONSTRAINT [Countries_CreatedDate] DEFAULT (GETDATE()) FOR [CreatedDate]
GO
ALTER
TABLE [dbo].[Countries] ADD CONSTRAINT [Countires_IsActive] DEFAULT ((1)) FOR [IsActive]
GO


In the given above script, we have 2 fields, which are set with default values. By using the given below script, we can see all the fields which have been set with the default values in a database.

SELECT s.name AS 'Schema', ts.name AS TableName,
c.name AS column_name,
t
.name AS Datatypename,
m
.text AS defaultValue

FROM
sys.columns AS c
INNER
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER
JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER
JOIN sys.schemas s ON s.schema_id = ts.schema_id
inner
join syscomments m
ON
m.id=c.default_object_id

ORDER
BY s.name, ts.name, c.column_id

The result will display like this: