1. Build table

1 IF object_id (N' Table name ', N'U') IS NULL CREATE TABLE Table name (
2 id INT IDENTITY (1, 1) PRIMARY KEY ,......);

2. Query all tables that meet the criteria

 
1 SELECT
2 NAME
3 FROM
4 sys.objects
5 WHERE
6 type = 'u'
7 AND NAME LIKE 'test_%';
 

3. Batch delete the tables that meet the conditions

 
 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. Batch cursor empties the tables that meet the conditions

 
 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
 

【SqlServer】empty table and delete table and create table More articles about

  1. 【SQLServer】“ Unable to update database ’XXX’ Execution deletion , Because it's being used to replicate ” Solutions for

    Warning : Of one or more existing columns ANSI_PADDING Set to "off", Will be with ANSI_PADDING by "on" To recreate [SQLServer]& ...

  2. 【SqlServer】T-SQL Introduction and basic usage of

    One .T-SQL summary SQL Server The programming language used to operate the database is Transaction-SQL, abbreviation T-SQL.T-SQL And PL/SQL Different , There is no fixed program structure .T-SQL Including the following 4 Parts of : DD ...

  3. 【SQLServer】 Use T-SQL Access remote database :openrowset and openquery And the creation of the connection server

    █ Enable / close Ad Hoc Distributed QueriesAd Hoc Distributed Queries The service is turned off by default , To use openrowset and openquery Access remote database ...

  4. 【51】 To write new and delete We need to stick to the routine

    1.[50] Talked about it. , There are many reasons to write a custom new/delete, Customize new/delete When , Some rules need to be observed . 2. Circular application , Until it succeeds or throws an exception , as follows : void* operator new ...

  5. 【ThinkingInC++】65、 Use delete void* There may be errors

    /** * Book :[ThinkingInC++] * function : Use delete void* There may be errors * Time :2014 year 10 month 5 Japan 14:31:43 * author :cutter_point */ #incl ...

  6. 【SqlServer】JSON function

    1   summary This paper will combine MSND Brief analysis Sqlserver in JSON function , It mainly includes ISJSON,JSON_VALUE,JSON_MODIFY,JSON_QUERY. 2   The specific content 2.1  J ...

  7. 【SqlServer】SqlServer Computed columns in

    Computed columns are different from columns that require us to assign values manually or programmatically , Its values are derived from the calculated values of other columns in the table . such as , A table contains quantity Columns Number And the unit price column Price, We can create a calculation column amount Amount To express quantity * The result value of the unit price , ...

  8. 【SqlServer】SqlServer Exception handling

    stay SQLserver In the database , If there are many stored procedures , We'll use dynamic SQL Make a stored procedure call a stored procedure , Now , It's very likely that something went wrong at some point , But when something goes wrong, it's hard to trace the stored procedure that went wrong , At this point we can use exceptions ...

  9. 【SqlServer】SqlServer General operation of

    Create a new table , Not responsible for any data ( The table will not have the primary key of the original table . Index, etc ) select * into NewTable from OldTable where 1<>1; Create a new table , And copy the old ...

Random recommendation

  1. GUI - GEB UI library

    Recently based on Winform Developed several products , Feeling Winform There are great limitations , The main point is : Controls are based on Windows Form , This results in each control being a heavyweight control , For complex interfaces , Its performance and performance are not good . In practice ...

  2. [ turn ]Git - Rewrite history

    from http://git-scm.com/book/zh/Git-%E5%B7%A5%E5%85%B7-%E9%87%8D%E5%86%99%E5%8E%86%E5%8F%B2     Rewrite history Many times ...

  3. Will be varchar value &#39;1,2,3,4,5,6,7,8&#39; Convert to data type int When the failure .

    alter PROCEDURE PrTradingDelete ) AS BEGIN WHERE id in(@id) END GO Executing the above stored procedure will cause an exception . Tips : Will be varchar value '1 ...

  4. Oracle Basics Import database Delete user 、 Delete tablespace 、 Delete all tables in the table space

    Import database stay cmd Next use imp Import   Format : imp userName/passWord file=bmp File path ignore = y ( Ignore creation errors )full=y( Import everything in the file ); example : ...

  5. About PHPAPI ZEND_API TSRM_API Definition of macro

    stay PHP Source code , We can see things like PHPAPI ZEND_API TSRM_API etc. xxx_API( Of course, there are other formats ) Such a macro The definitions of them are similar to #if defined(__GNUC__) & ...

  6. [Scikit-learn] 4.4 Dimensionality reduction - PCA

    2.5. Decomposing signals in components (matrix factorization problems) 2.5.1. Principal component an ...

  7. SpringMVC Beginner to master ( One )

    Recommend a very good learning blog http://jinnianshilongnian.iteye.com/blog/1752171

  8. java Memory model ( turn )

    Prerequisite knowledge : Java Memory model (JMM) It's a conceptual model , The bottom layer is the computer register . Cache memory . Main memory and CPU etc. .  In a multiprocessor environment , The relationship between interactive hardware devices that share data : JMM: From the above two pictures , Let's talk about the following ...

  9. java Common interview questions and answers 11-20(JVM)

    11.JVM What are the areas of memory , What is the role of each zone ? Java The virtual machine is mainly divided into the following areas : Method area :1. Sometimes it becomes a permanent generation , There is little recycling in this area , But it doesn't mean it doesn't happen GC, What's going on here GC It's mainly about the method area ...

  10. pom.xml take jar Package import

    2.5 yes Maven Version of