[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

would like to check 100 tables for a largest in size

Posted on 2006-05-18
10
Medium Priority
?
422 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:TransBind
  • 4
  • 4
  • 2
10 Comments
 
LVL 5

Expert Comment

by:bwdowhan
ID: 16714471
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
 
LVL 7

Author Comment

by:TransBind
ID: 16714638
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
 
LVL 5

Accepted Solution

by:
bwdowhan earned 1600 total points
ID: 16714640
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 16714644
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
 
LVL 7

Author Comment

by:TransBind
ID: 16714659
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
 
LVL 5

Expert Comment

by:bwdowhan
ID: 16714668
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
 
LVL 5

Expert Comment

by:bwdowhan
ID: 16714683
The solution actually builds a table with the results from sp_spaceused. It doesn't just generate select statements.
0
 
LVL 7

Author Comment

by:TransBind
ID: 16714743
i also like his approach ..
this syntax i don't understand though

exec sp_MSForEachTable "Sp_SpaceUsed '?' "

???
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16714767
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
 
LVL 7

Author Comment

by:TransBind
ID: 16714798
very cool ... thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

873 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