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')

1.创建表
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EVADELSECURITYPROJECT]') AND type in (N'U'))
BEGIN
CREATE TABLE TABLENAME
(
  ....
)
END

2.--判断表中列是否存在，不存在添加
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='dbo' AND table_name='TABLENAME' AND column_name='IsPush')
BEGIN
...
END;
IF NOT EXISTS (SELECT * FROM syscolumns WHERE ID=object_id('dbo.TableName') AND Name='ColName') 
BEGIN
....
END;

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

修改表结构包括：增加字段、删除字段、增加约束、删除约束、修改缺省值、修改字段数据类型、重命名字段、重命名表。所有这些操作都是用 alter table 命令执行的。

1、增加字段，增加字段需要注意的是如果源表里面已经有相应的值，那么新增加的字段需要设置为可以为null,不然会造成增加异常。它的语法是这样的：

alter table 表名 add 字段名 字段类型;

你也可以同时在该字段上定义约束，使用通常的语法，如：

alter table 表名 add  字段名 字段类型 check (添加约束的字段名 <> '');

实际上，所有在　create table　里描述的可以应用于字段之选项都可以在这里使用。不过，我们要注意的是缺省值必须满足给出的约束，否则　add　将会失败。

2、 删除字段

要删除一个字段，使用下面这样的命令：

alter table 表名 drop column 字段列名;

不管字段里有啥数据，都会消失。和这个字段相关的约束也会被删除。 不过，如果这个字段被另外一个表的外键所引用，postgresql 则不会隐含地删除该约束。你可以通过使用 cascade 来授权删除任何依赖该字段的东西,如:

alter table 表名 drop column 字段名 cascade;

 3、增加约束

要增加一个约束，使用表约束语法。比如：

alter table 表名 add check (字段名 <> '');

alter table 表名 add constraint 约束名称 unique (字段名);

4、 删除约束

要删除一个约束，语法是这样的：

alter table 表名 drop constraint 约束名;（如果你在处理一个生成的约束名，比如 $2，别忘了你需要给它 添加双引号，让它成为一个有效的标识符。）

和删除字段一样，如果你想删除有着被依赖关系地约束，你需要用 cascade。 一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。

除了非空约束外，所有约束类型都这么用。要删除非空类型，用

alter table 表史 alter column 字段名 drop not null;（要记得非空约束没有名字。）

5、改变一个字段的缺省值

要给一个字段设置缺省值，使用一个象下面这样的命令：

alter table 表名 alter column 字段名 set default 默认值;

这里一定要注意这么做不会影响任何表中现有的数据行， 它只是为将来　insert　命令改变缺省值。

同时如果要删除缺省值，用

alter table 表名 alter column 字段名 drop default;

这样实际上相当于把缺省设置为空。 结果是，如果我们删除一个还没有定义的缺省值不算错误，因为缺省隐含就是空值。

6、 修改一个字段的数据类型，这是我们用得很多的操作。语法是这样的：

alter table 表名 alter column 字段名 type 新的类型;

只有在字段里现有的每个项都可以用一个隐含的类型转换转换城新的类型时才可能成功。也就是说新类型和源始类型必须存在兼容的关系，类似于程序语言中的自动转换时所要求的条件。如果需要更复杂的转换，你可以增加一个 using 子句，它声明如何从旧值里计算新值。

这个方法将试图把字段的缺省值（如果存在）转换成新的类型， 还有涉及该字段的任何约束。但是这些转换可能失败，或者可能生成奇怪的结果。 在修改某字段类型之前，你最好删除那些约束，然后再把自己手工修改过的添加上去。

7、给字段改名字

alter table 表名 rename column 源字段名 to 新字段名;

8、给表改名字

alter table 表名 rename to 新表名;