would like to check 100 tables for a largest in size

How can I generate a querry which will check all tables in a db and find me the largest in megabytes and if it is possible show a list in order from largest to smallest
LVL 7
TransBindAsked:
Who is Participating?
 
bwdowhanCommented:
I apologize, I re-read your question and see that you want table space being used by each table... I made some modifications and used the sp_spaceused function to build a table that has info for each table in your database. It then sorts by reserved space (total space reserved for the table).


SET NOCOUNT ON
GO

DECLARE @V_SQL               NVARCHAR(2000),
        @V_TABLE_NAME        VARCHAR(50),  -- Increase this if your tablenames are bigger than 50
        @V_REC_CNT           INT

DECLARE CUR_LIST_TABLE CURSOR FOR
        SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U'
 
BEGIN
  -- If a table with the same name (Z_TAB_REC_CNT) as the one used to hold the record counts already exist,
   -- then it is dropped.
   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'Z_TAB_REC_CNT' AND TYPE = 'U') BEGIN
      BEGIN TRANSACTION
      EXECUTE SP_EXECUTESQL N'DROP TABLE Z_TAB_REC_CNT'
      COMMIT TRANSACTION
   END

   -- Create a working table Z_TAB_REC_CNT that contains the table names and the record count for each of them.
   BEGIN TRANSACTION
            create table Z_TAB_REC_CNT (
        [name]      varchar(50),
        [rows]      int,
        [reserved]      varchar(20),
        [data]      varchar(20),
        [index_size]      varchar(20),
        [unused]      varchar(20) )
   COMMIT TRANSACTION
   
   -- Populates the temp table with a record for each user table along with its number of records in it.
   OPEN CUR_LIST_TABLE
   FETCH NEXT FROM CUR_LIST_TABLE INTO @V_TABLE_NAME
   WHILE @@FETCH_STATUS = 0 BEGIN
      -- Insert tablename into temp table.
      BEGIN TRANSACTION

      --Use sp_spaceused to get information for each table
      SET @V_SQL = N'INSERT INTO Z_TAB_REC_CNT EXECUTE sp_spaceused ''' + @V_TABLE_NAME + ''''
      EXECUTE SP_EXECUTESQL @V_SQL
      COMMIT TRANSACTION;
     
      FETCH NEXT FROM CUR_LIST_TABLE INTO @V_TABLE_NAME
   END;
   CLOSE CUR_LIST_TABLE
   DEALLOCATE CUR_LIST_TABLE

END;

GO



-- Use this to get your results... The reserved field is the total table space being used
select      *
from Z_TAB_REC_CNT
order by cast(patindex('%[^0-9]%',reserved) as integer) desc


0
 
bwdowhanCommented:
Hi TransBind,

Here is a script that I use to get the same thing you are looking for:

-- Script for record count for each table for MSSQL 7.0 / 2000

SET NOCOUNT ON
GO

DECLARE @V_SQL               NVARCHAR(2000),
        @V_TABLE_NAME        VARCHAR(30),
        @V_REC_CNT           INT

DECLARE CUR_LIST_TABLE CURSOR FOR
        SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U'
 
BEGIN
   -- If a table with the same name (Z_TAB_REC_CNT) as the one used to hold the record counts already exist,
   -- then it is dropped.
   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'Z_TAB_REC_CNT' AND TYPE = 'U') BEGIN
      BEGIN TRANSACTION
      EXECUTE SP_EXECUTESQL N'DROP TABLE Z_TAB_REC_CNT'
      COMMIT TRANSACTION
   END

   -- Create the temporary table Z_TAB_REC_CNT that contains the table names and the record count for each of them.
   BEGIN TRANSACTION
   EXECUTE SP_EXECUTESQL N'CREATE TABLE Z_TAB_REC_CNT (TABLENAME NCHAR(30),RECCOUNT INTEGER NULL)'
   COMMIT TRANSACTION
   

   -- Populates the temp table with a record for each user table along with its number of records in it.
   OPEN CUR_LIST_TABLE
   FETCH NEXT FROM CUR_LIST_TABLE INTO @V_TABLE_NAME
   WHILE @@FETCH_STATUS = 0 BEGIN
      -- Insert tablename into temp table.
      BEGIN TRANSACTION
      SET @V_SQL = N'INSERT INTO Z_TAB_REC_CNT (TABLENAME) VALUES (''' + @V_TABLE_NAME + ''')'
      EXECUTE SP_EXECUTESQL @V_SQL
      COMMIT TRANSACTION;
     
      -- Updates temp table with record count.
      BEGIN TRANSACTION
      SET @V_SQL = N'UPDATE Z_TAB_REC_CNT SET RECCOUNT = (SELECT COUNT(*) FROM ' + @V_TABLE_NAME + ')'
      SET @V_SQL = @V_SQL + N'WHERE TABLENAME = ''' + @V_TABLE_NAME + ''''
      EXECUTE SP_EXECUTESQL @V_SQL
      COMMIT TRANSACTION
     
      FETCH NEXT FROM CUR_LIST_TABLE INTO @V_TABLE_NAME
   END;
   CLOSE CUR_LIST_TABLE
   DEALLOCATE CUR_LIST_TABLE
END;

GO

SELECT TABLENAME,
       RECCOUNT,
FROM Z_TAB_REC_CNT
ORDER BY RECCOUNT DESC
GO
0
 
TransBindAuthor Commented:
interesting approach ... however your scripts produces total row count for every user table in a database ... perhaps i wasn't clear ... but i am interested in a size of table in a database ...

for example if u do sp_spaceused tableNAme that will give u number of rows and space occupied on a database  etc ... I would like to see a report which will show this

[table Name]  [reserved space]  
table 1             32 KB
table 2             12kb
table 3             10kb

row count does not indicate that a table reserves more or less space ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp
create Table #temp (name varchar(255), Rows int, Reserved varchar(10), Data varchar(10), Index_size varchar(10),unused varchar(10) )
insert into #temp
exec sp_MSForEachTable "Sp_SpaceUsed '?' "

SELECT name, rows FROM #temp ORDER BY ROWS DESC
0
 
TransBindAuthor Commented:
i could do this as well

select 'exec sp_spaceused ' + sysobjects.name from sysobjects where sysobjects.xtype = 'u'

and paste that in QA and compare and but thats not an idea solution or report
0
 
bwdowhanCommented:
I like the approach aneeshattingal. I used to use sp_MSForEachTable but it seems to have so much overhead that it took more than a minute to return what the cursor does in a second. **Much cleaner though***

You could use that and just change the select statement at the bottom to get what you want:

SELECT name, reserved, rows
FROM #temp
order by cast(patindex('%[^0-9]%',reserved) as integer) desc
0
 
bwdowhanCommented:
The solution actually builds a table with the results from sp_spaceused. It doesn't just generate select statements.
0
 
TransBindAuthor Commented:
i also like his approach ..
this syntax i don't understand though

exec sp_MSForEachTable "Sp_SpaceUsed '?' "

???
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sp_MSForEachTable  is an undocumented sp which loops thru all the tables and the '?' serves as a place holder for the table name.

Similarto this

sp_MSForEachDB whic loops thru all the databases
0
 
TransBindAuthor Commented:
very cool ... thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.