重置Sql Server数据库

本文介绍了重置Sql Server数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ Hello Friends 我使用以下代码重置数据库。 但我不希望清除所有表格的信息。 例如，我想保留我的客户表，但我要清理其他信息。 请帮忙。 注意： 我正在使用SQL SERVER 2008 我想保留一张桌子，然后清除所有其他桌子。 请帮助我。 我的代码： string w = 使用[ + Application.StartupPath + @ \ 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 FriendsI'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 2008I 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数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！