SQL Server 动态添加 表 列字段 主键 外键 存储过程 数据脱敏

向表中添加列
从表中删除列
重命名列
修改列

添加表

USE [dbTest]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sys_user]'))
begin
	print 'sys_user 已存在'
end
else
begin
    print 'sys_user 不存在'
	CREATE TABLE [dbo].[sys_user](
	    [id] [int] NULL,
	    [user_name] [nvarchar](50) NULL,
	    [age] [int] NULL
	)
	print 'sys_user 创建成功'
end
go

添加字段语法

alter table [表名] add [字段名] 数据类型 default 允许Null值     --【default 是可选参】

添加字段示例

--1
alter table asset add age int default 100 null
或
alter table asset add age int null default 100

--2
USE [dbTest]
GO

if exists(select * from syscolumns where id=object_id('Asset') and name='FinancialState') 
begin
    --alter table Asset drop column FinancialState  --删除字段
    print '已存在 [Asset].[FinancialState]'
end
else
begin
    alter table Asset add FinancialState int null
    --设置默认值
    --alter table Asset add constraint [df_Asset_FinancialState] default ((0)) for [FinancialState]
    print '添加成功 [Asset].[FinancialState]'
end

添加默认值

ALTER TABLE Asset add IsReturnAAA NVARCHAR(100) null 
ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_IsReturnAAA]  DEFAULT (N'abcd1234快快快') FOR [IsReturnAAA]

ALTER TABLE Asset add IsReturnBBB INT null 
ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_IsReturnBBB]  DEFAULT ((1)) FOR [IsReturnBBB]

ALTER TABLE Asset add IsReturnCCC BIT null 
ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_IsReturnCCC]  DEFAULT ((0)) FOR [IsReturnCCC]

ALTER TABLE Asset ADD  CONSTRAINT [DF_Asset_created_time]  DEFAULT (getdate()) FOR [created_time]

修改字段类型

ALTER TABLE [USER] ALTER COLUMN [NAME] VARCHAR(35) NULL

USE [dbTest]
GO

if exists(select * from syscolumns where id=object_id('sys_user') and name='password') 
begin
    ALTER TABLE sys_user ALTER COLUMN [password] NVARCHAR(250) NULL
    print '[sys_user].[password] 已修改'
end
else
begin
    print '[sys_user].[password] 不存在'
end

修改字段名称

execute sp_rename '表名.原字段名称','新字段名称'

execute sp_rename 'sys_notice.system_code','resource_code'

USE [dbTest]
GO

if exists(select * from syscolumns where id=object_id('sys_notice') and name='system_code') 
begin
    execute sp_rename 'sys_notice.system_code','resource_code'
    print '修改成功'
end
else
begin
    print '不存在'
end

添加字段说明【备注】信息

EXEC sp_addextendedproperty N'MS_Description', '输入您的备注内容', N'user', N'dbo', N'table', N'表名称', N'COLUMN', N'字段名称'
EXEC sp_addextendedproperty N'MS_Description', '用户姓名11111111', N'user', N'dbo', N'table', N'Employee', N'COLUMN', N'EmployeeName'

修改字段说明【备注】信息

EXEC sp_updateextendedproperty 'MS_Description','输入您的备注内容','user',dbo,'table','表名称','column',字段名称
EXEC sp_updateextendedproperty 'MS_Description','用户姓名22222222','user',dbo,'table','Employee','column',EmployeeName

添加主外键关联关系

/********* 添加主外键关联关系 *********/
ALTER TABLE TestQQ WITH CHECK ADD CONSTRAINT [FK_TestQQ_Switch] FOREIGN KEY([SupplyId])
REFERENCES Switch ([Id])
GO

ALTER TABLE TestQQ CHECK CONSTRAINT [FK_TestQQ_Switch]
GO

--语法
--ALTER TABLE 外键表名 WITH CHECK ADD CONSTRAINT [约束名称] FOREIGN KEY([外键字段])
--REFERENCES 主键表名 ([字段名称(主表id)])
--GO

--ALTER TABLE 外键表名 CHECK CONSTRAINT [约束名称]
GO

删除【自增】属性，【注意：自增属性不能直接删除】 
1、添加一个临时字段，赋值；
2、为临时字段赋值；
3、删除旧字段主键，删除旧字段
4、临时字段重命名

--base_room 添加临时字段
if exists(select * from syscolumns where id=object_id('base_room') and name='temp_room_code') 
begin
    print '[base_room].[temp_room_code] 已存在'
end
else
begin
	alter table [dbo].[base_room] add [temp_room_code] int null
    print '[base_room].[temp_room_code] 添加成功'
end
go

--base_room
if exists(select * from syscolumns where id=object_id('base_room') and name='room_code') 
begin
    --临时字符赋值
    update [dbo].[base_room] set temp_room_code = room_code
    --删除主键
    alter table [dbo].[base_room] drop constraint [pk_base_room]
    --删除字段
    alter table [dbo].[base_room] drop column [room_code]
    --重命名
    EXEC sp_rename 'base_room.temp_room_code','room_code','Column'
    --设置字段不允许为空
    alter table [dbo].[base_room] alter column [room_code] nvarchar(50) not null
    --设置主键
	alter table [dbo].[base_room] add constraint pk_base_room primary key clustered (room_code)
    print '[base_room].[room_code] 已修改'
