SQL Server 清除一个数据库下所有表数据,保留表结构

用法:在需要清空数据的数据库创建并执行存储过程,该存储过程并不会影响其他数据库

❗请小心使用这些脚本,确保在生产环境之前备份您的数据库。⚠️

存储过程:

CREATE PROCEDURE ClearAllTables
AS
BEGIN
    DECLARE @TableName NVARCHAR(255)

    DECLARE tableCursor CURSOR FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN tableCursor
    FETCH NEXT FROM tableCursor INTO @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @SqlQuery NVARCHAR(MAX)

        -- Build the dynamic SQL query to truncate the current table
        SET @SqlQuery = 'TRUNCATE TABLE ' + QUOTENAME(@TableName)

        -- Execute the dynamic SQL query
        EXEC sp_executesql @SqlQuery

        FETCH NEXT FROM tableCursor INTO @TableName
    END

    CLOSE tableCursor
    DEALLOCATE tableCursor
END

执行

EXEC ClearAllTables

免责声明:仅技术技术分享,谨慎操作,数据无价

热门相关:鬼王绝宠:逆天废材妃   鬼王绝宠:逆天废材妃   天河大帝   布衣官道   陆爷的小祖宗又撩又飒