There may be many tables in a database, and I would like to delete them all if one manual removal is too cumbersome and a waste of time.
Two questions involved: 1. How do I know which tables are in this database? 2. How do I read these table names?
These two problems have been solved, which is much more convenient.
First question:
Select name from sysobjects
where xtype= ' U '
sysobjects table
Each object created within the database (constraints, default values, logs, rules, stored procedures, and so on) occupies a single row in the table.
Xtype= ' U ' represents the user table
Second question:
You can read the table name and put it in a table variable, and then use the While loop to drop table
1: declareTABLEINTIdentity(), NAME NVARCHAR (50));
2: declareint;
3: set @i=1;
4: declareint;
5:
6: into @tabletemp
7: SELECT NAME
8: fromwhere xtype=' U ';
9:
: set @mx = (selectMAX from @tabletemp);
One :
: while (@i<[email protected])
: begin
: declare @name nvarchar (50)
: Selectfromwhere [email protected]
: --truncatetable @name This is wrong, so you need @name is a table type variable
+ : exec(' TRUNCATE table '[email protected])
: exec(' drop table '[email protected])
: set @[email protected]+1
: End
£ º
Perhaps more convenient to handle with cursors
Using cursors
1: declarecursor, @name nvarchar (50);
2: set @cur =cursorforselectfromwhere xtype=' U '
3:
4: Open @cur
5: fetchnext from into @name
6:
7: while @ @FETCH_STATUS =0
8: begin
9: exec (' TRUNCATE table '[email protected])
: exec (' drop table '[email protected])
One : fetchnext from into @name
: End
:
: Close @cur
: deallocate @cur
At the beginning of the online search is the use of the cursor method, try to achieve.
Then try to use other methods to achieve (did not expect to succeed), in fact, is the cottage downstream of the idea of the standard, feel no cursor simple. The only surprise might be the use of a table variable, previously just seen in Oracle.
Success is important, but also do not deliberately care, enjoy the unexpected on the journey to get!