Hej

Nu har jag kört fast (eller inte egentligen men vill slippa skriva massor av SQL kod). Vad jag vill göra är att lista alla constraints som finns på en kolumn en i en tabell. Jag vet att man kan få ut alla constrains som går mot en tabell mha sp_helpconstraints, men i detta fallet så vet jag vilken tabell OCH kolumn jag vill få ut constraintsen för. Dvs jag vill enbart få de constraints som ligger på just den kolumnen.

Ett sätt som jag har påbörjat är att ta sp_helpconstraints och hacka om delar av den, men det blir det så jäkla mycket kod då så jag udrar om det fins ett smidigare sätt?

Hmm, nja, det är inte så enkelt som du märkt. Det beror ju också lite på exakt vad du vill ha ut. Om det finns en check-constraint som inbegriper den aktuella kolumnen, ska den listas då? Om kolumnen ingår i en composite key, ska denna isf listas?

Du kan slänga ett öga på sp_columns och se om den ger något kanske. Man ska helst undvika att basera funktionalitet på system-tabellerna, men du kan ju om du vill titta på sysconstraints. Den har information om vilken kolumn en constraint påverkar och sedan kan du från syscomments och sysreferences (plus kanske sysindexes etc) få ut information att visa. Alternativt kan du titta på INFORMATION_SCHEMA-vyerna, t ex CONSTRAINT_COLUMNS_USAGE, KEY_COLUMN_USAGE etc. I alla fall tror jag dock tyvärr att det innebär en del kodande, det finns inget färdigt att använda för att lista constraints för en kolumn vad jag vet.

<code>
declare @TableName varchar(500)
declare @ColName varchar(500)
set @TableName = 'mintabell'
set @ColName = 'minkolumn'

declare @Tableid int
declare @i int
declare @cnstname sysname
declare @cnstid int
declare @cnsttype character(2)

set @Tableid = (select id from sysobjects where name = @TableName)


declare ms_crs_cnst cursor local static for
select id, xtype, name from sysobjects where parent_obj = @Tableid
and xtype in ('C ','PK','UQ','F ', 'D ')

create table #Tmp (ConstraintID sysname, cnsttype varchar(2))

open ms_crs_cnst
fetch ms_crs_cnst into @cnstid ,@cnsttype ,@cnstname
while @@fetch_status >= 0
begin
if @cnsttype in ('PK','UQ') -- Primärnycklar & unika constraints
begin
declare @Myname varchar(200)
select @Myname = index_col(@TableName, (select indid
from sysindexes
where name = object_name(@cnstid)
AND id = @Tableid),1)

if (@ColName = @Myname)
insert into #tmp values (@cnstname, @cnsttype)

set @i = 2
while @Myname is not null
begin
select @Myname = index_col(@TableName, (select indid
from sysindexes
where name = object_name(@cnstid)
and id = @Tableid), @i)
if (@ColName = @Myname)
insert into #tmp values (@cnstname, @cnsttype)

set @i = @i + 1
end
end
else
if @cnsttype = 'F ' -- Främmande nycklar
begin
declare @fkeyid int, @rkeyid int
select @fkeyid = fkeyid, @rkeyid = rkeyid from sysreferences where constid = @cnstid
declare @keys varchar(200)
declare ms_crs_fkey cursor local for select fkey, rkey from sysforeignkeys where constid = @cnstid
open ms_crs_fkey
declare @fkeycol smallint, @rkeycol smallint
fetch ms_crs_fkey into @fkeycol, @rkeycol
while @@fetch_status >= 0
begin
if ((select col_name(@fkeyid, @fkeycol)) = @ColName)
insert into #tmp values (@cnstname, @cnsttype)
fetch ms_crs_fkey into @fkeycol, @rkeycol
end
deallocate ms_crs_fkey
end
else
if (@cnsttype = 'C ') or (@cnsttype = 'D ') --checkconstraint & defaultconstraint
begin
if ((select col_name(@Tableid, (select info from sysobjects where id = @cnstid))) = @ColName)
insert into #tmp values (@cnstname, @cnsttype)
end

fetch ms_crs_cnst into @cnstid ,@cnsttype ,@cnstname
end
deallocate ms_crs_cnst
</code>

Löste det omedelbara behovet iaf. Tokful kod, men...

Tjena!

Här har du en annan variant. Intressant att namn på defaults inte existerar i INFORMATION_SCHEMA vyerna, så att man måste hämta från sysobjects...

<code>
declare @tablename sysname,
@columnname sysname

select @tablename = 'mytable', @columnname = 'mycolumn'

select tc.constraint_name,
tc.constraint_type
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON CCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
where CCU.table_name = @tablename
and CCU.column_name = @columnname
UNION ALL
select so.name,
'DEFAULT'
from sysobjects so
inner join
syscolumns sc
on sc.id=so.parent_obj
where sc.name = @columnname
and object_name(so.parent_obj) = @tablename
and so.xtype='D'

</code>