澳门金沙vipSQL Server 7.0 入门

当我们需要批量删除数据库中的表时,对于单个删除一些表是否感到烦躁,厌倦,干脆写个脚本用得了。

有朋友问起:MSSQL中如何用SQL清除所有表的数据?这个需求分三种类型:
第一:只要数据库中表是空的;
第二:表是空的,并且自增长列可以从1开始增长。
第三:表是空的,并且自增长列可以从1开始增长,而且存在表间的约束。
邀月稍微整理了下,放在这里,便于有需要的朋友参阅。
其实,这不算什么需求。只要用数据库的生成脚本,几分钟即可生成一个干净的表结构及存储过程、视图、约束等。这里提供了另一种用SQL解决问题的方案。权当是无聊的学习,加深点印象吧。呵呵。
首先,作一些假设:假设database名为TestDB_2000_2005_2008
预先准备一些脚本

声明游标 
在这一步中,需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。 
形式1 (ANSI 92) 
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR 
FOR select_statement 
[FOR {READ ONLY | UPDATE ][OF column_list]}] 
形式2 
DECLARE cursor_name CURSOR 
[LOCAL | GLOBAL] 
[FORWARD_ONLY | SCROLL] 
[STATIC | KEYSET | DYNAMIC] 
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] 
FOR select_statement 
[FOR {READ ONLY | UPDATE ][OF column_list]}] 
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝,以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中,原有基表中数据发生了改变,那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。 
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项(first、last、next、relative、absolute)都可以在游标中使用。如果忽略该选项,则游标只能向前滚动(next)。 
Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。 
READ ONLY指明在游标结果集中不允许进行数据修改。 
UPDATE关键字指明游标的结果集可以修改。 
OF column_list指明结果集中可以进行修改的列。缺省情况下(使用UPDATE关键字),所有的列都可进行修改。 
LOCAL关键字指明游标是局部的,它只能在它所声明的过程中使用。 
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时,游标才不再可用。 
FORWARD_ONLY指明游标只能向前滚动。 
STATIC的游标与INSENSITIVE的游标是相同的。 
KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改,则它们对游标是可见的。因为是固定的关键字集合,所以对关键字列进行修改或新插入列是不可见的。 
DYNAMIC指明游标将反映所有对结果集的修改。 
SCROLL_LOCK是为了保证游标操作的成功,而对修改或删除加锁。 
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。 
注意: 
· 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句,且在选择中包含了聚合表达式,则游标自动为INSENSITIVE的游标。 
· 如果基表没有唯一的索引,则游标创建成INSENSITIVE的游标。 
· 如果SELECT语句包含了ORDER BY,而被ORDER BY的列并非唯一的行标识,则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开,则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此,只是没有INSENSITIVE关键字而已。 
            ii.       打开游标 
打开游标就是创建结果集。游标通过DECLARE语句定义,但其实际的执行是通过OPEN语句。语法如下: 
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name} 
GLOBAL指明一个全局游标。 
Cursor_name是被打开的游标的名称。 
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。 
在游标被打开之后,系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时,表示游标正在被异步迁移,其绝对值(如果@@cursor_rows为-5,则绝对值为5)为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。 
           iii.       从游标中取值 
在从游标中取值的过程中,可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的(在声明时使用SCROLL关键字),则任何时候都可取出结果集中的任意行。对于非滚动的游标,只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下: 
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] 
FROM [GLOBAL] cursor_name} | cursor_variable_name} 
[INTO @variable_name ][,……n]] 
NEXT指明从当前行的下一行取值。 
PRIOR指明从当前行的前一行取值。 
FIRST是结果集的第一行。 
LAST是结果集的最后一行。 
ABSOLUTE n表示结果集中的第n行,该行数同样可以通过一个局部变量传播。行号从0开始,所以n为0时不能得到任何行。 
RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数,则要取出的行在当前行前n行的位置上,如果该值为负数,则返回当前行的后n行。 
INTO @cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前,变量中的值都会一直保持。 
每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功,则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分(例如,在游标被打开之后,基表中的行被删除)。@@fetch_status可以用来构造游标处理的循环。 
例如: 
DECLARE @iname char(20), @fname char(20) 
OPEN author_cur 
FETCH FIRST FROM author_cur INTO @iname, @fname 
WHILE @@fetch_status = 0 
BEGIN 
IF @fname = ‘Albert’ 
PRINT “Found Albert Ringer” 
ELSE 
Print “Other Ringer” 
FETCH NEXT FROM author_cur INTO @iname, @fname 
END 
           iv.       关闭游标 
