-- ================================================
--
-- 根据指定表名,删除其它数据表格如下为,删除除people以外的使有表数据
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Shayne>
-- Create date: <Create Date,,>
-- Add the parameters for the stored procedure here
AS
BEGIN
--获取Sys数据库中所有表名
declare
@names nvarchar(200),
@Name nvarchar(200)
exec(@Name);
declare tableNames cursor local
for
SELECT Name FROM Sys..SysObjects Where XType='U' ORDER BY Name
--打开游标
open tableNames
Fetch next from tableNames into @names
while(@@FETCH_STATUS=0)
begin
if ('people' !=@names)
begin
set @Name='delete from '+@names;
exec(@Name);
end
Fetch next from tableNames into @names
end
close tableNames--关闭游标
deallocate tableNames;--删除游标
END
GO
--
-- 根据指定表名,删除其它数据表格如下为,删除除people以外的使有表数据
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Shayne>
-- Create date: <Create Date,,>
-- Description: <Description,,
--表名
--Student, people, Info..
>
-- =============================================
CREATE PROCEDURE deletTableByNames-- Add the parameters for the stored procedure here
AS
BEGIN
--获取Sys数据库中所有表名
declare
@names nvarchar(200),
@Name nvarchar(200)
exec(@Name);
declare tableNames cursor local
for
SELECT Name FROM Sys..SysObjects Where XType='U' ORDER BY Name
--打开游标
open tableNames
Fetch next from tableNames into @names
while(@@FETCH_STATUS=0)
begin
if ('people' !=@names)
begin
set @Name='delete from '+@names;
exec(@Name);
end
Fetch next from tableNames into @names
end
close tableNames--关闭游标
deallocate tableNames;--删除游标
END
GO