Solved

find the table size using sp_spaceused

Posted on 2011-09-14
6
473 Views
Last Modified: 2012-05-12
Hi,

We have prod server (X1) and this server contains around 250 DB's. and in all DB's there is one common table called 'MyTable' (but number of rows differ from DB to DB). I want to find out table size,rowcount of this table using sp_spaceused command. It is difficult to run manually each and evry database because it was taking long time.

I have created script (PFA) for this requiremnt, but script returns results for single database in server, but i need results of all databases in server (250 DB's). Please let me know how to modify attached script to execute against all databases in server.

The table is exist in all 250 DB's.
3.txt
0
Comment
Question by:sg05121983
[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
  • 3
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
venk_r earned 250 total points
ID: 36537502
Try the below script
and If you want to ignore any database other than system database please include it in the where clause



CREATE TABLE #TableInfo (Name sysname NULL,
                         Rows int,
                         Reserved varchar(256) NULL,
                         Data varchar(256) NULL,
                         Index_Size varchar(256) NULL,
                         Unused varchar(256) NULL)
CREATE TABLE #DBTables (Instance sysname NULL,
                        DBName sysname NULL,
                        TableName sysname NULL,
                        --TableType char(2),
                        TableRows int NULL,
                        --IndexCount int NULL,
                        ReservedKB int NULL,
                        DataSizeKB int NULL,
                        IndexSizeKB int NULL,
                        UnusedKB int NULL)

DECLARE @DatabaseName varchar(64)
DECLARE @TableName varchar(256)
DECLARE @xtype char(2)
DECLARE @TableRows int
DECLARE @IndexCount int
DECLARE @ReservedKB int
DECLARE @DataSizeKB int
DECLARE @IndexSizeKB int
DECLARE @UnusedKB int
DECLARE @Table varchar(50)

DECLARE @DB varchar(50)
DECLARE @ExecSQL VARCHAR(2000)
SET @Table='Mytable'
DECLARE cs CURSOR FOR
  SELECT     NAME FROM MASTER..SYSDATABASES WHERE DBID>5 --Ignore system database

OPEN cs
FETCH NEXT FROM cs INTO @DB
WHILE (@@FETCH_STATUS = 0)
BEGIN
  TRUNCATE TABLE #TableInfo
  SET @ExecSQL = 'INSERT #TableInfo EXEC ' + @DB +'.dbo.sp_spaceused ' + @Table + ',@updateusage = ''TRUE'''
  PRINT @ExecSQL
 -- EXEC @ExecSQL



  SELECT
    @TableRows = Rows,
    @ReservedKB = CAST(SUBSTRING(Reserved, 1, CHARINDEX('KB', Reserved, 1)-1) AS int),
    @DataSizeKB = CAST(SUBSTRING(Data, 1, CHARINDEX('KB', Data, 1)-1) AS int),
    @IndexSizeKB = CAST(SUBSTRING(Index_Size, 1, CHARINDEX('KB', Index_Size, 1)-1) AS int),
    @UnusedKB = CAST(SUBSTRING(Unused, 1, CHARINDEX('KB', Unused, 1)-1) AS int)
  FROM #TableInfo


  INSERT INTO #DBTables
  VALUES (@@SERVERNAME,
          @DB,
          @Table,
          --@xtype,
          @TableRows,
          --@IndexCount,
          @ReservedKB,
          @DataSizeKB,
          @IndexSizeKB,
          @UnusedKB)

  FETCH NEXT FROM cs INTO @DB
END
CLOSE cs
DEALLOCATE cs

SELECT
  Instance,
  DBName,
  TableName,
  --TableType,
  TableRows,
  --IndexCount,
  ReservedKB,
  DataSizeKB,
  IndexSizeKB,
  UnusedKB
FROM #DBTables

DROP TABLE #DBTables
DROP TABLE #TableInfo
0
 

Author Comment

by:sg05121983
ID: 36540666
Hi Venk_r,

The Results are showing as NULL(TableRows, ReservedKB,DataSizeKB,IndexSizeKB,UnusedKB).

FYI...
In mytable (for X34 DB) : the number of rows are 111111
In mytable (for X35 DB) the number of rows are 222222.

But script is displaying results as NULL. Please review the script once again.

Thanks for your help.


0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 36541959
CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO


open management studio and type EXEC GetAllTableSizes in new query windows
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:sg05121983
ID: 36542034
Mr. Sqlserver,

Have you understood, my reuirment?
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36542629
Sorry I commented out certain statements for testing.
Here you go
CREATE TABLE #TableInfo (Name sysname NULL,
                         Rows int,
                         Reserved varchar(256) NULL,
                         Data varchar(256) NULL,
                         Index_Size varchar(256) NULL,
                         Unused varchar(256) NULL)
CREATE TABLE #DBTables (Instance sysname NULL,
                        DBName sysname NULL,
                        TableName sysname NULL,
                        --TableType char(2),
                        TableRows int NULL,
                        --IndexCount int NULL,
                        ReservedKB int NULL,
                        DataSizeKB int NULL,
                        IndexSizeKB int NULL,
                        UnusedKB int NULL)

DECLARE @DatabaseName varchar(64)
DECLARE @TableName varchar(256)
DECLARE @xtype char(2)
DECLARE @TableRows int
DECLARE @IndexCount int
DECLARE @ReservedKB int
DECLARE @DataSizeKB int
DECLARE @IndexSizeKB int
DECLARE @UnusedKB int
DECLARE @Table varchar(50)

DECLARE @DB varchar(50)
DECLARE @ExecSQL VARCHAR(4000)
SET @Table='MyTable'
DECLARE cs CURSOR FOR
  SELECT     NAME FROM MASTER..SYSDATABASES WHERE DBID>5 --If you want to ignore any database other than system database please include it in the where clause

OPEN cs
FETCH NEXT FROM cs INTO @DB
WHILE (@@FETCH_STATUS = 0)
BEGIN
  TRUNCATE TABLE #TableInfo
  SET @ExecSQL = 'INSERT into tempdb..#TableInfo EXEC ' + @DB +'.dbo.sp_spaceused ' + @Table + ',@updateusage = ''TRUE'''
 select  @ExecSQL
  EXECute( @ExecSQL)



  SELECT
    @TableRows = Rows,
    @ReservedKB = CAST(SUBSTRING(Reserved, 1, CHARINDEX('KB', Reserved, 1)-1) AS int),
    @DataSizeKB = CAST(SUBSTRING(Data, 1, CHARINDEX('KB', Data, 1)-1) AS int),
    @IndexSizeKB = CAST(SUBSTRING(Index_Size, 1, CHARINDEX('KB', Index_Size, 1)-1) AS int),
    @UnusedKB = CAST(SUBSTRING(Unused, 1, CHARINDEX('KB', Unused, 1)-1) AS int)
  FROM #TableInfo


  INSERT INTO #DBTables
  VALUES (@@SERVERNAME,
          @DB,
          @Table,
          --@xtype,
          @TableRows,
          --@IndexCount,
          @ReservedKB,
          @DataSizeKB,
          @IndexSizeKB,
          @UnusedKB)

  FETCH NEXT FROM cs INTO @DB
END
CLOSE cs
DEALLOCATE cs
SELECT
  Instance,
  DBName,
  TableName,
  --TableType,
  TableRows,
  --IndexCount,
  ReservedKB,
  DataSizeKB,
  IndexSizeKB,
  UnusedKB
FROM #DBTables

DROP TABLE #DBTables
DROP TABLE #TableInfo

0
 

Author Closing Comment

by:sg05121983
ID: 36547178
--
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 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