create procedure sp_dba_gendbddl @dbname varchar(32) = null
as
declare @db_name varchar(30),
@dbid smallint,
@segmap smallint,
@segs varchar(4),
@dev_frag varchar(30),
@frag_size char(10),
@sql_string varchar(255),
@alter_status tinyint,
@counter int,
@seq_no int,
@numdbs int
set nocount on
if @dbname is null select @dbname = "%"
-- Match databases if given else do them all
--
select @numdbs = count(*) from master.dbo.sysdatabases where name like @dbname
-- DB exists ?
--
if @numdbs = 0
begin
/* 17590, "The specified database does not exist." */
raiserror 17590
return (1)
end
-- Initialize dbids twmp table from sysdatabases
--
select dbid into #dbids from master.dbo.sysdatabases where name like @dbname
declare @curdbid smallint /* the one we're currently working on */
declare @dbdesc varchar(102) /* the total description for the db */
declare @bitdesc varchar(30) /* the bit description for the db */
-- Set @curdbid to the first dbid.
--
select @curdbid = min(dbid) from #dbids
create table #db_info(
seq_no int not null,
sql_string varchar(255) null
)
-- Iterate through by dbid
--
while @curdbid is not NULL
begin
/************************************************
* Temporary table to hold individual fragments *
* of SQL_STRING, numbered to preserve their *
* correct sequence *
************************************************/
delete #db_info
-- Initialize variables
select @alter_status = 0
select @counter = 0
select @seq_no = 0
select @db_name = db_name(@curdbid)
/* Declare a cursor to allow processing of each row individually */
/* Select statement extracts relevant info from the system tables */
/* ORDER BY clause is critical in order to ensure that all fragments */
/* are processed in the correct sequence */
declare get_db_info cursor for
select db.dbid, usg.segmap, "device fragment"=substring(dev.name,1,15), "size(MB)" = str(usg.size/512.,10,0)
from master.dbo.sysusages usg,
master.dbo.sysdevices dev,
master.dbo.sysdatabases db
where vstart between low and high
and cntrltype = 0
and db.dbid = @curdbid
and db.dbid =usg.dbid
order by db.dbid,usg.lstart,dev.name
-- Prepare initial SQL string
select @sql_string = 'create database ' + @db_name + ' on '
-- Store initial SQL_STRING in temporary table and
-- number it to allow later extraction in the correct
-- sequence
insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)
-- Increment the sequence number
select @seq_no = @seq_no + 1
-- Open the cursor and fetch the first row
open get_db_info
fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size
if (@@sqlstatus=2)
begin
select "No information found for database"
close get_db_info
deallocate cursor get_db_info
return
end
while (@@sqlstatus = 0)
begin
if @alter_status = 0 /* Still part of original 'create database ...' command */
begin
if @segmap = 4 /* Must refer to log device */
begin
select @sql_string = ' log on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
select @alter_status = 1
end
else /* Must refer to data device */
begin
if @counter = 0 /* 1st data device of "create database ..." */
begin
select @sql_string = ' ' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))
select @counter = @counter + 1
end
else /* Additional data device for "create database ..." */
begin
select @sql_string = ' ,' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))
end
end
end
else /* Must be an alter database command */
begin
select @sql_string = 'alter database ' + @db_name
insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)
select @seq_no = @seq_no + 1
-- Log = 4 or data
--
if @segmap = 4
select @sql_string = ' log on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
else
select @sql_string = ' on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
end
insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)
select @seq_no = @seq_no + 1
-- Get next row
fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size
end
close get_db_info
deallocate cursor get_db_info
-- Select each piece of SQL from temporary table ordering by */
-- sequence number */
--
declare print_createdb_cursor cursor for
select sql_string
from #db_info
order by seq_no
for read only
open print_createdb_cursor
fetch print_createdb_cursor into @sql_string
while (@@sqlstatus = 0)
begin
print @sql_string
fetch print_createdb_cursor into @sql_string
end
close print_createdb_cursor
deallocate cursor print_createdb_cursor
-- Get next dbid.
--
select @curdbid = min(dbid) from #dbids where dbid > @curdbid
end
drop table #db_info
drop table #dbids
return(0)
No comments:
Post a Comment