Easy to delete all the data tables in the database, empty the database, some constraints, not directly delete, you need to delete the constraints in the library, the code is as follows
Copy Code code 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)
and then empty all the tables in the database：
If you need to delete a stored procedure, and so on, just do the following modification on the line where xtype= ' U ' is changed to where xtype= ' P ', drop table is changed to drop Procedure
the meaning of sysobjects's xtype representative:
One row in the table for each object (constraint, default, log, rule, stored procedure, and so on) created within the database. Only within Tempdb is one row for each temporary object in the table.
Column name Data type description
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 = defaults or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inline Table function
P = Stored Procedure
PK = PRIMARY KEY constraint (type is K)
RF = copy Filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User Table
UQ = UNIQUE constraint (type is K)
V = view
X = Extended Stored Procedure
UID smallint the user ID of the owner object.
Info smallint reserved. Internal use only.
status int reserved. Internal use only.
Base_schema_
ver int reserved. Internal use only.
replinfo int reserved. For replication use.
The object identification number of the Parent_obj int parent object (for example, for triggers or constraints, the identification number is the table ID).
The date the Crdate DateTime object was created.
Ftcatid smallint the Full-text catalog identifier for all user tables registered for Full-text indexing, 0 for all user tables that are not registered.
Schema_ver int version number, which is incremented for each table's schema changes.
Stats_schema_
ver int reserved. Internal use only.
Type char (2) object types. Can be one of the following values:
C = CHECK Constraint
D = defaults or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inline 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 reserved.
Sysstat smallint internal state information.
Indexdel smallint reserved.
Refdate datetime is reserved for later use.
version int is reserved for later use.
Deltrig int reserved.
Instrig int reserved.
Updtrig int reserved.
Seltrig int reserved.
Category int is used for publishing, constraining, and labeling.
Cache smallint reserved.
