- 论坛徽章:
- 7
|
第二个:
if (object_id('dbseg')<>null) drop procedure dbseg
go
create procedure dbseg
as
begin
--usage: isql -Usa -Pxxxx -Sssss -b > dbseg.log
-- 1>exec dbseg
-- 2>go
set nocount on
declare @name varchar(30)
declare @dat int
declare @mix int
declare @log int
declare @iii int
select "--Device DDL"
select a.name,b.segmap,sum(b.size/512) as size into #dbseg
from sysdatabases a,sysusages b
where a.dbid=b.dbid and a.dbid>3 and a.dbid<31513
group by a.name,b.segmap
order by a.name
set rowcount 1
while exists (select name from #dbseg)
begin
select @name=name from #dbseg
select "--"+@name+":"
if exists(select 1 from #dbseg where name=@name and segmap=3)
begin
select @dat=size from #dbseg where name=@name and segmap=3
select "disk init name="+@name+"dat1,physname='/data/dev/"+@name+"dat1.dev',size='"+convert(varchar(20),@dat)+"M'"
select "go"
end
if exists(select 1 from #dbseg where name=@name and segmap=7)
begin
select @mix=size from #dbseg where name=@name and segmap=7
select "disk init name="+@name+"mix1,physname='/data/dev/"+@name+"mix1.dev',size='"+convert(varchar(20),@mix)+"M'"
select "go"
end
if exists (select 1 from #dbseg where name=@name and segmap=4)
begin
select @log=size from #dbseg where name=@name and segmap=4
select "disk init name="+@name+"log1,physname='/data/dev/"+@name+"log1.dev',size='"+convert(varchar(20),@log)+"M'"
select "go"
end
set rowcount 0
delete #dbseg where name=@name
set rowcount 1
end
select "--Database DDL"
set rowcount 0
select a.name,b.segmap,b.size/512 as size into #dbseg2
from sysdatabases a,sysusages b
where a.dbid=b.dbid and a.dbid>3 and a.dbid<31513
order by a.name,b.lstart
while exists (select name from #dbseg2)
begin
set rowcount 1
select @name=name from #dbseg2
select "--"+@name+":"
declare @seg int
select @seg=segmap from #dbseg2 where name=@name
if (@seg=3)
begin
select @dat=0,@log=0
exec segcount @seg output,@dat output,@name
exec segcount @seg output,@log output,@name
select "create database "+@name+" on "+@name+"dat1="+convert(varchar(30),@dat)+" log on "+@name+"log1="+convert(varchar(30),@log)+" for load"
select "go"
end
if @seg=7
begin
select @mix=0,@dat=0,@log=0
exec segcount @seg output,@mix output,@name
exec segcount @seg output,@dat output,@name
if (@seg=7)
begin
select "create database "+@name+" on "+@name+"mix1="+convert(varchar(30),@mix)+" for load"
select "go"
end
else
if (@seg=4)
begin
select @log=@dat
select @dat=0
select "create database "+@name+" on "+@name+"mix1="+convert(varchar(30),@mix)+" for load"
select "go"
select "alter database "+@name+" log on "+@name+"log1="+convert(varchar(30),@log)+" for load"
select "go"
end
else
if (@seg=3)
begin
exec segcount @seg output,@log output,@name
select "create database "+@name+" on "+@name+"mix1="+convert(varchar(30),@mix)+" for load"
select "go"
select "alter database "+@name+" on "+@name+"dat1="+convert(varchar(30),@dat)+" log on "+@name+"log1="+convert(varchar(30),@log)+" for load"
select "go"
end
end
while exists (select 1 from #dbseg2 where name=@name)
begin
set rowcount 1
exec segcount @seg output,@dat output,@name
if (@seg=7) select "alter database "+@name+" on dat"+@name+"mix1="+convert(varchar(30),@dat)+" for load"
else if (@seg=3) select "alter database "+@name+" on "+@name+"dat1="+convert(varchar(30),@dat)+" for load"
else if (@seg=4) select "alter database "+@name+" log on "+@name+"log1="+convert(varchar(30),@dat)+" for load"
select "go"
end
end
end
go |
|