drop all tables in database by T-SQL in SQLServer
来源:互联网 发布:ie内核浏览器for mac 编辑:程序博客网 时间:2024/04/30 03:28
firstly I searched this topic in Google, but unfortunately, I cannot find any valueable article to
resolve it. someone think give an " drop database ; create database" idea.but always it doesn't match for our true intention. the reason is that this solution will delete all the related objects and even serious, the security (previleges grant) are also deleted. It make the table restore difficult.
in fact,it is really easy to implement it by an T-SQL.
following is my solution. (enviorment windows2003+SQL SERVER2005).
--it's my first time to write T-SQL function,share it for commemoration:-)
--delete all tables under current DB
--written by shenjian @ 2006/03/17
--if "foreign key constraint error" occur,just ignore it.
declare
@tblName varchar(2000),
@count int ,
@ExecStr varchar(2000),
@localcounter int
--written by shenjian @ 2006/03/17
--if "foreign key constraint error" occur,just ignore it.
declare
@tblName varchar(2000),
@count int ,
@ExecStr varchar(2000),
@localcounter int
set @count=0
set @localcounter=1 --only need a <>0 value to start the loop,no care what it is
while @localcounter<>0 --to deal the foreign key constraint
begin
set @localcounter=0
--cursor should be cycled for next time
declare curName cursor for
select name from sys.tables order by name
open curName
set @localcounter=1 --only need a <>0 value to start the loop,no care what it is
while @localcounter<>0 --to deal the foreign key constraint
begin
set @localcounter=0
--cursor should be cycled for next time
declare curName cursor for
select name from sys.tables order by name
open curName
fetch curName into @tblName
while @@fetch_status =0
begin
set @localcounter=@localcounter+1
select @ExecStr='drop table "'+@tblName+'"'
--print('SQL string:'+@ExecStr)
EXEC (@ExecStr)
if(@@error<>0)
begin
print(@@error)
end
fetch curName into @tblName
end
set @count=@count+@localcounter
close curName
deallocate curName
end
print('.....drop all table success....')
print('drop '+convert(varchar(5),@count)+' tables')
go
while @@fetch_status =0
begin
set @localcounter=@localcounter+1
select @ExecStr='drop table "'+@tblName+'"'
--print('SQL string:'+@ExecStr)
EXEC (@ExecStr)
if(@@error<>0)
begin
print(@@error)
end
fetch curName into @tblName
end
set @count=@count+@localcounter
close curName
deallocate curName
end
print('.....drop all table success....')
print('drop '+convert(varchar(5),@count)+' tables')
go
- drop all tables in database by T-SQL in SQLServer
- SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)【复杂版本】
- tables in database
- Find Text in all columns of all tables in a Database
- get all rows count of all tables in a mysql database.
- the database principal owns a service in the database -- can't drop a user
- Oralce drop all tables
- Check if a database and tables exist in sql server in a vb .net project
- QL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)【复杂版本】
- Get Tables of SQLServer in PowerShell
- How to identify all the Corrupted Objects in the Database reported by RMAN
- Liferay get Organization by using User or get All Organizations in liferay database
- check if filed exists in all tables in current DB
- 186.User A executes the following command to drop a large table in your database: SQL> DROP TABLE tr
- 【sqlServer】Order By 报错:The ORDER BY clause is invalid in views, inline functions, derived tables
- Oracle by Example - Oracle Database 10g Release 2 on a Single Database Instance (All In One CHM)
- groovy Exports all table names in database
- All about control file in Oracle Database
- 运行Silverlight报错可能与金山词霸有关
- C#Sqlserver2000
- 打印集合所有的子集
- 10-1 使用数据视图便捷类(Using the Item View Convenience Classes)
- .Net3.5新的Socket异步模式总结
- drop all tables in database by T-SQL in SQLServer
- 随笔——感悟个人能力提高的三个阶段
- C# 加密
- 实现细线表格
- 同名model导致的Invalid operation for the current cursor position
- 一般的对表单数据验证的javascript代码收集
- 安装SQL Server2000 与 SP4补丁时挂起解决
- 重载++运算符
- 怎么管