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 新表名;

 

查看表属性

sp_help XXXX;
sp_helptext XXXX;
sp_depends XXXX;
sp_columns XXXXX;
--返回可在当前环境中查询的对象列表。这代表可在FROM子句中出现的任何对象
exec sp_tables;
--查询数据库中的所有数据库名：
SELECT Name FROM Master..SysDatabases ORDER BY Name;
--获取数据库内所有用户新增的表的语句，在数据库中创建的每个对象（例如约束、默认值、日志、规则以及存储过程）都有对应一行
select * from sysobjects where xtype='U';
select * from sys.all_objects where type='U';
--为每个表对象返回一行
select * from sys.tables;

---查询表属性
 select
     c.colorder 字段序号
     ,c.name as name,t.name as type
     ,convert(bit,c.IsNullable)  as isNULL
     ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
         select name from sysindexes where indid in(
             select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end)
                 as isKey
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as isIdentity

     ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as length
     ,ISNULL(CM.text,'') as defualtVal
     ,isnull(ETP.value,'') AS describe
     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description'
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('F_CUST_BOOK_MSTR') ;
--查询表属性
SELECT 表名称= d.name,
       表说明= isnull(f.value,''),
       排序号= a.colorder,
       列名称= a.name,
       列说明= isnull(g.[value],''),
       标识列= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end,
       主键列= case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
       列类型= b.name,
       字节数= a.length,
       列长度= COLUMNPROPERTY(a.id,a.name,'PRECISION'),
       小数位= isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
       允许空= case when a.isnullable=1 then 1 else 0 end,
       默认值= isnull(e.text,'')
FROM  syscolumns a
LEFT JOIN  systypes b on a.xusertype=b.xusertype
INNER JOIN sysobjects d on  a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
LEFT JOIN  syscomments e on  a.cdefault=e.id
LEFT JOIN  sys.extended_properties g on  a.id=G.major_id and a.colid=g.minor_id  
LEFT JOIN  sys.extended_properties f on  d.id=f.major_id and f.minor_id=0
WHERE d.name = 'XXXX'    --可修改表名，并且名字只是表名，不需要加数据库名字
ORDER BY 表名称 ASC,排序号 ASC;
--查询表属性
SELECT (case when a.colorder=1 then d.name else null end) 表名,isnull(f.value,'') 表说明,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
 (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in   (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  AND (xtype = 'PK'))>0 then '√' else '' end) 主键,
 b.name 类型,
 a.length 占用字节数,  
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
 (case when a.isnullable=1 then '√'else '' end) 允许空,  
 isnull(e.text,'') 默认值,
 isnull(g.[value], ' ') AS [说明] FROM  syscolumns a 
 left join systypes b on a.xtype=b.xusertype  
 inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
 left join syscomments e on a.cdefault=e.id  
 left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id 
 left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0  --d.id = f.class AND f.name = ‘MS_Description‘
 where b.name is not null  and d.name= 'XXXX' order by a.id,a.colorder;

 --sql 查询库中某表中所有字段及字段类型、属性等
 --SELECT column_name,column_type,IS_NULLABLE,column_key FROM information_schema.columns WHERE table_schema= '库名' AND table_name = '表名';

 --表（Department）
 
/******添加********/
--为表添加描述信息
execute sys.sp_addextendedproperty N'MS_Description',N'部门表',N'Schema',N'dbo',N'table',N'Department',null,null
go
--为字段添加描述信息
execute sys.sp_addextendedproperty N'MS_Description',N'部门表ID',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
go
/******添加********/
 
/******修改********/
--把表 Department 的扩展属性（表的描述）原值 ‘部门表’改为 ‘部门表测试修改’
execute sp_updateextendedproperty N'MS_Description',N'部门表测试修改',N'Schema',N'dbo',N'table',N'Department',null,null
 
--把表 Department 的列 D_Remark 的扩展属性（列的描述）原值 ‘部门描述’改为 ‘部门详细描述’
execute sp_updateextendedproperty N'MS_Description',N'部门详细描述',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
/******修改********/
 
/******删除********/
--删除表 Department 的扩展属性（表的描述）
execute sp_dropextendedproperty N'MS_Description',N'Schema',N'dbo',N'table',N'Department',null,null
 
--删除表 Department 的列 D_Remark 的扩展属性（列的描述）
execute sp_dropextendedproperty N'MS_Description',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark' ;
/******删除********/

---查出每个列定义的字段类型,字段长度
select a.name,b.name from syscolumns a inner join systypes b on a.xtype=b.xtype where a.id=object_id( 'tableName ');

select name,type_name(xtype),length,* from syscolumns where id=object_id( '表名 ');