项目04(Sql server添加旬表01)

最新推荐文章于 2022-01-04 11:06:26 发布
最新推荐文章于 2022-01-04 11:06:26 发布
阅读量180 收藏
点赞数
分类专栏： Sql Server 文章标签： sqlserver
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baidu_34007305/article/details/110873568
版权
此博客介绍了如何使用SQL游标批量创建数据库旬表，包括表结构、索引和用户权限设置。示例代码展示了从每月1张表扩展到每月3张表的过程，并提供了删除所有表、添加索引、分配用户权限的步骤。此外，还提到了基础表的数据插入，以引导外部数据写入分表。
摘要由CSDN通过智能技术生成

背景

每年12月，为线上数据库添加旬表(一般为每月3张，格式为tablenameby202112_1,tablenameby202112_2,tablenameby202112_3) 可根据需要更改。(其中包括表结构，索引，用户权限等)

写在前面
本次使用的是游标，因为使用频率为1年添加1次，频率不高。读者可以根据需要建立存储过程并建立计划任务，使其自动执行。

例子：
create procedure [dbo].[Create_points_table]    --存储过程里面放置游标
as
begin
您的游标内容(自定义哟)
end

每月3张

#其中根据需要更改起止时间，本次为2021年每月
declare @tablename varchar(50)
declare @sql01 varchar(1000)
declare @sql02 varchar(1000)
declare @sql03 varchar(1000)
declare Create_points_table cursor  
		for select convert(varchar(6),dateadd(mm,number,'2021-01-01'),112) --每月3张旬表（通过varchar(6)控制输出的字符数202001）
					from master..spt_values with(nolock) 
					where type='P' and number>=0 
					and dateadd(mm,number,'2021-01-01')<'2022-01-01'

	open Create_points_table
	
		fetch next from Create_points_table into @tablename
	while(@@fetch_status=0)
		begin

		set @sql01 = 'CREATE TABLE [dbo].[t_web_log_'+@tablename+'_1](
				[id] bigint primary key not null,
				[method_name] varchar(100) NULL ,
				[request_url] varchar(255) NULL ,
				[request_ip] varchar(50) NULL ,
				[request_params] text NULL ,
				[response_data] text NULL ,
				[time_span] int NULL default 0 ,
				[create_time] datetime NOT NULL ,
				[result_success] bit NULL DEFAULT ((0)) ,
				); CREATE INDEX [time_index] ON [dbo].[t_web_log_'+@tablename+'_1]
([create_time] DESC);'
		set @sql02 = 'CREATE TABLE [dbo].[t_web_log_'+@tablename+'_2](
				[id] bigint primary key not null,
				[method_name] varchar(100) NULL ,
				[request_url] varchar(255) NULL ,
				[request_ip] varchar(50) NULL ,
				[request_params] text NULL ,
				[response_data] text NULL ,
				[time_span] int NULL default 0 ,
				[create_time] datetime NOT NULL ,
				[result_success] bit NULL DEFAULT ((0)) ,
				); CREATE INDEX [time_index] ON [dbo].[t_web_log_'+@tablename+'_2]
([create_time] DESC);'
		set @sql03 = 'CREATE TABLE [dbo].[t_web_log_'+@tablename+'_3](
				[id] bigint primary key not null,
				[method_name] varchar(100) NULL ,
				[request_url] varchar(255) NULL ,
				[request_ip] varchar(50) NULL ,
				[request_params] text NULL ,
				[response_data] text NULL ,
				[time_span] int NULL default 0 ,
				[create_time] datetime NOT NULL ,
				[result_success] bit NULL DEFAULT ((0)) ,
				); CREATE INDEX [time_index] ON [dbo].[t_web_log_'+@tablename+'_3]
([create_time] DESC);'

		execute(@sql01)
		execute(@sql02)
		execute(@sql03)
		fetch next from Create_points_table into @tablename
		
		end
	close Create_points_table    --关闭
	deallocate Create_points_table  --释放

结果
在这里插入图片描述

每月1张

	declare @tablename varchar(50)
	declare @sql varchar(1000)
	declare Create_points_table cursor  
		for select convert(varchar(6),dateadd(mm,number,'2020-01-01'),112) --每月一账分表（通过varchar(6)控制输出的字符数202001）
					from master..spt_values with(nolock) 
					where type='P' and number>=0 
					and dateadd(mm,number,'2020-01-01')<'2021-01-01'

	open Create_points_table
	
		fetch next from Create_points_table into @tablename
	while(@@fetch_status=0)
		begin

		set @sql = 'CREATE TABLE [dbo].[t_web_log_'+@tablename+'](
				[id] bigint primary key not null,
				[method_name] varchar(100) NULL ,
				[request_url] varchar(255) NULL ,
				[request_ip] varchar(50) NULL ,
				[request_params] text NULL ,
				[response_data] text NULL ,
				[time_span] int NULL default 0 ,
				[create_time] datetime NOT NULL ,
				[result_success] bit NULL DEFAULT ((0)) ,
				); CREATE INDEX [time_index] ON [dbo].[t_web_log_'+@tablename+']
