1.建表
1 IF object_id (N'表名', N'U') IS NULL CREATE TABLE 表名 (
2 id INT IDENTITY (1, 1) PRIMARY KEY ,......);
2.查询所有满足条件的表
1 SELECT
2 NAME
3 FROM
4 sys.objects
5 WHERE
6 type = 'u'
7 AND NAME LIKE 'test_%';
3.批量删除满足条件的表
1 DECLARE
2 @NAME VARCHAR (50)
3 WHILE (
4 EXISTS (
5 SELECT
6 *
7 FROM
8 sysobjects
9 WHERE
10 NAME LIKE 'test_%'
11 )
12 )
13 BEGIN
14 SELECT
15 @NAME = NAME
16 FROM
17 sysobjects
18 WHERE
19 NAME LIKE 'test_%' exec ('drop table' + @NAME)
20 END
4.批量游标清空满足条件的表
1 DECLARE
2 @trun_name VARCHAR (50) DECLARE
3 name_cursor CURSOR FOR SELECT
4 'truncate table ' + NAME
5 FROM
6 sysobjects
7 WHERE
8 xtype = 'U'
9 AND NAME LIKE 'test_%' OPEN name_cursor FETCH next
10 FROM
11 name_cursor INTO @trun_name
12 WHILE @@FETCH_STATUS = 0
13 BEGIN
14 exec (@trun_name) print 'truncated table ' + @trun_name FETCH next
15 FROM
16 name_cursor INTO @trun_name
17 END CLOSE name_cursor DEALLOCATE name_cursor