Solved

Generate list of database tables and size

Posted on 2007-11-20
5
1,358 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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