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

3 comments:

  1. Typically the 18K pink-gold cambered sapphire precious stone claim provides you with some firmness from 9 as well as some patek philippe replica height from 41. 5mm. There is also a old watches Excel at Compressor close off at the claim spine as well as water resistant towards (100m). Various to choose from claim might be stainless. The average straps are actually matte chocolate-colored straps with the help of match finder system sheet metal deployment buckle. What's more , chanel replica sale seems to have charms constructed from match finder system sheet metal being the claim to boot. Typically the dial comes into play two to three styles, specifically exceptional charcoal, grilling with charcoal greyish and / or slate greyish. Typically the dial goes along with luminescent numerals with the help of 7 luminescent per hour marker pens. Typically the rhodium-plated trapeze-shaped luminescent per hour not to mention hour brass wrists and hands are actually associated with the help of vivid white lacquered-brass small-seconds wrists and hands with the help of inflammed tips and hints. Ought to be which the 52-parts branded 5-dual axis form concerning sheet metal bracelet fabricated from outstanding towards Jaegar-LeCoultre. This unique bracelet goes along with easily summer/winter and / or morning/evening length-change letting breitling replica sale wonderful treatment finished 2x4mm. Typically the old watches devices are actually polished. Jaeger-LeCoultre can be described as high-end follow brand name which has been launched from Antoine LeCoultre through 1833. Antoine developed buy in your smallish place from Le Sentier, Swiss. Antoine isn't solely curious about getting looks after despite the fact, the guy was basically curious about reliability timepieces.

    ReplyDelete
  2. atlanta falcons, http://www.atlantafalconsjersey.us/
    michael kors outlet online, http://www.michaelkorsusa.us/
    mbt sneakers, http://www.mbtshoesoutlet.us.com/
    dallas cowboys, http://www.dallascowboysjersey.us/
    oklahoma city thunder, http://www.thunderjerseystore.com/
    san francisco 49ers, http://www.sanfrancisco49ersjerseys.us/
    cheap uggs, http://www.uggsale.in.net/
    polo ralph lauren uk, http://www.ralphlauren-outletonline.co.uk/
    chicago bulls, http://www.chicagobullsjerseys.net/
    babyliss hair dryer, http://www.babyliss.us.com/
    supra sneakershttp://www.suprashoes.us.com
    ray ban sunglasses, http://www.ray-bansunglasses.eu.com/
    toms shoes outlet online, http://www.toms.us.com/
    snapback hats, http://www.cheapsnapbacks.us.com/
    fitflop sandals, http://www.fitflop.in.net/
    louis vuitton outlet,louis vuitton outlet online,louis vuitton,louisvuitton.com,authentic louis vuitton,louis vuitton factory outlet,cheap louis vuitton
    ralph lauren sale, http://www.ralphlaurenoutlet.in.net/
    oakley outlet online, http://www.oakleyoutlet.in.net/
    kkk10 13

    ReplyDelete