Default restrictions and field names...

Hello,
I'm in the task of changing the data type of a column in a SQL Server
2000 database which already has default and check constraints.
Being unable to directly issue an ALTER TABLE command, I guess that the
only way will be to delete the check constraints, the default
constraints, change the data type and then recreate the check and
default constraints updated.
The names of the check constraints and their corresponding columns could
be obtained from the CONSTRAINT_COLUMN_USAGE view. But I still can't get
any system view that related the name of the default constraints and the
name of their corresponding table columns.
Any help will be much appreciated,
KlermanHi
CREATE TABLE Test
(
col DATETIME DEFAULT GETDATE()
)
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('Test')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
ALTER TABLE Test DROP CONSTRAINT DF__Test__col__6C190EBB
"Klerman Gutierrez" <klerman.gutierrez@.gmail.com> wrote in message
news:e2KvNGllFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm in the task of changing the data type of a column in a SQL Server 2000
> database which already has default and check constraints.
> Being unable to directly issue an ALTER TABLE command, I guess that the
> only way will be to delete the check constraints, the default constraints,
> change the data type and then recreate the check and default constraints
> updated.
> The names of the check constraints and their corresponding columns could
> be obtained from the CONSTRAINT_COLUMN_USAGE view. But I still can't get
> any system view that related the name of the default constraints and the
> name of their corresponding table columns.
> Any help will be much appreciated,
> Klerman|||Uri,
Based on your suggestion, I wrote this solution. Hope this could help
someone else trying to remove restrictions associated with a field.
Thanks a lot for the help
Klerman
EXEC pa_BorraRestricciones 'TableName', 'ColumnName'
CREATE PROCEDURE pa_BorraRestricciones(@.Tabla SYSNAME, @.Columna SYSNAME)
AS
DECLARE @.Restriccion SYSNAME,
@.strSQL NVARCHAR(2048)
DECLARE Restricciones CURSOR LOCAL FAST_FORWARD FOR SELECT
SYSOBJECTS.NAME FROM (SYSCONSTRAINTS INNER JOIN SYSOBJECTS ON
SYSCONSTRAINTS.constid=SYSOBJECTS.id) INNER JOIN SYSCOLUMNS ON
SYSCONSTRAINTS.colid=SYSCOLUMNS.colid WHERE
SYSCONSTRAINTS.id=OBJECT_ID(@.Tabla) AND SYSCOLUMNS.NAME=@.Columna
OPEN Restricciones
FETCH NEXT FROM Restricciones INTO @.Restriccion
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.strSQL = N'ALTER TABLE ' + @.Tabla + ' DROP CONSTRAINT ' +
@.Restriccion
EXEC (@.strSQL)
FETCH NEXT FROM Restricciones INTO @.Restriccion
END
CLOSE Restricciones
DEALLOCATE Restricciones
RETURN