如何清除通过 SQL 中的 INFORMATION_SCHEMA.TABLES 查询选择的所有表? [英] How to clear all tables selected through a INFORMATION_SCHEMA.TABLES query in SQL?

问题描述

结果是包含应清除的无用行的数百个表的列表.我怎么能不手动输入每一个?

The result is a list of a few hundred tables that have useless rows that should be cleared. How can I do so without manually typing each one?

不尝试删除所有表，只删除通过 WHERE AND

Not trying to delete all tables, just the ones that been selected through WHERE AND

推荐答案

DECLARE @SQL_String NVARCHAR(MAX)
DECLARE @TableName  NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
    -- You might want to modify this in order to get only 
    -- a specific subset of tables
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
OPEN db_cursor   

FETCH NEXT FROM db_cursor 
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN 

    SET @SQL_String = N'DELETE * FROM ' + QUOTENAME(@TableName)
    EXEC sp_executesql @SQL_STRING

    FETCH NEXT FROM db_cursor 
    INTO @TableName        
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

这样的事情应该可以解决问题.

Something like this should do the trick.

与往常一样，删除没有 WHERE 子句的表中的记录时，要非常小心.

As always, when deleting records in tables without a WHERE clause, be very careful.

这篇关于如何清除通过 SQL 中的 INFORMATION_SCHEMA.TABLES 查询选择的所有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！