iihero
2022-06-21
SAP-ASE, DatabaseManagement
##背景
This blog just introduces a simple script to clean up all the data including DDLs under one database. It’s very useful when we are developing or executing testing large scale application on ASE database.
As we know, if using some GUI client app, we also can do the cleanup, but those are manual work. And sometimes, because of some references between tables, you will fail to delete some tables in one time. If there is one script to do this, we can call the script to do the initialization directly without any manual work.
To be attention, the script is better to be used in dev/test environment or used by DBA/DBOps to execute some clean up tasks. Please don’t use it in any PROD env.
The basic idea is:
- For all the tables, get the constraints and disable the constraints
- List all the views
- List all the procedures, excluding it self: sp_cleandb
- Store all the above SQLs into a temp table
- Iterate the statement in the temp table, execute them.
CREATE OR REPLACE PROCEDURE sp_cleandb
AS
BEGIN
DECLARE @strdrop VARCHAR(512)
DECLARE @username VARCHAR(32)
select @strdrop=''
-- not suser_name, use user_name as the db user name to execute the code
SELECT @username=user_name()
SET @strdrop = 'To DELETE content under user: ' + @username
PRINT @strdrop
CREATE TABLE #TTT123(id numeric(19, 0) IDENTITY NOT NULL primary key, col2 VARCHAR(512) NULL)
/* 1. begin drop all constraints */
DECLARE @tname VARCHAR(128), @tableid INT, @cid INT, @vname VARCHAR(128)
DECLARE @cname VARCHAR(128)
set @strdrop=''
DECLARE c_constraints CURSOR FOR
select a.id, c.tableid, a.name from sysobjects a, sysusers b, sysconstraints c where a.type='RI' and a.uid=b.uid and b.name=@username and c.constrid=a.id ORDER BY c.tableid, a.name
open c_constraints
FETCH c_constraints INTO @cid, @tableid, @cname
while (@@sqlstatus=0)
begin
select @tname=name from sysobjects where id=@tableid
set @strdrop='alter table ' + @username + '.' + @tname + ' drop constraint ' + @cname
set @strdrop='INSERT INTO #TTT123(col2) VALUES(''' + @strdrop + ''')'
EXEC (@strdrop)
fetch c_constraints INTO @cid, @tableid, @cname
end
close c_constraints
deallocate cursor c_constraints
/*2 drop tables */
set @tname=''
set @strdrop=''
DECLARE c_tables CURSOR FOR
select a.name from sysobjects a, sysusers b where a.type='U' and a.uid=b.uid and b.name=@username
open c_tables
fetch c_tables INTO @tname
while (@@sqlstatus = 0)
begin
set @strdrop = 'drop table ' + @username + '.' + @tname
set @strdrop='INSERT INTO #TTT123(col2) VALUES(''' + @strdrop + ''')'
EXEC (@strdrop)
fetch c_tables INTO @tname
end
close c_tables
deallocate cursor c_tables
/*3 Views dropping */
set @vname=''
set @strdrop=''
DECLARE c_views CURSOR FOR
select a.name from sysobjects a, sysusers b where a.type='V' and a.uid=b.uid and b.name=@username
open c_views
fetch c_views INTO @vname
while (@@sqlstatus = 0)
begin
set @strdrop = 'drop view ' + @username + '.' + @vname
set @strdrop='INSERT INTO #TTT123(col2) VALUES(''' + @strdrop + ''')'
EXEC (@strdrop)
fetch c_views INTO @vname
end
close c_views
deallocate cursor c_views
/*4 drop procedures*/
DECLARE @procname VARCHAR(128)
DECLARE c_procs CURSOR FOR
select a.name from sysobjects a, sysusers b where a.type='P' and a.uid=b.uid and b.name=@username and NOT a.name='sp_cleandb'
open c_procs
fetch c_procs INTO @procname
while (@@sqlstatus=0)
begin
set @strdrop = 'drop procedure ' + @username + '.' + @procname
set @strdrop='INSERT INTO #TTT123(col2) VALUES(''' + @strdrop + ''')'
EXEC (@strdrop)
fetch c_procs INTO @procname
end
close c_procs
deallocate cursor c_procs
/* 5 drop from the temp table #TTT123 */
set @strdrop=''
DECLARE c_tmptable CURSOR FOR
SELECT col2 FROM #TTT123 ORDER BY id
open c_tmptable
FETCH c_tmptable INTO @strdrop
while (@@sqlstatus=0)
begin
print @strdrop
EXEC (@strdrop)
fetch c_tmptable INTO @strdrop
end
close c_tmptable
deallocate cursor c_tmptable
end
go
--
--exec sp_cleandb
--go
exec sp_cleandb
It will automatically clean up all the Table/View/Procedures under the current db user.