Table size from sysobjects

How can you add the table size in MB detail to the following query:

select  object_name(b.id) as tableName,rows from sysindexes a, sysobjects b where a.id = b.id AND b.id >99 AND indid < 2
LVL 6
anushahannaAsked:
Who is Participating?
 
wittysloganConnect With a Mentor Commented:
I would use sp_spaceused:
a sort of insert into spaceused table
exec sp_spaceused [.dbo.'+@TABLENAME+']'
and then do a join or just fill a table with data:

Here's something I wrote which may help.

drop table index_count
drop table TABLE_INDEX_INFO
drop table table_list
drop table database_list
DECLARE @DBNAME VARCHAR (1000)
DECLARE @TABLENAME VARCHAR (1000)
DECLARE @CMD VARCHAR (1000)
declare @sql VARCHAR (1000)
declare @sql1 VARCHAR (1000)
CREATE TABLE INDEX_COUNT
(INDEX_NAME VARCHAR(1000),INDEX_DESCRIPTION varchar (1000),INDEX_KEYS VARCHAR(1000))
CREATE TABLE TABLE_INDEX_INFO
(DATABASE_NAME VARCHAR (1000),TABLE_NAME VARCHAR(1000),[ROWS] INT ,RESERVED VARCHAR(1000),[DATA] VARCHAR(1000),INDEX_SIZE VARCHAR(1000),UNUSED VARCHAR (1000),INDEX_COUNT INT)

Create table Table_list ([TABLE_NAME] varchar (1000))

Create table Database_list (DATABASE_NAME varchar(1000),DATABASE_SIZE int,remarks varchar (1000))
insert into database_list
exec sp_databases

select *
from Database_list

if (select count(*) from Database_list) > 0

Select @DBNAME =''
while exists (select DATABASE_NAME from master.dbo.Database_list where DATABASE_NAME>@DBNAME)

	BEGIN
	select @DBNAME=min(DATABASE_NAME) from master.dbo.Database_list where DATABASE_NAME >@DBNAME
	

	delete master.dbo.Table_list
	
		select @sql1='insert into master.dbo.Table_list
		SELECT name FROM '+@dbname+'.dbo.sysobjects WHERE type = ''U'''
		Print (@sql1)
		exec (@sql1)
		Select @TABLENAME =''
		while exists (select TABLE_NAME from master.dbo.TABLE_LIST where TABLE_NAME>@TABLENAME)

			BEGIN
			select @TABLENAME=min([TABLE_NAME]) from master.dbo.TABLE_LIST where TABLE_NAME >@TABLENAME
			
			select @sql='use ' +@dbname
			select @sql=@sql+' exec sp_spaceused [.dbo.'+@TABLENAME+']'
			
			
			
			INSERT INTO TABLE_INDEX_INFO
			(TABLE_NAME,[ROWS],RESERVED,[DATA],INDEX_SIZE,UNUSED)
			EXEC (@sql)

			select @sql='use ' +@dbname
			
			select @sql=@sql+' exec SP_HELPINDEX [.dbo.'+@TABLENAME+']'
			
			
			INSERT INTO INDEX_COUNT
			EXEC (@sql)
			UPDATE table_index_info
			set index_count =(select Count(*) from INDEX_COUNT) where index_count  is null
			delete INDEX_COUNT
			END
		
		UPDATE TABLE_INDEX_INFO
		set database_name=@DBNAME where database_name  is null
		
	end

	select *
from 		TABLE_INDEX_INFO
order by rows desc
			


	

Open in new window

0
 
anushahannaAuthor Commented:
wittyslogan, it gives errors, because of missing schema in the code..
0
 
wittysloganCommented:
these errors are they the drop table bits at the start?

run it without these bits.  
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
anushahannaAuthor Commented:
wittyslogan, foll is an example of a set of errors.

I think the problem is the code assumes dbo schema, but there are other schemas in the tables

exec sp_spaceused [.dbo.'+@TABLENAME+']'
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object '.dbo.AETSET' does not exist in database 'SINGT' or is invalid for this operation.

(0 row(s) affected)
Msg 15009, Level 16, State 1, Procedure sp_helpindex, Line 37
The object '.dbo.AETSET' does not exist in database 'SINGT' or is invalid for this operation.

Open in new window

0
 
wittysloganCommented:
if you don't use dbo do you get the errors?
0
 
anushahannaAuthor Commented:
yes, because it is looking for its right schema.. :( sorry..
0
 
wittysloganCommented:
how are you using the sp_spaceused procedure
are you using it as a separate peiece of code?
0
 
anushahannaAuthor Commented:
no, only part of the whole query you have provided..
0
 
wittysloganCommented:
so which piece of the code I have provided are you using could you supply it as it's written?
0
 
anushahannaAuthor Commented:
I running the below.. (i took of the master.dbo.) and creating it in the user db.

also the collation is case sensitive, so i make all the tables the same sensitivity..

thanks
drop table index_count
drop table TABLE_INDEX_INFO
drop table table_list
drop table database_list
DECLARE @DBNAME VARCHAR (1000)
DECLARE @TABLENAME VARCHAR (1000)
DECLARE @CMD VARCHAR (1000)
declare @sql VARCHAR (1000)
declare @sql1 VARCHAR (1000)
CREATE TABLE index_count
(INDEX_NAME VARCHAR(1000),INDEX_DESCRIPTION varchar (1000),INDEX_KEYS VARCHAR(1000))
CREATE TABLE TABLE_INDEX_INFO
(DATABASE_NAME VARCHAR (1000),TABLE_NAME VARCHAR(1000),[ROWS] INT ,RESERVED VARCHAR(1000),[DATA] VARCHAR(1000),INDEX_SIZE VARCHAR(1000),UNUSED VARCHAR (1000),index_count INT)

Create table table_list ([TABLE_NAME] varchar (1000))

Create table database_list (DATABASE_NAME varchar(1000),DATABASE_SIZE int,remarks varchar (1000))
insert into database_list
exec sp_databases

select *
from database_list

if (select count(*) from database_list) > 0

Select @DBNAME =''
while exists (select DATABASE_NAME from database_list where DATABASE_NAME>@DBNAME)

	BEGIN
	select @DBNAME=min(DATABASE_NAME) from database_list where DATABASE_NAME >@DBNAME
	

	delete table_list
	
		select @sql1='insert into table_list
		SELECT name FROM '+@dbname+'.dbo.sysobjects WHERE type = ''U'''
		Print (@sql1)
		exec (@sql1)
		Select @TABLENAME =''
		while exists (select TABLE_NAME from table_list where TABLE_NAME>@TABLENAME)

			BEGIN
			select @TABLENAME=min([TABLE_NAME]) from table_list where TABLE_NAME >@TABLENAME
			
			select @sql='use ' +@dbname
			select @sql=@sql+' exec sp_spaceused ['+@TABLENAME+']'
			
			
			
			INSERT INTO TABLE_INDEX_INFO
			(TABLE_NAME,[ROWS],RESERVED,[DATA],INDEX_SIZE,UNUSED)
			EXEC (@sql)

			select @sql='use ' +@dbname
			
			select @sql=@sql+' exec SP_HELPINDEX ['+@TABLENAME+']'
			
			
			INSERT INTO index_count
			EXEC (@sql)
			UPDATE TABLE_INDEX_INFO
			set index_count =(select Count(*) from index_count) where index_count  is null
			delete index_count
			END
		
		UPDATE TABLE_INDEX_INFO
		set database_name=@DBNAME where database_name  is null
		
	end

	select *
