Tablas de Sistema

Hola, mira no se bien que queres hacer pero yo uso esto:

sp_helpdb basedatos

Usar tablas de sistema no es recomendado por los cambios de version y todo
ello.

Si con un store o vista como las Information_schema no lo podes resolver,
bueno ahi si quizas debas acceder a tus tablas de sistema.

Salu2

Maximiliano Damian Accotto


"Glambo" escribió en el mensaje
news:0a9501c3d5e8$04fa7e40$
Buenas tardes me gustaria saber que tablas tengo que
acceder para saber el espacio en Kb ocupa cada una de las
tablas de mi base de datos.

Gracias, y Feliz año 2004.

Este procedimento lo copié la verdad no recuerdo de que pagina pero te
puede servir.. mis reconocimentos para quien lo hizo

Un saludo
Jose Nadim

Create procedure sp_sys_allspace
as

declare @objname varchar(92) -- The object we want size on.
declare @id int -- The object id of @objname.
declare @type smallint -- The object type.
declare @pages int -- Working variable for size
calc.
declare @dbname varchar(30)
declare @dbsize dec(15,0)

declare @trows dec(15,0)
declare @tall dec(15,0)
declare @tdata dec(15,0)
declare @tidx dec(15,0)
declare @tuu dec(15,0)

declare @srows dec(15,0)
declare @sall dec(15,0)
declare @sdata dec(15,0)
declare @sidx dec(15,0)
declare @suu dec(15,0)

declare @irows dec(15,0)
declare @iall dec(15,0)
declare @idata dec(15,0)
declare @iidx dec(15,0)
declare @iuu dec(15,0)
declare @iname varchar(20)

declare @msg varchar(80)

declare AllTables insensitive cursor
for
select name from sysobjects order by name

open AllTables

/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255
(text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
name varchar(92),
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
select getdate()

fetch next from AllTables into @objname

/*
** Insert the row for the table
*/

insert into #spt_space (name,rows,reserved,data,indexp,unused)
values ('zzUser Table Totals',0,0,0,0,0)
insert into #spt_space (name,rows,reserved,data,indexp,unused)
values ('zzSystem Table Totals',0,0,0,0,0)

while @@fetch_status = 0
begin

/*
** Find the object.
*/
select @id = null
select @id = id, @type = sysstat & 0xf
from sysobjects
where id = object_id(@objname)

/*
** See if it's a space object.
** types are:
** 1 - system table
** 2 - view
** 3 - user table
** 4 - sproc
** 6 - default
** 7 - rule
** 8 - trigger
** 9 - primary key
** 10 - check constraint
** 11 - foreign key
** 12 - replication filter stored proc
*/

if @type in (1,3)
begin
/*
** Insert the row for the table
*/
insert into #spt_space (name)
values (@objname)
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
/* insert into #spt_space (reserved) */
select @iall = sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
update #spt_space
set reserved = @iall
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set reserved = reserved + @iall
where name = 'zzUser Table Totals'
else
update #spt_space
set reserved = reserved + @iall
where name = 'zzSystem Table Totals'

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2 and id = @id

select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255 and id = @id
update #spt_space
set data = @pages
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set data = data + @pages
where name = 'zzUser Table Totals'
else
update #spt_space
set data = data + @pages
where name = 'zzSystem Table Totals'


/* index: sum(used) where indid in (0, 1, 255) - data */
select @iidx = sum(used)
from sysindexes
where indid in (0, 1, 255) and id = @id
update #spt_space
set indexp = @iidx - data
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set indexp = indexp + (@iidx - data)
where name = 'zzUser Table Totals'
else
update #spt_space
set indexp = indexp + (@iidx - data)
where name = 'zzSystem Table Totals'


/* unused: sum(reserved) - sum(used) where indid in (0, 1,
255) */
update #spt_space
set unused = reserved - @iidx
where name = @objname
/* Update Totals */
if @type = 3
update #spt_space
set unused = unused + (reserved - @iidx)
where name = 'zzUser Table Totals'
else
update #spt_space
set unused = unused + (reserved - @iidx)
where name = 'zzSystem Table Totals'

select @irows = i.rows
from sysindexes i
where i.indid < 2 and i.id = @id
update #spt_space
set rows = @irows
where name = @objname
/* Update totals */
if @type = 3
update #spt_space
set rows = rows + @irows
where name = 'zzUser Table Totals'
else
update #spt_space
set rows = rows + @irows
where name = 'zzSystem Table Totals'
end
fetch next from AllTables into @objname
end
/* end while loop here and print out totals */
select 'Table' = substring(#spt_space.name, 1, 20),
'Rows' = str(rows,12),
'Reserved KB' = (str(reserved * d.low / 1024.,15)),
'Data Space KB' = (str(data * d.low / 1024.,15)),
'Index Space KB' = (str(indexp * d.low / 1024.,15)),
'Unused Space KB' = (str(unused * d.low / 1024.,15))
from #spt_space, master.dbo.spt_values d
where (d.number = 1 and d.type = 'E')
order by #spt_space.name
close alltables
deallocate alltables

sp_spaceused

=Miguel Egea
http://www.portalsql.com
Microsoft SQL-SERVER MVP.
Brigada Anti-Cursores
Aviso de Seguridad
http://www.microsoft.com/spain/tech...9-USER.asp
==
"Glambo" escribió en el mensaje
news:0a9501c3d5e8$04fa7e40$
Buenas tardes me gustaria saber que tablas tengo que
acceder para saber el espacio en Kb ocupa cada una de las
tablas de mi base de datos.

Gracias, y Feliz año 2004.

Usa:

sp_MSForEachTable 'sp_spaceused ''?'''

saludos,

Buenas tardes me gustaria saber que tablas tengo que
acceder para saber el espacio en Kb ocupa cada una de

las

tablas de mi base de datos.

Gracias, y Feliz año 2004.
.

Hermano Sergio ;) no estara usted usando funciones reservadas de casualidad
;-)

pd: como anda todo, creo que vos me debes algo no? ;)


Salu2 enormes

Maximiliano Damian Accotto

Fundicion San Cayetano S.A.
Gerente de IT
Buenos Aires - Argentina
-
Desarrollador 3 estrellas
http://www.microsoft.com/spanish/ms...efault.asp
-
(maxi_accotto[arroba]speedy.com.ar)
MSN:
"Sergio Monteiro" escribió en el mensaje
news:050101c3d63b$e4c90660$

Usa:

sp_MSForEachTable 'sp_spaceused ''?'''

saludos,

Buenas tardes me gustaria saber que tablas tengo que
acceder para saber el espacio en Kb ocupa cada una de

las

tablas de mi base de datos.

Gracias, y Feliz año 2004.
.