end
else
begin
    print '[base_room].[room_code] 不存在'
end
go

在现有表中创建主键

alter table [dbo].[base_class] add constraint [pk_base_class] primary key clustered ([class_code])

创建联合主键

--创建联合主键
ALTER TABLE [dbo].[base_class] ADD CONSTRAINT
PK_base_class PRIMARY KEY 
(
	class_code,
	class_name
);

删除主键

--删除主键
ALTER TABLE [dbo].[base_class] DROP CONSTRAINT PK_base_class;

删除字段语法

alter table [表名] drop column [字段名]

删除字段示例

alter table asset drop column age

--
if exists(select * from syscolumns where id=object_id('asset') and name='age') 
begin
    alter table asset drop column age  --删除字段
    print '[asset].[age] 删除成功'
end
else
begin
    print '[asset].[age] 不存在'
end

delete、truncate、delete 区别
语法

drop table [表名]
truncate table [表名]
delete from [表名]

drop：删除表（结构和数据），删除内容和定义，表所占用的空间全释放掉；
truncate：一次性清空所有数据，不把单独的删除操作记录记入日志保存，删除行不能恢复；表和索引所占用的空间会恢复到初始大小；
delete：从表中删除单个，多个或所有记录；删除操作作为事务记录在日志中保存以便进行进行回滚操作；

一般而言，drop > truncate > delete

查询所有存储过程 

select distinct OBJECT_NAME(id),REPLACE(substring([text],charindex('ion',[text]),charindex('CREATE',[text])),'=============================================','')  from syscomments
where id IN(select id from sysobjects where type IN ('p'))
--AND text LIKE '%tablename%' 
order by OBJECT_NAME(id)

-----【生成删除存储过程的语句】-----
-----【生成删除存储过程的语句】-----
-----【生成删除存储过程的语句】-----
declare @num int = 1
declare @print_string varchar(1000) = N''
declare @proc_name varchar(100)
declare cursor1 cursor for    --定义游标cursor1
select distinct object_name(id) as proc_name from syscomments 
where id in(select id from sysobjects where type in ('p')) order by object_name(id)
open cursor1                  --打开游标
fetch next from cursor1 into @proc_name
while @@fetch_status=0        --判断是否成功获取数据
    begin
        set @print_string = N'
--' + convert(nvarchar(10),@num) + '、' + @proc_name + ' 存储过程
if exists (select 1 from sys.procedures where name = '''+ @proc_name +''')
begin
    print ''' + convert(nvarchar(10),@num) + '、存储过程 '+ @proc_name +' 已存在，删除重新创建''
	--drop procedure '+ @proc_name +'
end
'
        print @print_string
        set @num += 1
        fetch next from cursor1 into @proc_name
    end
close cursor1                 --关闭游标
deallocate cursor1            --删除游标

查询所有视图 

select distinct OBJECT_NAME(id) from syscomments
where id IN(select id from sysobjects where type IN ('v'))
--AND text LIKE '%tablename%' 
order by OBJECT_NAME(id)

存储过程

USE [dbTest]
GO

if exists (select 1 from sys.procedures where name = 'proc_test')
begin
    print '存储过程 proc_test 已存在，删除重新创建'
	DROP PROCEDURE proc_test
end

IF OBJECT_ID('proc_test', 'P') IS NOT NULL
BEGIN
    print '存储过程 proc_test 已存在，删除重新创建'
	DROP PROCEDURE proc_test
END
GO  

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE proc_test 
AS
BEGIN
	SELECT * from sys_user
END
GO

查看表名注释，说明

SELECT st.name tablename, sep.value tabledesc
FROM sys.tables st
JOIN sys.extended_properties sep
ON sep.major_id = st.object_id
AND minor_id = 0

表添加注释，说明

EXEC sys.sp_addextendedproperty 'MS_Description', '用户表','SCHEMA','dbo','TABLE','sys_user';

数据脱敏 

--concat()、left()和right()字符串函数组合使用
--concat(str1,str2,…)：返回结果为连接参数产生的字符串
--left(str,len)：返回从字符串str 开始的len 最左字符
--right(str,len)：从字符串str 开始，返回最右len 字符

--电话号码脱敏
select phone as 脱敏前电话号码,concat(left(phone,3), '********') as 脱敏后电话号码 from sys_user
select phone as 脱敏前电话号码,concat(left(phone,3), '****', right(phone,4)) as 脱敏后电话号码 from sys_user

--身份证号码脱敏
select id_card as 未脱敏身份证, concat(left(id_card,6),'********',right(id_card,4)) as 脱敏后身份证号 from sys_user
 
--电话号码、身份证脱敏
select left(real_name,1) + replicate('*',len(real_name)-1) as 脱敏后姓名
,left(id_card,4) + '****' + substring(id_card,9,1) + '*' + right(id_card,1) as 脱敏后身份证号
from sys_user

*
*
*
*
*
*