sql - 如果创建日期超过一周，则删除 SQL 表 - MS SQL Server

Question

我想在 Ms SQL Server 中创建一个存储过程和作业，以删除架构中超过一周的所有表。

我每天都创建备份，我想通过安排一个作业来删除任何超过一周的备份(SQL 表)来自动执行该过程。

非常感谢您的帮助。

Answer 1

您可以在 SQL Server 中使用 sys.objects 关键字来完成此操作。

查询应该是这样的:

USE [Your_Database_Name];
GO
SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE create_date > GETDATE() - [No_Of_Days_Old]
ORDER BY create_date;
GO

上面的示例与 MSDN 页面上显示的第一个示例略有不同，该页面详细介绍了 sys.objects 关键字(A。返回上次修改的所有对象N 天).

可以在此处找到该页面:

sys.objects (Transact-SQL)

对于查询数据库对象“元数据”的许多不同方法来说，这是一个很好的资源。

当然，上面的代码只是简单地“选择”需要删除的表名，而不会实际从数据库中删除(或删除)该表。

为此，您需要一种机制来发出 DROP TABLE 命令。不幸的是，DROP TABLE 命令不接受参数值表名(即您不能执行 DROP TABLE @tablename)，但您可以构建一个字符串/varchar一个完整的 T-SQL 语句并 EXECUTE 它)。

为此，您可以使用 CURSOR循环遍历先前 SELECT 语句的结果，在字符串/varchar 中构建一个新的 T-SQL 命令，该命令将删除表名。下面是一个例子:

DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR FOR 
SELECT name AS tname
FROM sys.objects
WHERE create_date > GETDATE() - 7

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tname  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @sql = 'DROP TABLE ' + @tname
       --EXEC (@sql)
       PRINT @sql

       FETCH NEXT FROM db_cursor INTO @tname  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

请注意，在上面的示例中，我注释掉了 EXEC (@sql) 行。这是在@sql 变量中实际执行 T-SQL 语句的行，由于它是一个破坏性命令，我只是将其注释掉并使用 PRINT @sql 命令代替(下面的行) .按原样运行它，看看您可能会删除哪些表，当您满意时，取消注释 EXEC (@sql) 命令并注释掉 PRINT @sql命令!