【vnsc5858威尼斯城官网】MSSQL 删除数据库里某个用

时间:2020-03-14 08:33来源:计算机教程
--Title:删除数据库里某个用户所有表里的数据 --Author:wufeng4552 --Date:2009-09-21 15:08:41 --方法1 复制代码代码如下: declare @uname varchar(20) declare cuser cursor for selectso.name from sysobjects so,sysusers su

--Title:删除数据库里某个用户所有表里的数据 --Author:wufeng4552 --Date :2009-09-21 15:08:41 --方法1 复制代码 代码如下: declare @uname varchar(20) declare cuser cursor for select so.name from sysobjects so,sysusers su where so.uid=su.uid and su.name='Stone' and so.xtype='U' open cuser fetch next from cuser into @uname while(@@fetch_status=0) begin exec('truncate table [Stone].[' @uname ']') fetch next from cuser end close cuser deallocate cuser --方法2 复制代码 代码如下: exec sp_msforeachtable @command1="truncate table ? ;",@whereand='and schema_id = (select schema_id from sys.schemas where [name] =''Stone'')'

第一种方法:

复制代码 代码如下:

CREATE proc [dbo].[delAllRecord]asdeclare @tableName nvarchar(255)declare @Sql nvarchar(255)

Declare curTable Cursor for select Table_Name from information_schema.tables where TABLE_TYPE='BASE TABLE'Open curTableFetch Next From curTable Into @tableName

WHILE(@@FETCH_STATUS = 0) BEGIN set @Sql = N'delete from ' @tableName exec sp_executesql @sql Fetch Next From curTable Into @tableName endCLOSE curTableDEALLOCATE curTable

第二种方法:

复制代码 代码如下:

--declare test_cursor cursor scroll for

--select id,table_name from dbo.section_type

--open test_cursor

--declare @id int

--declare @table_name nvarchar(50)

--while @@fetch_status=0

--begin

--fetch next from test_cursor into @id,@table_name

--print @id

--print @table_name

--end

--close test_cursor

--deallocate test_cursor

--删除projectrangtree的脏数据

delete from projectrangtree where deleteversion0

delete from projectrangtree where type=3 and parentid not in(select id from projectrangtree where type=2)

delete from projectrangtree where type=4 and parentid not in(select id from projectrangtree where type=3)

delete from projectrangtree where type=5 and parentid not in(select id from projectrangtree where type=4)

编辑:计算机教程 本文来源:【vnsc5858威尼斯城官网】MSSQL 删除数据库里某个用

关键词: