Friday, May 02, 2008

Checking for default constraints on SQL Server 2000 AND 2005 - CORRECTION!

Unfortunately there isn't a simple way to check for default constraints on a column which works on both SQL Server 2000 and 2005. To get around this problem, I created the following function:

CREATE FUNCTION DefaultConstraintExists(@SchemaAndTableName sysname, @Column sysname)
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
DECLARE @columnId INT
--Try and get the columnID - only works on SQL Server 2000
SELECT @columnId = [info] FROM sysobjects
WHERE [xtype] = 'D'
AND [parent_obj] = OBJECT_ID(@SchemaAndTableName)
AND COL_NAME([parent_obj], [info]) = @Column
--If that failed, try and get it in a way that works on SQL Server 2005 (There is no way that works on both)
IF @columnId IS NULL
BEGIN
SELECT @columnId = COLUMNPROPERTY(OBJECT_ID(@SchemaAndTableName), @Column, 'ColumnId')
END
--Now see if the default constraint exists
IF EXISTS (Select * From sysobjects where xtype = 'D' and parent_obj = object_id(@SchemaAndTableName)
and col_name(parent_obj, @columnId) = @Column)
BEGIN
SET @Result = 'T'
END
ELSE
BEGIN
SET @Result = 'F'
END
RETURN @Result;
END
go


The above doesn't work properly on SQL Server 2005, it returns true if there are any constraints on the table, not just the desired field.
The following is simpler AND IT WORKS!


CREATE FUNCTION FDS.DefaultExists(@Schema varchar(10), @TableName varchar(100), @Column varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
DECLARE @default varchar(255)

SELECT @default = COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema
AND TABLE_NAME = @TableName
AND COLUMN_NAME = @Column

IF @default IS NULL
BEGIN
SET @Result = 'F'
END
ELSE
BEGIN
SET @Result = 'T'
END
RETURN @Result;
END
go

0 comments:

Post a comment