SQL Server删除库中所有临时表或用户表

USE tempdb;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/***************************************
* ProcName: sp_clean_temp_table    
* Author: wenbo    
* Create date: 2022-04-24
* Description: 删除服务器tempdb的所有用户临时表
***************************************/
CREATE PROCEDURE [dbo].[sp_clean_temp_table]
AS
BEGIN
    DECLARE @sql VARCHAR(8000);
    WHILE(SELECT COUNT(*) FROM sysobjects WHERE type = 'U' AND name LIKE 'tmp_%') > 0
    BEGIN
        SELECT @sql = 'drop table ' + name
        FROM sysobjects
        WHERE (type = 'U')
              AND name LIKE 'tmp_%'
        ORDER BY 'drop table ' + name;
        EXEC (@sql);
    END;
END;
GO