SQL语句增删改字段、表、修改默认值

收集转载：

1.修改字段，默认值

1、修改字段默认值

alter table 表名 drop constraint 约束名字   ------说明：删除表的字段的原有约束

alter table 表名 add constraint 约束名字 DEFAULT 默认值 for 字段名称 -------说明：添加一个表的字段的约束并指定默认值

2、修改字段名：

alter table 表名 rename column A to B

3、修改字段类型：

alter table 表名 alter column UnitPrice decimal(18, 4) not null 

4、修改增加字段：

USE DB
GO
--test表增加col1字段
--作者：
--日期：
IF NOT EXISTS
(SELECT * FROM syscolumns WHERE ID=object_id('test') AND Name='col1') 
BEGIN
ALTER Table test ADD col1 bit 
END

alter table 表名 ADD 字段 类型 NOT NULL Default 0

5.删除字段

当动态添加 表的 列时，如果设置了该列的 默认值，那么再用alter table TableName drop column ColumnName语句时就会报错，因为存在了default约束。

1.查找出表中该列的约束名称

declare @name varchar(50)
select  @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault 
where a.id = object_id('TableName') 
 and a.name ='ColumName'

2. 删除 约束 删除该列 即可

exec('alter table TableName drop constraint ' + @name)
exec('alter table TableNamedrop column ColumName')

2.执行函数 存储过程 列前

/******在生成的函数CREATE脚本前面加这一段（如果函数存在，先删除） ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FunTest]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[FunTest]
GO


/******在生成的存储过程CREATE脚本前面加这一段（如果存存储过程在，先删除） ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Test]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[SP_Test]
GO


/******在为表增加列的脚本前面加这一段（如果表存在列不存在才执行） ******/
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Ttest]') AND type in (N'U')) AND COL_LENGTH('Ttest','WeChatORCodeUrl') IS NULL)
BEGIN
    ALTER TABLE Ttest ADD WeChatORCodeUrl VARCHAR(500)
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'企业号二维码路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TMobileConfig', @level2type=N'COLUMN',@level2name=N'WechatORCodeUrl'
END
GO

3.判断表 临时表是否存在

1、判断数据表是否存在

　　方法一：

use yourdb;
go

if object_id(N'tablename',N'U') is not null
print '存在'
else 
print '不存在'


例如：
use fireweb;
go

if object_id(N'TEMP_TBL',N'U') is not null
print '存在'
else 
print '不存在'



方法二：

USE [实例名] 
GO 

IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'[表名]') AND OBJECTPROPERTY(ID, 'IsTable') = 1) 
PRINT '存在' 
ELSE 
PRINT'不存在'


例如：
use fireweb;
go

IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'TEMP_TBL') AND OBJECTPROPERTY(ID, 'IsTable') = 1) 
PRINT '存在' 
ELSE 
PRINT'不存在'

2、临时表是否存在：

方法一：
use fireweb;
go

if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##TEMP_TBL'))
PRINT '存在' 
ELSE 
PRINT'不存在'


方法二：
use fireweb;
go

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#TEMP_TBL') and type='U')
PRINT '存在' 
ELSE 
PRINT'不存在'

 

4.动态添加表，修改列

1. 创建表：
CREATE TABLE 学生信息
(
    学号 varchar(14) IDENTITY(1,1) PRIMARY KEY,
    姓名 varchar(8) UNIQUE NOT NULL,
    班级编号 varchar(14) REFERENCES '班级信息',
    年级 int null,
    性别 varchar(2) CHECK(性别in ('男’','女’)),
    民族 varchar(20) DEFAULT '未知该生民族',
    籍贯 varchar(50)
 )


2. 修改表：
A. 重命名表：
EXEC sp_rename 'oldname','newname'
B. 修改列属性：
ALTER TABLE 学生信息
ALTER COLUMN 姓名 varchar(20) NOT NULL
C. 添加列：
ALTER TABLE 学生信息
ADD 家庭住址 nvarchar(20) NULL
D. 删除列：
ALTER TABLE 学生信息
DROP COLUMN 家庭住址

D. 修改列名：
exec sp_rename '表名.[字段原名]','字段新名','column'



3. 复制表：
A. 复制整张表：
select * into new_table from old_table

B. 复制表结构：
select * into new_table from old_table where 1=2

B. 复制表内容：
insert into new_tab select * from old_table



4. 修改identity列

自增列不能直接修改，必须将原有ID列删除，然后重新添加一列具有identity属性的ID字段。比如你要修改的字段名为ID：
alter table 表名 drop column ID
 alter table 表名 add ID int identity(1,1)

 5.重命名列 说明

-- 重命名 表.列 ，新的 ，列
EXEC sp_rename '表.列', '新列', 'COLUMN' 

-- 修改列说明
USE EMDB
GO
IF EXISTS (SELECT 1 FROM SYSCOLUMNS WHERE ID=OBJECT_ID('表名') AND NAME='列名')
BEGIN
    EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名'
END