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