Buscar en el sitio

Contacto

Danny

962318754

blackorwhite_dm@hotmail.com

Visualizar el tamaño de una base de datos

03.11.2010 01:36

Gracias a este truco de SQL Server 2000 podrás visualizar la información de todas las tablas de una base de datos. Podrás ver el tamaño de cada tabla de la base de datos, las filas que tiene cada tabla, el tamaño de los índices y el espacio reservado que tiene (Allocated size)



Para ello, tenemos que crear dos storeds.




CREATE PROCEDURE sp_rpttblspc (@dbname varchar(30) = null, @tblname varchar(500) = null)
as


/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 15 April 1996 */
/* Description: Used TO CREATE a report OF tables & number of rows. */
/* Resources : http://www.umachandar.com/resources.htm */


SET nocount ON
DECLARE @tablename varchar(500), @cmdstr varchar(255), @stringa varchar(500)
SELECT @dbname = isnull(@dbname, db_name()), @tblname = isnull(@tblname , '') + '%'
IF db_id(@dbname) IS NULL
BEGIN
raiserror('Invalid DATABASE name was specified.', -1, -1)
return(1)
END
CREATE TABLE #tblspc
(name varchar(500), rows varchar(10), reserved varchar(20), data varchar(20),
index_size varchar(20), unused varchar(20))
set @stringa ='declare tbls CURSOR FOR SELECT name FROM [' + @dbname + '].dbo.sysobjects WHERE type = ''U'' AND name LIKE ''' + @tblname + ''''
PRINT @stringa
exec(@stringa)
OPEN tbls
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM tbls INTO @tablename
IF @@fetch_status <; 0 BREAK
SELECT @cmdstr = 'use ' + @dbname + ' EXEC sp_spaceused ''[' + @tablename + ']'''
INSERT INTO #tblspc exec(@cmdstr)
IF @@error <;>; 0
BEGIN
DEALLOCATE tbls
raiserror('Fatal error, unable TO obtain space details FOR tables.', -1, -1)
return(1)
END
end
DEALLOCATE tbls
SELECT name AS "Table Name:", rows as "Number OF Rows:" ,
data AS "Data Size", index_size as "Index Size",
reserved AS "Allocated Size"
FROM #tblspc
--ORDER BY convert(int, rows) desc, 1
ORDER BY convert(int, substring(reserved,1,PATINDEX('% KB%',reserved))) desc, 1
return(0)
GO


Y seguidamente:

CREATE PROCEDURE sp_TablesSize @bd as varchar(50)='DBA' AS
declare @db as char(30)
if @bd is null or @bd = '' begin set @bd='DBA' end
Create table #mydbs (dbname char( 255), size char( 255), dbowner char( 255), dbid int,
crdate datetime, status char( 255), Comp_lvl char( 255))
insert #mydbs Exec sp_helpdb
declare db_cursor CURSOR FOR SELECT dbname from #mydbs where dbname = @bd
open db_cursor
fetch next from db_cursor into @db
while (@@fetch_status <;>; -1)
begin
exec sp_rpttblspc @db
fetch next from db_cursor into @db
end
deallocate db_cursor
Drop table #mydbs
GO

NOTA: Este truco de sql server incorpora el código fuente del truco.
Puedes descargar el truco pulsando aquí