如何在本地数据库中删除表，只在运行时创建表 [英] How to Drop tables in a local database provided tables are created at runtime only

问题描述

我有一个本地数据库，其中只在运行时生成表。在退出应用程序时，我需要对数据库中的每个表进行多维处理。我怎么能这样做？



P.S-我不需要丢弃整个数据库。我需要删除其中的每个表（另外，由于表是在运行时创建的，我在执行drop命令时没有名称来引用它们）

I have a local database in which tables get generated at runtime only. While exiting the application I need to dop every tables in the database. How can I do this?

P.S-I dont need to drop entire database. I need to drop every tables inside it(Also, as tables are created at runtime, i dont have a name to refer them while doing drop command)

推荐答案

使用此脚本清理所有数据库

Use This script Clean all Database

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

对于Mdf文件删除



我不确定'清除架构'是什么意思 - 肯定是新的数据库已经有一个'清除'模式..但是，您可以通过以下T-SQL在代码中创建一个新数据库：

For Mdf File Delete

I'm not sure what the point is of 'clearing the schema' - surely a new database already has a 'clear' schema.. BUT, you can create a new database in code via the following T-SQL:

CREATE DATABASE NewDb (NAME=NewDbFile, FILENAME= '<filepath>')

如果您需要一个文件（MDF），那么您也可以使用sp_detach_db和您分离数据库然后可以根据需要从上面指定的位置移动它：

If you need a file (an MDF) you can then detach the database too with sp_detach_db and you can then move it as required from the location specified above:

EXEC sp_detach_db NewDb

清除da你可以使用sp_msforeachtable和截断命令 - 它是一个非记录操作，不检查约束，也不检查外键 - 但是，它不能回滚！

To clear the data you can use sp_msforeachtable with a truncation command - it is a non-logged operation, and does not check constraints, nor foreign keys - however, it cannot be rolled back!

EXEC sp_msforeachtable 'TRUNCATE TABLE ?'

这篇关于如何在本地数据库中删除表，只在运行时创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！