sql-server - tutorial - tsql sql server

Columna de caída de SQL Server 2005 con restricciones

Question

Tengo una columna con una restricción "DEFAULT". Me gustaría crear un script que elimine esa columna.

El problema es que devuelve este error:

Msg 5074, Level 16, State 1, Line 1  
The object ''DF__PeriodSce__IsClo__4BCC3ABA'' is dependent on column ''IsClosed''. 
Msg 4922, Level 16, State 9, Line 1 
ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

No pude encontrar una manera fácil de descartar una columna y todas sus restricciones asociadas (solo encontré grandes scripts que miran en la tabla del sistema ... DEBE (!) Ser una "buena" manera de hacerlo).

Y como el nombre de la restricción DEFAULT se ha generado aleatoriamente, no puedo soltarlo por su nombre.

Actualización :
El tipo de restricción es "POR DEFECTO".

Vi las soluciones que todos ustedes propusieron, pero las encuentro realmente "sucias" ... ¿No creen? No sé si es con Oracle o MySQL, pero es posible hacer algo como:

DROP COLUMN xxx CASCADE CONSTRAINTS 

Y elimina todas las restricciones relacionadas ... O al menos elimina automáticamente las restricciones asignadas a esa columna (¡al menos restricciones de CHECK!)

¿No hay nada como eso en MSSQL?

Answer 1

Simplemente genere scripts para la tabla. Ahí puede encontrar el nombre de todas las restricciones.

Answer 2

¿Qué quieres decir al azar generado? Puede buscar las restricciones en la columna específica en Management Studio o a través de la vista sys.tables y buscar cuáles son los nombres.

Luego, puede cambiar su secuencia de comandos para eliminar las restricciones antes de soltar la columna. ¿Qué tipo de restricción es esto? Si se trata de una restricción de clave externa, asegúrese de que hacerlo no perjudicará la integridad de los datos en su base de datos.

Answer 3

Creo que eliminar explícitamente las restricciones antes de eliminar la columna es una solución "limpia". De esta manera, no abandones las restricciones de las que quizás no estés consciente. Si la caída aún falla, usted sabe que quedan restricciones adicionales. Me gusta tener el control de exactamente lo que está sucediendo en mi base de datos.

Además, la creación de scripts de las caídas garantiza explícitamente la secuencia de comandos y, con suerte, los resultados se pueden repetir exactamente de la manera que usted desea.

Answer 4

Puede obtener los nombres de restricciones consultando las vistas del sistema information_schema.

select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = ''<tablename>'' AND COLUMN_NAME = ''IsClosed''

Answer 5

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> WHERE TABLE_NAME = ''<tablename>'' AND COLUMN_NAME = ''IsClosed''

No es la solución correcta, como se explica aquí: http://msdn.microsoft.com/en-us/library/aa175912.aspx que:

Desafortunadamente, el nombre de la restricción predeterminada de columna no se mantiene en la vista COLUMNAS ANSI, por lo que debe volver a las tablas del sistema para encontrar el nombre

La única forma que encontré para obtener el nombre de la restricción DEFAULT es esta solicitud:

select  
    t_obj.name              as TABLE_NAME
    ,c_obj.name             as CONSTRAINT_NAME
    ,col.name               as COLUMN_NAME

from    sysobjects  c_obj
join    sysobjects  t_obj on c_obj.parent_obj = t_obj.id  
join    sysconstraints con on c_obj.id  = con.constid
join    syscolumns  col on t_obj.id = col.id
            and con.colid = col.colid
where
    c_obj.xtype = ''D''

¿Soy el único que se encuentra loco al no poder eliminar fácilmente una restricción que solo afecta a las columnas que intento eliminar?
Necesito ejecutar una solicitud con 3 uniones solo para obtener el nombre ...

Answer 6

Esta consulta encuentra restricciones predeterminadas para una tabla dada. No es bonito, estoy de acuerdo:

select 
    col.name, 
    col.column_id, 
    col.default_object_id, 
    OBJECTPROPERTY(col.default_object_id, N''IsDefaultCnst'') as is_defcnst, 
    dobj.name as def_name
from sys.columns col 
    left outer join sys.objects dobj 
        on dobj.object_id = col.default_object_id and dobj.type = ''D'' 
where col.object_id = object_id(N''dbo.test'') 
and dobj.name is not null

[EDIT] Actualizado por comentario de Julien N

Answer 7

Tal vez podría ayudar un poco más:

declare @tablename nvarchar(200)
declare @colname nvarchar(200)
declare @default sysname, @sql nvarchar(max)

set @tablename = ''your table''
set @colname = ''column to drop''

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id(@tablename)
AND type = ''D''
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(@tablename)
    and name = @colname 
    )

set @sql = N''alter table '' + @tablename + '' drop constraint '' + @default
exec sp_executesql @sql

set @sql = N''alter table '' + @tablename + '' drop column '' + @colname
exec sp_executesql @sql

Solo es necesario configurar las variables @tablename y @colname para descartar la columna.

