Solved

Generate list of database tables and size

Posted on 2007-11-20
5
1,322 Views
Last Modified: 2008-02-01
Hello,

I have a SQL Server 2000 DBMS with 70+ databases that I need to manage.  I am having trouble figuring out a way to generate a list of tables within each db along with the size they are taking up on the server.  I looked around the internet and found several references to 'sp_spaceused' and 'sp_MSforeachtable', but I'm not quite sure how I could implement either to generate one long list of all the tables in all my databases.  Do I have any options?

Thanks!
0
Comment
Question by:sviriyal
5 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 25 total points
Comment Utility
Here's some code I use.  It will also give you rowcount and the number of columns in each table.
Declare @include_system_tables bit

Set @include_system_tables = 0
 

SELECT Table_Name

, (SELECT rowcnt FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.Table_Name)) AS Row_Count

, Total_Space_Used_MB 

Into #T1

FROM (SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name

, CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) 

* (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024)/1024)) AS Total_Space_Used_MB 

   FROM sysindexes i (NOLOCK)

   INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id 

   AND ((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') 

   AND (@include_system_tables = 1 OR OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)

   WHERE indid IN (0, 1, 255) --AND Total_Space_Used_MB = 0

   GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))) as a

--ORDER BY Total_Space_Used_MB DESC

ORDER BY Row_Count DESC
 

-- Get the column counts

Select myInner.Table_Name, Count(sc.id) As Number_Columns

Into #T2

From dbo.syscolumns sc

Inner Join 

(SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name, i.id

 FROM sysindexes i (NOLOCK)

 INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((o.type IN ('U', 'S')) OR o.type = 'U') 

 AND ((OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))

 WHERE indid IN (0, 1, 255)) myInner On sc.id = myInner.id

Group By myInner.Table_Name
 

Select #T1.*, #T2.Number_Columns

From #T1

Inner Join #T2 On #T1.Table_Name = #T2.Table_Name
 

Drop Table #T1

Drop Table #T2

Open in new window

0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
Comment Utility
0
 
LVL 17

Expert Comment

by:Chris Mangus
Comment Utility
User needs a list of tables and the physical space each table is using.  The link doesn't give that.
0
 
LVL 7

Accepted Solution

by:
wittyslogan earned 100 total points
Comment Utility
this will give info on indexes and how much space they use as well:


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
                  


      



0
 

Author Comment

by:sviriyal
Comment Utility
Thank you all for your great responses!  Wittyslogan's post brought me to the right answer although cmangus helped quite a bit as well.  Thanks to you both.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now