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: