Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to delete all the tables in a database in SQLserver. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

It is convenient to delete all the data tables in the database and empty the database. Some have constraints and cannot delete directly. You need to delete the constraints in the library first. The code copy code is as follows:-- Delete all constraints DECLARE C1 cursor for select'alter table ['+ object_name (parent_obj) +'] drop constraint ['+ name+'] 'from sysobjects where xtype=' F' open C1 declare @ C1 varchar (8000) fetch nextfrom C1 into@c1 while (@ @ fetch_status=0) begin exec (@ C1) fetch nextfrom C1 into@c1 end close C1 deallocate C1-delete all tables in the database declare @ tname varchar (8000) set@tname='' select@tname=@tname+Name+','from sysobjects where xtype='U' select@tname='drop table' + left (@ tname,len (@ tname)-1) exec (@ tname)

Then empty all the tables in the database: if you need to delete stored procedures, etc., just change the above where xtype='U' to where xtype='P',drop table to the xtype of drop Procedure sysobjects: each object created in the database (constraints, default values, logs, rules, stored procedures, etc.) occupies a row in the table. It is only within tempdb that each temporary object has a row in the table. The column name data type describes the name sysname object name. Id int object identification number. Xtype char (2) object type. Can be one of the following object types: C = CHECK constraint D = default or DEFAULT constraint F = FOREIGN KEY constraint L = log FN = scalar function IF = embedded table function P = stored procedure contention = PRIMARY KEY constraint (type is K) RF = replication filter stored procedure S = system table TF = table function TR = trigger U = user table UQ = UNIQUE constraint (type is K) V = view X = extended storage The user ID of the procedure uid smallint owner object. Info smallint is reserved. For internal use only. Status int is reserved. For internal use only. Base_schema_ ver int is reserved. For internal use only. Replinfo int is reserved. For replication. The object identification number of the parent_obj int parent object (for example, for triggers or constraints, the identification number is table ID). The date when the crdate datetime object was created. The full-text catalog identifier of ftcatid smallint for all user tables registered for the full-text index, and 0 for all user tables that are not registered. The schema_ver int version number, which increases each time the schema of the table changes. Stats_schema_ ver int is reserved. For internal use only. Type char (2) object type. It can be one of the following values: C = CHECK constraint D = default or DEFAULT constraint F = FOREIGN KEY constraint FN = scalar function IF = embedded table function K = PRIMARY KEY or UNIQUE constraint L = log P = stored procedure R = rule RF = copy filter stored procedure S = system table TF = table function TR = trigger U = user table V = view X = extended stored procedure userstat smallint retention. Sysstat smallint internal status information. Indexdel smallint is reserved. Refdate datetime is reserved for later use. Version int is reserved for later use. Deltrig int is reserved. Instrig int is reserved. Updtrig int is reserved. Seltrig int is reserved. Category int is used for publishing, constraining, and identifying. Cache smallint is reserved.

After reading the above, have you mastered the method of how to delete all tables in a database in SQLserver? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.