Visualizar información de todas las tablas de una base de datos(tamaño de cada tabla, las filas de cada tabla, etc)

Mediante este truco de SQL Server 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 los siguientes dos procedimientos (store procedure)...

Primero crear los siguientes dos procedimientos dentro de la Base de datos a mostrar información.  

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

Y para ejecutarlo: 


EXECUTE sp_rpttblspc


--Ejemplo resultado:

Fuente aqui