77 lines
1.6 KiB
Markdown
Executable File
77 lines
1.6 KiB
Markdown
Executable File
--声明一个变量
|
|
```sql
|
|
declare @tbname as nvarchar(100);
|
|
```
|
|
--声明一个游标用来遍历查询到的结果
|
|
```sql
|
|
declare C_userID CURSOR for
|
|
select distinct TABLE_NAME from information_schema.COLUMNS
|
|
where TABLE_NAME like '%2020_SRC'
|
|
```
|
|
--打开游标
|
|
```sql
|
|
open C_userID;
|
|
```
|
|
--获取游标指向的数据
|
|
```sql
|
|
fetch next from C_userID into @tbname;
|
|
while @@FETCH_STATUS = 0
|
|
```
|
|
BEGIN
|
|
--执行具体的操作
|
|
--删除未来的数据
|
|
--DELETE
|
|
--FROM [HiMonitorDB].[dbo].[11350069_STC]
|
|
--WHERE aDatetime >'2020-02-03'
|
|
|
|
--create NONCLUSTERED INDEX 索引名称 ON 表名(字段名)
|
|
EXEC('delete from [safeMonitorSrc].[dbo].['+@tbname+'] where CollectTime>GETDATE()')
|
|
--游标指向下一条数据
|
|
```sql
|
|
FETCH next from C_userID into @tbname;
|
|
```
|
|
END
|
|
--关闭游标
|
|
CLose C_userID
|
|
--释放游标
|
|
DEALLOCATE C_userID;
|
|
|
|
begin tran
|
|
COMMIT
|
|
--声明一个变量
|
|
```sql
|
|
declare @tbname as nvarchar(100);
|
|
```
|
|
--声明一个游标用来遍历查询到的结果
|
|
```sql
|
|
declare C_userID CURSOR for
|
|
select distinct TABLE_NAME from information_schema.COLUMNS
|
|
where TABLE_NAME like '%2020_APP'
|
|
```
|
|
--打开游标
|
|
```sql
|
|
open C_userID;
|
|
```
|
|
--获取游标指向的数据
|
|
```sql
|
|
fetch next from C_userID into @tbname;
|
|
while @@FETCH_STATUS = 0
|
|
```
|
|
BEGIN
|
|
--执行具体的操作
|
|
--删除未来的数据
|
|
--DELETE
|
|
--FROM [HiMonitorDB].[dbo].[11350069_STC]
|
|
--WHERE aDatetime >'2020-02-03'
|
|
|
|
--create NONCLUSTERED INDEX 索引名称 ON 表名(字段名)
|
|
EXEC('delete from [safeMonitorAPP].[dbo].['+@tbname+'] where CollectTime>GETDATE()')
|
|
--游标指向下一条数据
|
|
```sql
|
|
FETCH next from C_userID into @tbname;
|
|
```
|
|
END
|
|
--关闭游标
|
|
CLose C_userID
|
|
--释放游标
|
|
DEALLOCATE C_userID; |