CLOSE语句用来关闭游标并释放结果集。游标关闭之后,不能再执行FETCH操作。如果还需要使用FETCH语句,则要重新打开游标。语法如下: 
CLOSE [GLOBAL] cursor_name | cursor_variable_name 
            v.       释放游标 
游标使用不再需要之后,要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下: 
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name 

本脚本使用游标循环删除,对于数量比较小,用游标暂不会造成恶劣影响。

澳门金沙vip 1澳门金沙vip 2Code
use master
go

    下面给出游标的一个完整的例子: 
    USE master 
    GO 
    CREATE PROCEDURE sp_BuildIndexes 
    AS 
    DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100) 

复制代码 代码如下:DECLARE @tablename
VARCHAR(30),@sql VARCHAR(500)DECLARE cur_delete_table CURSOR
READ_ONLY FORWARD_ONLY FORSELECT name FROM sysobjects WHERE name LIKE
‘PUB%’ AND type=’U’OPEN cur_delete_tableFETCH NEXT FROM
cur_delete_table INTO @tablenameWHILE @@FETCH_STATUS = 0BEGINSELECT
@sql=’drop table ‘+@tablenameEXEC (@sql)FETCH NEXT FROM
cur_delete_table INTO @tablenameENDCLOSE cur_delete_tableDEALLOCATE
cur_delete_table

IF OBJECT_ID(‘TestDB_2000_2005_2008’) IS NOT NULL
— print ‘Exist databse!’
— else print ‘OK!’
DROP Database TestDB_2000_2005_2008
GO
Create database TestDB_2000_2005_2008
go
use TestDB_2000_2005_2008
go

    DECLARE table_cur CURSOR FOR 
    SELECT name FROM sysobjects WHERE type=’u’ 

IF OBJECT_ID(‘b’) IS NOT NULL
drop table b 
go
create table b(id int identity(1,1),ba int,bb int)
–truncate table b 
insert into b 
select  1,1 union all
select 2,2 union all
select 1,1

    OPEN table_cur 
    FETCH NEXT FROM table_cur INTO @TableName 
    
    WHILE @@fetch_status = 0 
    BEGIN 
       IF @@fetch_status = -2 
           CONTINUE 
       SELECT @msg = “Building indexes for table”+@TableName+”…” 
       PRINT @msg 
       SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”’)” 
       EXEC (@cmd) 
       PRINT “  “ 
       FETCH NEXT FROM table_cur INTO @TableName 
    END 
    DEALLOCATE table_cur 
    GO 
    下面的脚本将为PUBS数据库执行sp_BuildIndexes 
    USE pubs 
    GO 
    EXEC ap_BuildIndexes 
    注意:上面也是创建用户定义的系统存储过程的示例。 

IF OBJECT_ID(‘c’) IS NOT NULL
drop table c
go
create table c(id int identity(1,1),ca int,cb int)
insert into c 
select  1,2 union all
select 1,3

使用临时表 
    临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为,临时表不能在两个连接之间共享,一旦连接关闭,临时表就会被丢弃。如果临时表被创建于存储过程之中,则临时表的范围在存储过程之中,或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表,则需要使用全局的临时表。全局的临时表以“##”符号开头,它将一直存在于数据库中,直到SQL Server重新启动。一旦这类临时表创建之后,所有的用户都可以访问到。在临时表上不能明确地指明权限。    临时表提供了存储中间结果的能力。有时候,临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中,然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时,建议使用临时表。在这种情况下,在临时表中保持数据的子集,以在随后的连接中使用,这样能大大改善性能。还可以在临时表中创建索引。 

先来看看第一种需求:只要数据库中表是空的。
这个其实并不难,用一个游标循环得出所有表名,再清除所有表,delete或truncate
table
提供几个语句:以下语句均在SQL2000/SQL2005/SQL2008下使用通过。
方法甲:

澳门金沙vip 3澳门金沙vip 4Code
/********************MSSQL 2000/2005/2008***********************/

use TestDB_2000_2005_2008
go
select * from b 
select * from c 

Declare @t varchar (1024)
Declare @SQL varchar(2048)
Declare tbl_cur cursor for  select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
OPEN tbl_cur FETCH NEXT  from tbl_cur INTO @t