([create_time] DESC);'

		execute(@sql)
		fetch next from Create_points_table into @tablename
		
		end
	close Create_points_table    --关闭
	deallocate Create_points_table  --释放

结果
在这里插入图片描述
每月3张的语句就是从每月一张的语句基础上更改的

每天1张

	declare @tablename varchar(50)
	declare @sql varchar(1000)
	declare Create_points_table cursor  
		for select convert(varchar(50),dateadd(day,n.number,'2021-01-01'),112)
					from master.dbo.spt_values  n 
					where n.type = 'p'
					and number < datediff(day,'2021-01-01','2022-01-01')

	open Create_points_table
	
		fetch next from Create_points_table into @tablename
	while(@@fetch_status=0)
		begin

		set @sql = 'CREATE TABLE [dbo].[t_web_log_'+@tablename+'](
				[id] bigint primary key not null,
				[method_name] varchar(100) NULL ,
				[request_url] varchar(255) NULL ,
				[request_ip] varchar(50) NULL ,
				[request_params] text NULL ,
				[response_data] text NULL ,
				[time_span] int NULL default 0 ,
				[create_time] datetime NOT NULL ,
				[result_success] bit NULL DEFAULT ((0)) ,
				); CREATE INDEX [time_index] ON [dbo].[t_web_log_'+@tablename+']
([create_time] DESC);'
		execute(@sql)
		fetch next from Create_points_table into @tablename
		
		end
	close Create_points_table    --关闭
	deallocate Create_points_table  --释放

结果
在这里插入图片描述

删除所有表

仅用于测试时，使用。在线上使用时，请注意游标中的过滤添加(选择性删除哟，name != 'sysdiagrams' )

declare @tablename varchar(50)
declare @sql varchar(1000)
declare cu_adcolumn cursor  for select name from sys.tables where name != 'sysdiagrams'

open cu_adcolumn
fetch next from cu_adcolumn into @tablename
while(@@fetch_status=0)
begin

set @sql = 'drop table ['+@tablename+']  '

execute(@sql)

fetch next from cu_adcolumn into @tablename
end
close cu_adcolumn    --关闭
deallocate cu_adcolumn  --释放

添加索引

(为以上添加的旬表添加索引)
对表的右键(编写表脚本为，create到，新查询编辑器窗口)只会显示表结构。
所以需要使用数据库导出（生成脚本，高级选项中，选择索引和仅架构）导出为sql文件，然后使用上面的建表语句和下面的创建索引语句(需要分开执行)。

需要修改name LIKE 'Orderby2021%';和表名的变量ON ['+@tableName+']，索引的个数需要自定义

declare @tableName varchar(50)
declare @sql01 varchar(max)
declare @sql02 varchar(max)


declare mycur cursor for select name from dbo.sysobjects where type='U' AND name LIKE 'Orderby2021%';
OPEN mycur
	fetch next from mycur into @tableName
	while @@FETCH_STATUS=0
	begin	
		set @sql01='CREATE NONCLUSTERED INDEX [time_index] ON ['+@tableName+']
