definition column from sys.check_constraints is coming as null in ado.net datareader when the field is not null when executed from sssms

[Ala Venkatachalam]

I am trying to write a C# program to read all the check constraints for a given table from ms sql server database. I am using sys.check_constraints table to get this information, when I tried to read this using ado.net datareader the column 'definition' from this table is always coming as empty though it has value.

SQL Script

GO

/****** Object: Table [dbo].[Customer] Script Date: 7/10/2019 3:24:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[CompanyId] [smallint] NOT NULL,
[Prefix] [varchar](4) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NULL,
[Suffix] [varchar](4) NULL,
[NickName] [varchar](10) NULL,
[ProfilePictureName] [varchar](50) NULL,
[Company] [varchar](100) NULL,
[DateOfBirth] [date] NOT NULL,
[DateOfDeath] [date] NULL,
[Gender] [char](1) NOT NULL,
[Type] [varchar](10) NOT NULL,
[IsActive] [char](1) NULL,
[CreatedBy] [varchar](50) NOT NULL,
[CreatedOn] [datetime2](7) NOT NULL,
[UpdatedBy] [varchar](50) NULL,
[UpdatedOn] [datetime2](7) NULL,
[Process] [varchar](100) NULL,
[MessageId] [varchar](50) NOT NULL,
[SysStartDate] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndDate] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([SysStartDate], [SysEndDate])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[MSSQL_TemporalHistoryFor_601769201] )
)
GO

ALTER TABLE [dbo].[Customer] ADD DEFAULT ('Y') FOR [IsActive]
GO

ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CUSTOMER_PREFIX_CHECK] CHECK (([Prefix]='Dr' OR [Prefix]='Miss' OR [Prefix]='Ms' OR [Prefix]='Mrs' OR [Prefix]='Mr'))
GO

ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CUSTOMER_PREFIX_CHECK]
GO

ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CUSTOMER_SUFFIX_CHECK] CHECK (([Suffix]='RN' OR [Suffix]='MD' OR [Suffix]='ESQ' OR [Suffix]='DO' OR [Suffix]='DDS' OR [Suffix]='DDM' OR [Suffix]='III' OR [Suffix]='II' OR [Suffix]='I' OR [Suffix]='Sr' OR [Suffix]='Jr'))
GO

ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CUSTOMER_SUFFIX_CHECK]
GO

ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [CUSTOMER_TYPE_CHECK] CHECK (([Type]='Customer' OR [Type]='VENDOR' OR [Type]='INSIDER' OR [Type]='ANALYST' OR [Type]='INVESTOR'))
GO

ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CUSTOMER_TYPE_CHECK]
GO




CREATE PROCEDURE [dbo].[GetCheckConstraints_Dup](@tableName as varchar(200))
AS
BEGIN
select
col.[name] as column_name,
con.[definition] As [constraint_value]
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
where t.[name]=@tableName and con.is_disabled=0
order by con.name;
End;

GO

exec GetCheckConstraints_Dup 'Customer'





C# Code

public static List<Constraints> GetConstraints(string tableName,string connectionString)
{
var constraints = new List<Constraints>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
//SqlDataReader
connection.Open();
SqlCommand cmd = new SqlCommand("DBO.GetCheckConstraints_Dup", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tableName", SqlDbType.VarChar).Value = tableName;

using (SqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
constraints.Add(new Constraints() { FieldName = dataReader["column_name"].ToString(), Constraint = ConstraintType.Check, ConstraintValue = dataReader["constraint_value"].ToString() });

}
}
}

return constraints;
}

Continue reading...