Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Generate list of database tables and size

Posted on 2007-11-20
5
Medium Priority
?
1,369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 100 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 400 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

660 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