数据库 SQL Server 修改 表结构 脚本 sql语句

在使用一些表管理工具，或对数据库的物理表结构进行改动时，可以通过数据库的语法脚本实现。

1.创建表

CREATE TABLE dbo.表名
	(
	 列名1 [列名数据类型](数据长度) 是否为Null,
     列名2 [列名数据类型](数据长度) 是否为Null(Null/Not Null),
	)  ON [PRIMARY]
ALTER TABLE dbo.表名 ADD CONSTRAINT
	PK_表名 PRIMARY KEY CLUSTERED 
	(
	 主键列名  --若是联合主键 则为 列名1,列名2
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

示例：

创建一个表名为 Table_1的 表，指定mainkey列为自增主键 且从10000 开始，步长为1递增

CREATE TABLE dbo.Table_1
	(
	mainkey int NOT NULL IDENTITY (10000, 1),
	a char(1) NULL,
	b nvarchar(50) NULL,
	c ntext NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
	ALTER TABLE dbo.Table_1 ADD CONSTRAINT
	PK_Table_1 PRIMARY KEY CLUSTERED 
	(
	mainkey 
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

2.设置表结构里列字段说明

为表的某一列 添加说明 

EXECUTE sp_addextendedproperty N'MS_Description', N'列的说明信息', N'SCHEMA', N'dbo', N'TABLE', N'表名', N'COLUMN', N'列名'

注意：添加说明 是 sp_addextendedproperty 更新说明 是sp_updateextendedproperty 删除说明是 sp_dropextendedproperty

示例：

为刚才的Table_1表 的 b列 添加 ‘b列说明’ ，以及 c列 添加 ‘c列说明’

EXECUTE sp_addextendedproperty N'MS_Description', N'b列的说明', N'SCHEMA', N'dbo', N'TABLE', N'Table_1', N'COLUMN', N'b'
EXECUTE sp_addextendedproperty N'MS_Description', N'c列的说明', N'SCHEMA', N'dbo', N'TABLE', N'Table_1', N'COLUMN', N'c'

想确保不报异常，可以采用如下方式 进行设置 列字段说明

IF EXISTS (SELECT 1 FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '表名', 'column',default) where objname='列名')
EXECUTE sp_updateextendedproperty N'MS_Description', N'列的说明', N'SCHEMA', N'dbo', N'TABLE', N'表名', N'COLUMN', N'列名'
ELSE
EXECUTE sp_addextendedproperty N'MS_Description', N'列的说明', N'SCHEMA', N'dbo', N'TABLE', N'表名', N'COLUMN', N'列名'

 

3.设置表的列字段的 默认值

IF EXISTS (select * from sysobjects where name='DF_表名_列名')
ALTER TABLE dbo.表名 DROP CONSTRAINT DF_表名_列名
ALTER TABLE dbo.表名 ADD CONSTRAINT DF_表名_列名 DEFAULT N'默认值' FOR 列名

这里是先采用 drop 将列名的约束值信息丢掉，然后再添加进去，确保不报异常

若是想要读取某个表的默认值 就用

select TM.name, text defaultValue 
from sysobjects TM
left join syscomments TB
on TM.id = TB.id
where name like '%表名%'
and xtype = 'D' --xtype D标识默认值  PK 标识是 主键

4.添加一列

IF NOT EXISTS ( SELECT TOP 1 1
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   [TABLE_NAME] = '表名'
                        AND [COLUMN_NAME] = '新字段名')
BEGIN
    ALTER TABLE dbo.表名 ADD 列名 数据类型(长度) 是否为NULL
END

示例：Table_1里加了个 "newField"列

IF NOT EXISTS ( SELECT TOP 1 1
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   [TABLE_NAME] = 'Table_1'
                        AND [COLUMN_NAME] = 'newField')
BEGIN
    ALTER TABLE dbo.Table_1 ADD newField nchar(10) NULL
END
--或采用下面这种 但是不太靠谱
IF NOT EXISTS (select 1 from sysobjects where name='DF_Table_1_newField ')
ALTER TABLE dbo.Table_1 ADD newField nchar(10) NULL

5.更改列名

EXECUTE sp_rename N'dbo.表名.旧列名', N'Tmp_新列名', 'COLUMN' 
EXECUTE sp_rename N'dbo.表名.Tmp_新列名', N'新列名', 'COLUMN' 

示例：将 Table_1 的 b列 更名为 newB

EXECUTE sp_rename N'dbo.Table_1.b', N'Tmp_newB', 'COLUMN' 
EXECUTE sp_rename N'dbo.Table_1.Tmp_newB', N'newB', 'COLUMN' 

6.删除列

先去删除约束 再去删除列，描述字段 删除列时 自动删除了

IF EXISTS (select 1 from sysobjects where name='DF_表名_列名')
ALTER TABLE dbo.表名 DROP CONSTRAINT DF_表名_列名
ALTER TABLE dbo.表名 DROP COLUMN 列名

7.更改表结构

例如 ：变更字段的数据类型、变更字段的 是否为Null, 变更表结构的主键构成，这些都属于变更表结构，此时需要先建一张临时表，把数据全部拷走，再重新建表把数据拷贝回来，并把数据改为对应的。

主要先创建一个 Tmp_表名 的临时表，然后 通过 CONVERT 函数 进行数据转换 CONVERT(新列对应的数据类型(长度), 旧列名)

然后通过 sp_rename 把 Tmp_表名 更名为 原本的表名。

利用事件回滚机制 防止出现错误
 

BEGIN TRANSACTION 

执行体

COMMIT

注意：这里的主键声明 要放到 表重命名后，用 更名后的表建立约束。 

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_表名
	(
	表列配置项
	)  ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.表名)
	 EXEC('INSERT INTO dbo.Tmp_表名 (新列名1, 新列名2, 新列名3)
		SELECT 旧列名1, CONVERT(新列名2的数据类型, 旧列名2), 旧列名3 FROM dbo.表名 WITH (HOLDLOCK TABLOCKX)')
DROP TABLE dbo.表名
EXECUTE sp_rename N'dbo.Tmp_表名', N'表名', 'OBJECT' 
ALTER TABLE dbo.表名 ADD CONSTRAINT
	PK_表名 PRIMARY KEY CLUSTERED 
	(
	主键列
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT

示例：

将Table_1的

b列 更新列名为 newB  并更新数据类型为 ntext → nvarchar(100)

c列 仅更新列名 c → newCName

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table_1
	(
	mainkey int NOT NULL IDENTITY (1, 1),
	newB nvarchar(100) NULL,
	newCName nchar(10) NULL
	)  ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table_1)
	 EXEC('INSERT INTO dbo.Tmp_Table_1 (mainkey , newB, newCName )
		SELECT mainkey , CONVERT(nvarchar(100), b), c FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
DROP TABLE dbo.Table_1
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT' 
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
	PK_Table_1 PRIMARY KEY CLUSTERED 
	(
	mainkey 
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT

ps:若表结构里还有默认值，列说明，相关语句请参照各对应SQL添加到 Commit之前即可

8.其他

对于sql server 的其他语句，可以通过 生成脚本来查看

 

附：

sql语句读取一个表的相关配置项

SELECT  
        a.colorder as colOrder, 
        d.name as tableName,
        a.name as FieldName, 
        convert(int,case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end) as isIndex, 
        convert(int,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) as isPk, 
        b.name as sqlTypeName, 
        a.length as 'length', 
        convert(int,COLUMNPROPERTY(a.id,a.name,'PRECISION')) as 'size', 
        convert(int,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)) as 'scale', 
        convert(int,case when a.isnullable=1 then 1 else 0 end) as 'Nullable', 
        convert(varchar(100), isnull(e.text,'')) as defaultVal, 
        convert(varchar(255), isnull(g.[value],'')) as remarks 
 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='表名'
order by  
        a.id,a.colorder