重置Sql Server数据库 [英] Reset Sql Server Database

问题描述

Hello Friends

我使用以下代码重置数据库。

但我不希望清除所有表格的信息。

例如，我想保留我的客户表，但我要清理其他信息。

请帮忙。



注意：

我正在使用SQL SERVER 2008

我想保留一张桌子，然后清除所有其他桌子。

请帮助我。



我的代码：

 string w =  使用[ + Application.StartupPath + @  < span class =code-string> \ Datafolder \mydb.MDF]设置NoCount ON声明@tableName varchar（200）set @ tableName =''存在时（从INFORMATION_SCHEMA.TABLES选择T.TABLE_NAME T左外连接INFORMATION_SCHEMA T.TABLE_NAME上的.TABLE_CONSTRAINTS TC = TC.TABLE_NAME其中（TC.CONSTRAINT_SCHEMA ='外键'或TC.CONSTRAINT_TY PE为NULL）且T.TABLE_NAME不在（'dtproperties'，'sysconstraints'，'syssegments'）和TABLE_TYPE ='BASE TABLE'和T.TABLE_NAME> @TableName）从INFORMATION_SCHEMA.TABLES开始选择@ tableName = min（T.TABLE_NAME）T T.TABLE_NAME = TC.TABLE_NAME上的左外连接INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC其中（TC.CONSTRAINT_TYPE ='外键'或TC.CONSTRAINT_TYPE为NULL ）和T.TABLE_NAME不在（'dtproperties'，'sysconstraints'，'syssegments'）和TABLE_TYPE ='BASE TABLE'和T.TABLE_NAME> @TableName Exec（'Truncate table'+ @ tableName）结束集@ TableName =''存在时（从INFORMATION_SCHEMA.TABLES T选择T.TABLE_NAME）在T.TABLE_NAME = TC.TABLE_NAME TC左外连接INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC TC.CONSTRAINT_TYPE ='主键'和T.TABLE_NAME<>'dtproperties'和TABLE_TYPE ='BASE TABLE'和T.TABLE_NAME> @TableName）从INFORMATION_SCHEMA.TABLES开始选择@ tableName = min（T.TABLE_NAME）T left left加入T.TABLE_NAME = TC.TABLE_NAME上的INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC，其中TC.CONSTRAINT_TYPE ='主键'和T.TABLE_NAME<>'dtproperties'和TABLE_TYPE ='BASE TABLE'和T.TABLE_NAME> @TableName Exec（'from from'+ @ tableName）如果存在（SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY（OBJECT_ID（QUOTENAME（TABLE_SCHEMA）+'。'+ QUOTENAME（@tableName）），COLUMN_NAME，'IsIdentity'）= 1 ）DBCC CHECKIDENT（@ tableName，RESEED，0）End Set NoCount Off; 
 sqlcom.CommandExecut（w， 1 ）; 

解决方案

如果这是MySQL你可以手动清除phpMyAdmin中的表格吗？



或者如果您只想通过代码执行此操作，请尝试修改此命令：



aCommand.CommandText = String.Format（DELETE FROM {0} WHERE {1 } ='{2}'，dbTable，Column，）



您可以循环浏览所有列，同时清除（最后一个参数）所指示的所有列/ BLOCKQUOTE>

Hello Friends
I've used the following code to reset the database.
But I do not want the information to be cleared of all tables.
For example, I want to keep my customer table but other information I clear the table.
Please help.

note:
I am using SQL SERVER 2008
I want to keep one table and I clear the all another tables.
Please help me.

My Code:

string w ="use [" + Application.StartupPath + @"\Datafolder\mydb.MDF] Set NoCount ON Declare @tableName varchar(200) set @tableName='' While exists( select T.TABLE_NAME from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.TABLE_NAME=TC.TABLE_NAME where (TC.CONSTRAINT_SCHEMA ='Foreign Key' or TC.CONSTRAINT_TYPE is NULL) and T.TABLE_NAME not in ('dtproperties','sysconstraints','syssegments')    and TABLE_TYPE ='BASE TABLE' and T.TABLE_NAME > @TableName) Begin Select @tableName=min(T.TABLE_NAME) from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.TABLE_NAME=TC.TABLE_NAME where (TC.CONSTRAINT_TYPE ='Foreign Key' or TC.CONSTRAINT_TYPE is NULL) and T.TABLE_NAME not in ('dtproperties','sysconstraints','syssegments')    and TABLE_TYPE ='BASE TABLE' and T.TABLE_NAME > @TableName  Exec('Truncate table '+@tableName) End set @TableName='' While exists(select T.TABLE_NAME from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.TABLE_NAME = TC.TABLE_NAME where TC.CONSTRAINT_TYPE ='Primary Key' and T.TABLE_NAME <>'dtproperties'and TABLE_TYPE ='BASE TABLE' and T.TABLE_NAME > @TableName) Begin Select @tableName=min(T.TABLE_NAME) from INFORMATION_SCHEMA.TABLES T  left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.TABLE_NAME =TC.TABLE_NAME where TC.CONSTRAINT_TYPE ='Primary Key' and T.TABLE_NAME <>'dtproperties'and TABLE_TYPE='BASE TABLE'  and T.TABLE_NAME > @TableName  Exec('Delete from '+@tableName)  If exists( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID( QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(@tableName)), COLUMN_NAME,'IsIdentity')=1) DBCC CHECKIDENT (@tableName, RESEED, 0) End Set NoCount Off";
sqlcom.CommandExecut(w,1);

解决方案

If this is MySQL you can manually clear the tables in phpMyAdmin?

Or if you only want to do it via code then try modifying this command:

aCommand.CommandText = String.Format("DELETE FROM {0} WHERE {1} = '{2}'", dbTable, Column, "")

You can loop through all columns while clearing all indicated by "" (last param)

这篇关于重置Sql Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！