from 		TABLE_INDEX_INFO
order by rows desc
			


	

Open in new window

0
 
wittysloganCommented:
your right I don't think this script works properly on 2005 boxes or at least on the one it's just run on.  Sorry
0
 
wittysloganCommented:
although it does work on another 2005 box.  not sure why.
0
 
anushahannaAuthor Commented:
no problem..it must be the schema..
0
 
anushahannaAuthor Commented:
Thanks for the idea, wittyslogan. I made it work with the schema and collation limitations. Now i believe it should work on any database.

DECLARE @DBNAME VARCHAR (1000)
DECLARE @TABLENAME VARCHAR (1000)
DECLARE @CMD VARCHAR (1000)
declare @sql VARCHAR (1000)
declare @sql1 VARCHAR (1000)
CREATE TABLE index_count
(INDEX_NAME VARCHAR(1000),INDEX_DESCRIPTION varchar (1000),INDEX_KEYS VARCHAR(1000))
CREATE TABLE TABLE_INDEX_INFO
(DATABASE_NAME VARCHAR (1000),TABLE_NAME VARCHAR(1000),[ROWS] INT ,RESERVED VARCHAR(1000),[DATA] VARCHAR(1000),INDEX_SIZE VARCHAR(1000),UNUSED VARCHAR (1000),index_count INT)

Create table table_list ([TABLE_NAME] varchar (1000))

Create table database_list (DATABASE_NAME varchar(1000),DATABASE_SIZE int,remarks varchar (1000))
insert into database_list
exec sp_databases
delete from database_list where DATABASE_NAME = 'tempdb'

select DATABASE_NAME,DATABASE_SIZE
from database_list

if (select count(*) from database_list) > 0

Select @DBNAME =''
while exists (select DATABASE_NAME from database_list where DATABASE_NAME>@DBNAME)

	BEGIN
	select @DBNAME=min(DATABASE_NAME) from database_list where DATABASE_NAME >@DBNAME
 
	delete table_list
	
		select @sql1='insert into table_list
		SELECT TABLE_SCHEMA+''.''+name FROM '+@dbname+'.dbo.sysobjects,'+@dbname+'.INFORMATION_SCHEMA.COLUMNS WHERE name COLLATE DATABASE_DEFAULT =TABLE_NAME COLLATE DATABASE_DEFAULT and type = ''U'''
		Print (@sql1)
		exec (@sql1)
		Select @TABLENAME =''
		while exists (select TABLE_NAME from table_list where TABLE_NAME>@TABLENAME)

			BEGIN
			select @TABLENAME=min([TABLE_NAME]) from table_list where TABLE_NAME >@TABLENAME
			
			select @sql='use ' +@dbname
			select @sql=@sql+' exec sp_spaceused ['+@TABLENAME+']'
			
			
			
			INSERT INTO TABLE_INDEX_INFO
			(TABLE_NAME,[ROWS],RESERVED,[DATA],INDEX_SIZE,UNUSED)
			EXEC (@sql)

			select @sql='use ' +@dbname
			
			select @sql=@sql+' exec sp_helpindex ['+@TABLENAME+']'
			
			
			INSERT INTO index_count
			EXEC (@sql)
			UPDATE TABLE_INDEX_INFO
			set index_count =(select Count(*) from index_count) where index_count  is null
			delete index_count
			END
		
		UPDATE TABLE_INDEX_INFO
		set DATABASE_NAME=@DBNAME where DATABASE_NAME  is null

	end

	select * from TABLE_INDEX_INFO order by [ROWS] desc
go
drop table index_count
drop table TABLE_INDEX_INFO
drop table table_list
drop table database_list			

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.