SQLServer脚本编写

现收通报,需要现编写一个SQL Server的脚本,供出差的同事使用一下。

本人这沉思这SQL
Server脚本听都没听说过,但是团队说决定就是公了,那我就只好硬在头皮上了。

 

剧本实现的作用于简单,在数据库中摸索固定前缀的发明,表中都蕴含[Time]性能,删除三独月(或者…天)的数据。

简短搜索了有关文化,分以下几步实现:

1.拿走时间节点并更换为指定格式:

  获取当前日期前指定时间,网上容易百度到,获取到时刻节点需要以那易为定位的格式,具体可参看《Sql中管datetime转换成为字符串(CONVERT)》;

  代码如下所示:

1 select @logdeldate = (select convert(varchar(100), dateadd(month, -3, getdate()), 21));

 

2.获取数据库符合要求的指定表:

  此处需要留意得到之表名需要仓储进入临时表中,供后补给遍历使用。因为表中数据尚未主键,因此需要开展扦插入Key以便查询(如果来更好的点子,请报告):

1 select NULL tmpKey, [name] into #TempTable from [sysobjects] where [type] = 'u' and name like 'T_Status_A%' order by [name];

  此处临时表中的数量记录tmpKey值均为NULL,使用如下语句可以吧创新中同样长条记下之tmpKey值:

1 set rowCount 1
2 update #TempTable set tmpKey = 1
3 set rowCount 0

  select rowCount
0的图吗限接下去执行的SQL语句仅执行同一修,比如下一样句语句,仅用一如既往久记下之tmpKey值更新为1。

 1 update #TempTable set tmpKey = 1 

 

3.脚以中宣称变量,并进行赋值操作

1 declare  
2     @i as int,
3     @deldate as varchar(100),     --设备状态表删除时间节点
4     @logdeldate as varchar(100),  --日志表删除时间节点

  上述代码中扬言了多变量,并标明其针对性承诺类型。

1 select @logdeldate = (select convert(varchar(100), dateadd(month, -3, getdate()), 21));
2 set @logsql = 'delete from [****].[dbo].[T_Log] where Time < ' + CHAR(39) + @logdeldate + CHAR(39);

  上述代码中对@logdeldate进行赋值,赋值为找后底结果;后续设置sql语句被@logsql变量,注意@sql声明时也@logsql
as nvarchar(MAX),,非varchar(MAX)。

  注:CHAR(39)为单引号。

 

4.遍历结果集,并实施语句

 1 set @i = 0;                        --初始化计数
 2 while @i < @deltablecount
 3     Begin
 4         set rowCount 0
 5         select @tablename = (select [name] from #TempTable where tmpKey = 1)
 6         delete from #TempTable where tmpKey = 1
 7         
 8         set rowCount 1
 9         update #TempTable set tmpKey = 1
10         set rowCount 0
11         
12         set @statussql = 'delete from ' + QUOTENAME(@tablename) + ' where [Time] < ' + CHAR(39) + @deldate + CHAR(39);
13         print @statussql
14         exec(@statussql)
15         Set @i = @i + 1
16     End
17 GO

  上述代码为遍历结果集,拼接新的SQL语句并推行。

 

  最后附上此次的脚本,因为正编写,有众多请勿满意的处在,但是呢不明了怎么改善,如果各位看后发生改进措施要报告。

  最后附上完整代码:

--删除数据库指定日期数据前,请确认操作不可逆转
USE [****] 
GO

---------------------------------------------------------------------------------------------------------------------------------------

--选取符合要求的表,存储进临时表中供后续遍历
select NULL tmpKey, [name] into #TempTable from [sysobjects] where [type] = 'u' and name like 'T_Status_A%' order by [name];
GO

set rowCount 1
update #TempTable set tmpKey = 1
set rowCount 0

---------------------------------------------------------------------------------------------------------------------------------------

declare  
    @i as int,
    @deldate as varchar(100),     --状态表删除时间节点
    @logdeldate as varchar(100),  --日志表删除时间节点
    @tablename as varchar(100),
    @deltablecount as int,        --需要删除的数据库总数
    @logsql as nvarchar(MAX),
    @realsql as nvarchar(MAX),
    @statussql as nvarchar(MAX);

select @deltablecount = COUNT(*) from #TempTable;

---------------------------------------------------------------------------------------------------------------------------------------

--设置日志表的删除时间节点(默认删除三个月以前的数据)
select @logdeldate = (select convert(varchar(100), dateadd(month, -3, getdate()), 21));
set @logsql = 'delete from [****].[dbo].[T_Log] where Time < ' + CHAR(39) + @logdeldate + CHAR(39);
print(@logsql)
exec(@logsql) 

---------------------------------------------------------------------------------------------------------------------------------------

--设置设备状态表删除时间(默认删除三个月以前的数据)
select @deldate = (select convert(varchar(100), dateadd(month, -3, getdate()), 21));

set @i = 0;                        --初始化计数
while @i < @deltablecount
    Begin
        set rowCount 0
        select @tablename = (select [name] from #TempTable where tmpKey = 1)
        delete from #TempTable where tmpKey = 1

        set rowCount 1
        update #TempTable set tmpKey = 1
        set rowCount 0

        set @statussql = 'delete from ' + QUOTENAME(@tablename) + ' where [Time] < ' + CHAR(39) + @deldate + CHAR(39);
        print @statussql
        exec(@statussql)
        Set @i = @i + 1
    End
GO

drop table #TempTable
GO

 

 

  

 

相关文章