(
	[create_time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]';
		set @sql02='CREATE NONCLUSTERED INDEX [time_span_index] ON ['+@tableName+']
(
	[time_span] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
';
		-- print @sql01
		-- print @sql02
		exec (@sql01)
		exec (@sql02)
			
		fetch next from mycur into @tableName
	end
close mycur
deallocate mycur

添加用户表权限

使用时请按需修改
declare @tableName varchar(50)
declare @sql01 varchar(max)
declare @sql02 varchar(max)


declare mycur cursor for select name from dbo.sysobjects where type='U' AND name LIKE 'Orderby2021%';
OPEN mycur
	fetch next from mycur into @tableName
	while @@FETCH_STATUS=0
	begin	
		set @sql01='grant select,insert,delete,update on ['+ @tableName+'] to testuser01';
		set @sql02='grant select,insert,delete,update on ['+ @tableName+'] to sa2';
		-- print @sql01
		-- print @sql02
		exec (@sql01)
		exec (@sql02)
			
		fetch next from mycur into @tableName
	end
close mycur
deallocate mycur
go

base表

（读者需要根据作业逻辑判断自己的环境） base表添加数据(数据为分表的后缀)（作用为引导外部数据写入分表,所以很重要）,顺序为添加完对应旬表之后再添加

declare @tablename varchar(50)
declare @sql01 varchar(1000)
declare @sql02 varchar(1000)
declare @sql03 varchar(1000)
declare Create_points_table cursor
		for select convert(varchar(6),dateadd(mm,number,'2021-01-01'),112) --每月一账分表（通过varchar(6)控制输出的字符数202001）
				from master..spt_values with(nolock) 
				where type='P' and number>=0 
				and dateadd(mm,number,'2021-01-01')<'2022-01-01'

open Create_points_table
	fetch next from Create_points_table into @tablename
		while(@@fetch_status=0)
			begin

			set @sql01 ='INSERT INTO  [dbo].[base] (id) VALUES (''by'+@tablename+'_1'');'
			set @sql02 ='INSERT INTO  [dbo].[base] (id) VALUES (''by'+@tablename+'_2'');'
			set @sql03 ='INSERT INTO  [dbo].[base] (id) VALUES (''by'+@tablename+'_3'');'
			execute(@sql01)
			execute(@sql02)
			execute(@sql03)
	fetch next from Create_points_table into @tablename
		
			end
close Create_points_table    --关闭
deallocate Create_points_table  --释放

小结

时间函数+系统库表进行语句拼接。其中master…spt_values with(nolock)
where type='P’限制为最大的数为2047
select * from master…spt_values where type=‘P’ order by number desc
也就是说，添加旬表的最大时间不能大约2047年。(也就是27年后以上脚本就不能执行成功了)

之后为项目04(Mysql添加旬表02),使用游标还可以批量添加字段（写在其他篇幅）

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​

请给我的爱人一杯mojito
关注
专栏目录
2020校招复盘——秋招不易，致敬每一位追梦者（含网易、京东等19家互联网公司后台/Server端面经）
ZY-JIMMY
10-18 3万+
秋招经历、秋招心得、C++后台/Server端方向校招准备、面经整理、面经题库
SQL Server时间粒度系列----第3节旬、月时间粒度详解
01-08 373
SQL Server时间粒度系列----第3节旬、月时间粒度详解 本文目录列表： 1、SQL Server旬时间粒度 2、SQL Server月有关时间粒度 3、SQL Server函数重构 4、总结语 5、参考清单列表   SQL Server旬时间粒度       SQL Server不存在旬这个时间粒度，这个时间粒度在我们国家存在的。一个月分为上、中、下3旬，上旬和中旬均
获取一个时间段内，每一旬的天数 返回map key=年份+月份+旬 value=天数
dz525815的博客
10-17 482
由于我们业务要用的，所以就写了这个方法。判断太多了，不知道考虑全部全，自己能想到的都测了，没有问题。 public static Map<String, Integer> getXun(Integer year ,Date startTime,Date endTime){ SimpleDateFormat sdf = new SimpleDateFormat("yyy...
在SQL SERVER 中获取指定连续月份的旬的日期范围
qianjin036a的专栏
08-31 2366
有时，要用到按旬统计，还可能涉及不存在记录的旬(为0)，必须要有一个能获得连续数月的旬的日期范围的子查询作连接用，可以按如下方法处理：   declare @dt1 varchar(7), @dt2 varchar(7) set @dt1='2011-07' set @d
SQL时间函数应用（时间、季度、旬、月、星期）
king5439的专栏
11-24 6398
效果： declare @beginDate date;set @beginDate=getdate();select convert(varchar(10),@beginDate,112) as datekey,year(@beginDate) as NIAN,datepart(quarter,@beginDate) as JIDU,case when datepart(quarter,@beg...
项目04(Mysql添加旬表02)
一杯Mojito可以吗
12-08 90
背景 承接上文，本篇介绍创建mysql旬表的内容，使用存储过程 写在前面 使用之前，请测试多次。并且修改对应的内容，使其符合您的实际需要 每月3张 主要是对变量 i 表示的是月份,需要替换表结构和表名 DELIMITER $$ CREATE PROCEDURE create_table() BEGIN declare i int; set i=1; while i<=12 do set @sql_create_table_gpstrail01 = concat(concat( 'CRE
SQL server 期末复习
qq_740785701的博客
01-04 2034
数据库的基本概念 数据（Data）为描述客户事物及其状态的符号记录。 数据库是（Database,DB）相互关联且具有一定结构的数据集合。 数据库的设计和实现具有特定的目的，随机组合的数据不能被称为数据库。 数据库中的数据具有如下特征：共享性、持久性、有效性、安全性、一致性、无冗余性。 数据库管理系统（Database Managment System，DBMS）是一种用于创建和管理数据库的计算机应用软件程序。 数据库管理系统具有的优点：将相互关联的数据集成在一起，减少冗余度，程序与数据相互独立，保证数据的
迁移 40TB SQL 服务器数据库
weixin_43507410的博客
01-04 346
背景 我们的 HAProxy1 日志又叫流量日志，当前存储在两台 SQL Server 上，一台在纽约，一台在科罗拉多州。在 2019 年初，我们有约 4.5 年的数据，总计约 38TB。最初，数据库设计为每天只存一个表。也就是说在 2019 年初，单个数据库中大约有 1600 个表，其中包含多个数据库文件（因为数据文件的大小限制为 16TB）。每个表都有一个集群化的列存储索引，其中包含 100-400 百万行。 我必须将数据从现有的每日表中移到一个新结构中——每个月一个表。这需要同时在 NY（纽约）和 C
记PageHleper 分页功能报错：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
weixin_45759287的博客
06-09 1426
项目里面的复杂的查询语句需要进行分页，由于需要根据索引检索，且索引数据需要转格式，故查询语句里面包含 cast() 函数。 使用pageHelper的时候，自带的count语句拼接出来的结果就是在原来的语句上添加如下内容 select count(*) from ( your origin sql ) t 但是由于sqlserver 分页需要用到order by， select count(*) from ( your origin sql ) t 这条sql 里面便包含order by。 sqlser
asp在线网上书店（购购）
10-05
Access是一款轻量级的数据库管理系统，适合小型项目，它可以快速创建和管理数据表，同时支持SQL查询，但相比大型数据库系统（如SQL Server），在并发性能和扩展性上有所不足。在实际的商业环境中，一般会使用更强大...
sqlserver中返回旬开始日期和结束日期的函数
搞怪的索引
06-06 2197
这段时间的项目用到了sql server，于是一边
oracle旬统计,求汇总旬度数据的SQL语句
weixin_42126274的博客
04-03 452
[php]SQL>Create table test_rq as select sysdate+rownum as rq,rownum as d from all_objects where rownum < 500 ;Table created.SQL>select sum(d),to_char(rq,'YYYY-MM-')||(case when to_char(rq,'dd...
ApexSQL Diff工具比较Sql Server数据异同
一杯Mojito可以吗
03-10 1519
背景 在某些情况下，需要将特定表从生产数据库复制到DEV。一种用于测试或故障排除的目的。属于数据对比和同步的工作范围。 写在前面 参考文档为https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/ link 使用 此方法只是众多方法中的一个,比如使用tablediff，或是Visual studio 插件 ApexSQL Diff是有用的SQL工具，可用于从架构端查找数据库之间的差异， 并生
Sql Server 死锁(Deadlock )分析的方法
一杯Mojito可以吗
11-04 1032
环境 Sql Server 数据库发生死锁。(非紧急情况或紧急情况) 方法 1. RML Utilities工具+Trace文件捕获死锁 2. DBCC捕获死锁 3. Profiler捕获死锁 4. Trace文件捕获死锁(很耗时在线下执行吧) 5. 自动部署Profiler捕获死锁 6. Service Broker事件通知捕获死锁 7. Extended Events捕获死锁 个人推荐方法1 ，下面将重点介绍它。(其他方法提供互联网链接) RML Utilities工具+Trace文件捕
Sql Server事务+隔离级别+阻塞+死锁
一杯Mojito可以吗
12-21 813
背景 分析事务的执行过程 写在前面 摘录自https://www.cnblogs.com/jackson0714/p/TSQLFundamentals_08.html link 分析当前查询中的锁 sys.dm_tran_locks 视图 SELECT request_session_id AS 会话id , resource_type AS 请求锁定的资源类型 , resource_description AS 描述 , request_mode AS 模
项目01(实现从Sql Server数据库读取报警数据并发送到企业微信01)
一杯Mojito可以吗
11-09 746
本篇介绍从远程库中拉取数据表到本地库(sql server) 背景 为解决值班手机替换方案，且报警不方便查看问题(短信报警也有成本问题) 业务逻辑 远程库中有一张表MobileMessages，实时存放有zabbix和hostmonitor的报警信息(短信类)。 1.拉取远程表到监控主机的sql server中的DBcenter库中(库中建立需要的字段信息，参照远程表结构)。 说明:Status字段默认0表示为发送的信息，发送接收之后更改为1表示已发送。 同时更新SentTime字段为getdate()表示
项目01(实现从Sql Server数据库读取报警数据并发送到企业微信02)
一杯Mojito可以吗
11-09 554
本篇介绍把本地库表中的信息通过python发送到企业微信(sql server) 背景 承接上一篇博文《项目01(实现从Sql Server数据库读取报警数据并发送到企业微信01)》 link 业务逻辑 读取监控主机上DBCenter库中MobileMessages表中的监控内容Content字段发送到企业微信中的自建应用中 设计思路 利用python2.6.6发送(线上，脚本主机上的版本) 利用python3.9.0发送(新安装，miniconda，内容见下面链接) link 注意：方案一解决目前的
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值