Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1373
  • Last Modified:

Generate list of database tables and size

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
sviriyal
Asked:
sviriyal
2 Solutions
 
Chris MangusDatabase AdministratorCommented:
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
 
Chris MangusDatabase AdministratorCommented:
User needs a list of tables and the physical space each table is using.  The link doesn't give that.
0
 
wittysloganCommented:
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
 
sviriyalAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now