I am writing a sql script for dropping column and a default constraint. The following script works fine but i like to know if it is a right way of doing it.
我正在编写一个用于删除列和默认约束的sql脚本。以下脚本工作正常,但我想知道它是否是一种正确的方法。
Can i drop a default constraint with a column in one statement instead of using two separate ones?
我可以在一个语句中删除带有列的默认约束而不是使用两个单独的约束吗?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID]
END
GO
BEGIN
ALTER TABLE [dbo].[Employees] DROP COLUMN [EmpID]
END
4 个解决方案
#1
12
In SQL Server 2005 upwards you can drop both the constraint and the column in one statement.
在SQL Server 2005中,您可以在一个语句中删除约束和列。
The syntax is
语法是
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ]
The emphasis is on [ ,...n ], indicating multiple terms.
重点是[,... n],表示多个术语。
NB! Since the terms are processed sequentially, if the column being dropped is part of the constraint being dropped, then the constraint must be the first term, followed by the column term.
NB!由于这些术语是按顺序处理的,如果要删除的列是要删除的约束的一部分,则约束必须是第一个术语,后跟列术语。
In your example:
在你的例子中:
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
So your code would be:
所以你的代码是:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
END
GO
In SQL Server 2016 they have introduced the IF EXISTS clause which removes the need to check for the existence of the constraint first e.g.
在SQL Server 2016中,他们引入了IF EXISTS子句,它不需要首先检查是否存在约束,例如:
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT IF EXISTS [DF_Employees_EmpID], COLUMN IF EXISTS [EmpID]
#2
5
Here is another way to drop a column & default constraints with checking if they exist before dropping them:
这是另一种删除列和默认约束的方法,在删除它们之前检查它们是否存在:
-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
IF EXISTS ( SELECT 1
FROM sys.default_constraints
WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
)
BEGIN
------ DROP Contraint
ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
END
-- ----- DROP Column -----------------------------------------------------------------
ALTER TABLE [dbo].table_Emplyee
DROP COLUMN Column_EmployeeName
PRINT 'Column Column_EmployeeName in images table was dropped'
END
--------------------------------------------------------------------------
-- ADD COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
----- ADD Column & Contraint
ALTER TABLE dbo.table_Emplyee
ADD Column_EmployeeName BIT NOT NULL
CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] DEFAULT (0)
PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
END
GO
#3
0
How you have it is fine.
你怎么拥有它很好。
An alternative would be
另一种选择
IF OBJECT_ID('DF_Employees_EmpID', 'D') IS NULL
BEGIN
ALTER TABLE dbo.Employees
DROP COLUMN EmpID
END
ELSE
BEGIN
ALTER TABLE dbo.Employees
DROP CONSTRAINT DF_Employees_EmpID,
COLUMN EmpID
END
In the event that the constraint does exist this combines the two operations into a single statement/transaction.
如果约束确实存在,则将两个操作组合成单个语句/事务。
#4
0
Another solution:
DECLARE @TableName sysname,
@Schema sysname,
@colname sysname,
@sql VARCHAR(1000)
SELECT @Schema = 'dbo',
@TableName = 'mytable',
@colname = 'mycol'
IF COL_LENGTH(@Schema+'.'+@TableName, @colname) IS NULL
BEGIN
PRINT 'Column does not exist!'
END
ELSE
BEGIN
SET @sql = ''
SELECT @sql += N' ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + default_constraints.name + ';'
FROM sys.all_columns
INNER JOIN sys.tables
ON all_columns.object_id = TABLES.object_id
INNER JOIN sys.schemas
ON TABLES.schema_id = schemas.schema_id
INNER JOIN sys.default_constraints
ON all_columns.default_object_id = default_constraints.object_id
WHERE schemas.name = @Schema
AND tables.name = @TableName
AND all_columns.name = @colname
SET @sql += N' ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @colname + ';'
PRINT ISNULL(@sql, 'NULL')
EXECUTE(@sql)
END
#1
12
In SQL Server 2005 upwards you can drop both the constraint and the column in one statement.
在SQL Server 2005中,您可以在一个语句中删除约束和列。
The syntax is
语法是
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ]
The emphasis is on [ ,...n ], indicating multiple terms.
重点是[,... n],表示多个术语。
NB! Since the terms are processed sequentially, if the column being dropped is part of the constraint being dropped, then the constraint must be the first term, followed by the column term.
NB!由于这些术语是按顺序处理的,如果要删除的列是要删除的约束的一部分,则约束必须是第一个术语,后跟列术语。
In your example:
在你的例子中:
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
So your code would be:
所以你的代码是:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
END
GO
In SQL Server 2016 they have introduced the IF EXISTS clause which removes the need to check for the existence of the constraint first e.g.
在SQL Server 2016中,他们引入了IF EXISTS子句,它不需要首先检查是否存在约束,例如:
ALTER TABLE [dbo].[Employees] DROP CONSTRAINT IF EXISTS [DF_Employees_EmpID], COLUMN IF EXISTS [EmpID]
#2
5
Here is another way to drop a column & default constraints with checking if they exist before dropping them:
这是另一种删除列和默认约束的方法,在删除它们之前检查它们是否存在:
-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
IF EXISTS ( SELECT 1
FROM sys.default_constraints
WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
)
BEGIN
------ DROP Contraint
ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
END
-- ----- DROP Column -----------------------------------------------------------------
ALTER TABLE [dbo].table_Emplyee
DROP COLUMN Column_EmployeeName
PRINT 'Column Column_EmployeeName in images table was dropped'
END
--------------------------------------------------------------------------
-- ADD COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
----- ADD Column & Contraint
ALTER TABLE dbo.table_Emplyee
ADD Column_EmployeeName BIT NOT NULL
CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] DEFAULT (0)
PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
END
GO
#3
0
How you have it is fine.
你怎么拥有它很好。
An alternative would be
另一种选择
IF OBJECT_ID('DF_Employees_EmpID', 'D') IS NULL
BEGIN
ALTER TABLE dbo.Employees
DROP COLUMN EmpID
END
ELSE
BEGIN
ALTER TABLE dbo.Employees
DROP CONSTRAINT DF_Employees_EmpID,
COLUMN EmpID
END
In the event that the constraint does exist this combines the two operations into a single statement/transaction.
如果约束确实存在,则将两个操作组合成单个语句/事务。
#4
0
Another solution:
DECLARE @TableName sysname,
@Schema sysname,
@colname sysname,
@sql VARCHAR(1000)
SELECT @Schema = 'dbo',
@TableName = 'mytable',
@colname = 'mycol'
IF COL_LENGTH(@Schema+'.'+@TableName, @colname) IS NULL
BEGIN
PRINT 'Column does not exist!'
END
ELSE
BEGIN
SET @sql = ''
SELECT @sql += N' ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + default_constraints.name + ';'
FROM sys.all_columns
INNER JOIN sys.tables
ON all_columns.object_id = TABLES.object_id
INNER JOIN sys.schemas
ON TABLES.schema_id = schemas.schema_id
INNER JOIN sys.default_constraints
ON all_columns.default_object_id = default_constraints.object_id
WHERE schemas.name = @Schema
AND tables.name = @TableName
AND all_columns.name = @colname
SET @sql += N' ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @colname + ';'
PRINT ISNULL(@sql, 'NULL')
EXECUTE(@sql)
END