Solved

Generate list of database tables and size

Posted on 2007-11-20
5
1,333 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
ID: 20324314
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
ID: 20325141
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20325284
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
ID: 20327516
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
ID: 20330191
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

896 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

17 Experts available now in Live!

Get 1:1 Help Now