One-time deletion of all tables in the database and all Stored Procedure SQL statements

From http://hi.baidu.com/yocnnzrmilceijr/item/d1d928f8e8cc73ea1b111f07

Delete all tables:

If table deletion fails due to foreign key constraints, delete all constraints first:

--/Step 2 *********** Delete the foreign key constraints of all tables ******************* ******/

Declare C1 cursor
Select 'alter table ['+ object_name (parent_obj) +'] Drop constraint ['+ name +'];'
From sysobjects
Where xtype = 'F'
Open C1
Declare @ C1 varchar (8000)
Fetch next from C1 into @ C1
While (@ fetch_status = 0)
Begin
Exec (@ C1)
Fetch next from C1 into @ C1
End
Close C1
Deallocate C1

--/Step 2 *********************** **/

Use 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)

Deleting all stored procedures is also available, but you do not need to take the first step.CodeChange where xtype = 'U' to where xtype = 'P', and change drop table to drop procedure.

The xtype of sysobjects indicates the meaning:

Each object created in the database (constraints, default values, logs, rules, stored procedures, etc.) occupies one row in the table. Each temporary object occupies one row in the table only in tempdb.

description of the Data Type of the column name
name sysname object name.
id int: ID of the object.
xtype char (2) object type. It can be one of the following object types:
C = check constraint
d = default value or default constraint
F = foreign key constraint
L = Log
fn = scalar function
if = embedded table function
P = Stored Procedure
PK = primary key constraint (type: K)
Rf = copy and filter the 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

User ID of the UID smallint owner object.
info smallint is retained. For internal use only.
Status int is retained. For internal use only.
base_schema _
ver int is retained. For internal use only.
replinfo int is retained. For replication.
parent_obj int indicates the Object ID of the parent object (for example, this ID is the table ID for triggers or constraints ).
Create date of the crdate datetime object.
ftcatid smallint is the full-text directory identifier of all user tables registered with the full-text index. It is 0 for all user tables not registered.
schema_ver int version number, which is added each time the schema of the table is changed.
stats_schema _
ver int is retained. For internal use only.
type char (2) object type. It can be one of the following values:
C = check constraint
d = default value or default constraint
F = foreign key constraint
fn = scalar function
If = nested table function
K = primary key or unique constraints
L = Log
P = Stored Procedure
r = Rules
Rf = copy and filter the Stored Procedure
S = system table
TF = table function
TR = trigger
U = User table
V = view
X = Extended Stored Procedure

userstat smallint is retained.
sysstat smallint internal status information.
indexdel smallint is retained.
refdate datetime is reserved for later use.
Version int is reserved for future use.
deltrig int is retained.
instrig int is retained.
updtrig int is retained.
seltrig int is retained.
category int is used for publishing, binding, and identification.
cache smallint is retained.