O script abaixo cria a procedure sp_dba_estrutura a qual permite obter a estrutura dos database no SQL Server 7.0 ou 2000. Ao executar a procedure você terá as seguintes informações para os arquivos de dados e log:
=========================================
Estrutura dos bancos para recuperaCao
=========================================
DATABASE: DBADM
NOME DO FILE: DBADM
ID DO FILE: 1
PATH DO FILE: G:/MSSQL$A/data/DBADM.mdf
GRUPO DO FILE : PRIMARY
SIZE: 768 KB
MAX SIZE: Unlimited
GROWTH: 80 KB
USAGE: data only
=================================================
DATABASE: DBADM
NOME DO FILE: DBADM_log
ID DO FILE: 2
PATH DO FILE: G:/MSSQL$A/data/DBADM_log.LDF
SIZE: 504 KB
MAX SIZE: Unlimited
GROWTH: 80 KB
USAGE: log only
A PROCEDURE
if exists (select * from sysobjects where id = object_id(‘dbo.sp_dba_estrutura’) and sysstat & 0xf = 4)
drop procedure dbo.sp_dba_estrutura
GO
set quoted_identifier off
set nocount on
go
create procedure sp_dba_estrutura
/* @CMD varchar(255)= OUTPUT */
as
declare @dbname char(20)
declare @CMD varchar (1024)
declare @nome char(25)
declare @fileid char(2)
declare @filename char(80)
declare @filegroup char(20)
declare @size varchar(20)
declare @maxsize varchar(20)
declare @growth nvarchar(20)
declare @usage char(20)
/*Descricao :
* Esta procedure objetiva demonstrar a estrutura de criação dos fILES
* de todos os databases
* Elaborada por : Joao Tadeu dos Santos – DBA – Suporte – Informática
* Data : 24/04/2001 */
create table #temp_estrutura
( dbname char(25) null ,
nome char(25),
fileid char(2),
filename char(80),
filegroup char(20) null,
size varchar(20),
maxsize varchar(20),
growth nvarchar(20),
usage char(20))
begin transaction estrutura
DECLARE dbcursor CURSOR for
SELECT name
FROM master..sysdatabases order by name
FOR READ ONLY
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
select @CMD = “insert into #temp_estrutura ” + “select ” + “‘” +
rtrim(@dbname) + “‘” + “, t1.name, t1.fileid, t1.filename,
t2.groupname,convert(nvarchar(15), t1.size * 8) + N’ KB’,
caset1.maxsize when -1 then N’Unlimited’ else convert(nvarchar(15),
t1.maxsize * 8) + N’ KB’ end),(case t1.status & 0x100000 when
0x100000 then convert(nvarchar(3), t1.growth) + N’%’ else convert
nvarchar(15), t1.growth * 8) + N’ KB’ end), (case t1.status & 0x40
when 0x40 then ‘log only’ else ‘data only’ end) from ” + “master”
+”..sysaltfiles t1,” + “master” + “..sysfilegroups t2 ” +
” WHERE dbid= DB_ID” + “(” + “‘” + rtrim (@dbname) + “‘” + “)” +
” and t1.groupid *= t2.groupid order by fileid”
/* print @CMD */
exec (@CMD)
FETCH NEXT FROM dbcursor INTO @dbname
END
close dbcursor
DEALLOCATE dbcursor
print ‘ ‘
print ‘=========================================’
print ‘ Estrutura dos bancos para recuperaCao ‘
print ‘=========================================’
print ‘ ‘
DECLARE dbcursor1 CURSOR for
select dbname, nome,fileid,filename, filegroup,size,maxsize,growth,usage from #temp_estrutura
order by dbname, fileid
FOR READ ONLY
OPEN dbcursor1
FETCH NEXT FROM dbcursor1 INTO @dbname, @nome,@fileid,@filename, @filegroup,@size,@maxsize,@growth,@usage
WHILE (@@FETCH_STATUS <> -1)
BEGIN
select @CMD = ‘DATABASE : ‘ + @dbname
print @CMD
select @CMD = ‘NOME DO FILE : ‘ + @nome
print @CMD
select @CMD = ‘ID DO FILE : ‘ + @fileid
print @CMD
select @CMD = ‘PATH DO FILE : ‘ + convert (varchar (80),@filename)
print @CMD
select @CMD = ‘GRUPO DO FILE : ‘ + @filegroup
print @CMD
select @CMD = ‘SIZE : ‘ + @size
print @CMD
select @CMD = ‘MAX SIZE : ‘ + @maxsize
print @CMD
select @CMD = ‘GROWTH : ‘ + @growth
print @CMD
select @CMD = ‘USAGE : ‘ + @usage
print @CMD
SELECT @CMD = ‘=====================================================================’
PRINT @CMD
FETCH NEXT FROM dbcursor1 INTO @dbname, @nome,@fileid,@filename, @filegroup,@size,@maxsize,@growth,@usage
END
close dbcursor1
DEALLOCATE dbcursor1
SELECT @CMD = ‘================ F I M ===========================================’
PRINT @CMD
commit transaction estrutura
— autor : João Tadeu dos Santos