Answer 8

También creo que es una falla en el servidor SQL no tener una caída en cascada disponible. Me centré en ello consultando las tablas del sistema de la misma manera que otras personas que se describen aquí:

• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE solo enumera claves externas, claves principales y restricciones exclusivas.
• La única forma de buscar restricciones predeterminadas es buscarlas en sys.default_constraints.
• Lo que no se ha mencionado aún aquí es que los índices también hacen que falle una columna, por lo que también debe descartar todos los índices que usan su columna antes de continuar con la eliminación de una columna.

El script resultante no es bonito, pero lo puse en un procedimiento almacenado para poder reutilizarlo:

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500)
AS

SELECT CONSTRAINT_NAME, ''C'' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, ''C''
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, ''I''
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN ''C'' THEN ''ALTER TABLE ['' + @tablename + ''] DROP CONSTRAINT ['' + @dep_name + '']''
            WHEN ''I'' THEN ''DROP INDEX ['' + @dep_name + ''] ON dbo.['' + @tablename + '']''
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = ''ALTER TABLE ['' + @tablename + ''] DROP COLUMN ['' + @columnname + '']''

print @sql
EXEC sp_executesql @sql

Answer 9

Me encontré con esto. Puede eliminar la columna con restricciones utilizando la vista de diseño MSSQL. Haga clic con el botón derecho en la columna que desea eliminar (con o sin restricciones) y podrá eliminar esto sin ningún problema. Ha ... Ya parecía estúpido.

Answer 10

Buscar el nombre del contraint o usar la vista de diseño de MSSQL no siempre es una opción. Actualmente quiero hacer un script para eliminar una columna con un contraint en él. Usar el nombre no es una opción ya que el nombre se genera y quiero usar el script en diferentes entornos Dev / Sys / Prod / etc. No es posible usar el nombre porque los nombres de los términos diferirán según el entorno. Probablemente tenga que mirar la tabla de sistemas, pero estoy de acuerdo en que debería haber una opción más fácil disponible.

Answer 11

La respuesta de pvolders fue justo lo que necesitaba, pero no detecté las estadísticas que causaban y error. Este es el mismo código, menos la creación de un procedimiento almacenado, además de enumerar las estadísticas y soltarlas. Esto es lo mejor que se me ocurre, así que si hay una forma mejor de determinar qué estadísticas se deben eliminar, agregue.

DECLARE @tablename nvarchar(500), 
        @columnname nvarchar(500)

SELECT  @tablename = ''tblProject'',
        @columnname = ''CountyKey''


SELECT CONSTRAINT_NAME, ''C'' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, ''C''
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, ''I''
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

INSERT INTO #dependencies
SELECT s.NAME, ''S''
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(@tableName)
AND c.NAME = @columnname
AND s.NAME LIKE ''_dta_stat%''

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN ''C'' THEN ''ALTER TABLE ['' + @tablename + ''] DROP CONSTRAINT ['' + @dep_name + '']''
            WHEN ''I'' THEN ''DROP INDEX ['' + @dep_name + ''] ON dbo.['' + @tablename + '']''
            WHEN ''S'' THEN ''DROP STATISTICS ['' + @tablename + ''].['' + @dep_name + '']''
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = ''ALTER TABLE ['' + @tablename + ''] DROP COLUMN ['' + @columnname + '']''

print @sql
EXEC sp_executesql @sql

Answer 12

Para basarme en la respuesta de Jeremy Stein, creé un procedimiento almacenado para esto y lo configuré para que se pueda usar para eliminar una columna que tiene o no restricciones predeterminadas. No es muy eficiente, ya que está consultando sys.columns dos veces, pero funciona.

CREATE PROCEDURE [dbo].[RemoveColumnWithDefaultConstraints] 
    -- Add the parameters for the stored procedure here
    @tableName nvarchar(max), 
    @columnName nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @ConstraintName nvarchar(200)
    SELECT @ConstraintName = Name 
    FROM SYS.DEFAULT_CONSTRAINTS 
    WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
        AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = (@columnName) 
        AND object_id = OBJECT_ID(@tableName))
    IF @ConstraintName IS NOT NULL
        EXEC(''ALTER TABLE '' + @tableName + '' DROP CONSTRAINT '' + @ConstraintName)

    IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName  
        AND Object_ID = Object_ID(@tableName))
        EXEC(''ALTER TABLE '' + @tableName + '' DROP COLUMN '' + @columnName)       
END
GO

Answer 13

Aquí hay una secuencia de comandos que eliminará la columna junto con su restricción predeterminada. Reemplace MYTABLENAME y MYCOLUMNNAME apropiadamente.

declare @constraint_name sysname, @sql nvarchar(max)

select @constraint_name = name 
from sys.default_constraints 
where parent_object_id = object_id(''MYTABLENAME'')
AND type = ''D''
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(''MYTABLENAME'')
    and name = ''MYCOLUMNNAME''
    )

set @sql = N''alter table MYTABLENAME drop constraint '' + @constraint_name
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go