using stored proc sp_spaceused with variables

Question

Post reply

using stored proc sp_spaceused with variables

Chris Golla

Ten Centuries

Points: 1171
More actions
March 13, 2006 at 10:24 am

#112034

Hi,
I am trying to create a script that will loop through the tables and return space used info for each table. Some tables are not owned by dbo so I need to use the owner.table syntax. The problem is I can't use that syntax without quotes, but it won't translate the variable with them on. Thanks in advance for your time and assistance.

DECLARE @table varchar(64),
@sql varchar(150),
@owner varchar(20)
DECLARE curTable SCROLL CURSOR FOR SELECT name FROM sysobjects WHERE Type = 'U' And Name <> 'dtproperties' ORDER BY name
OPEN curTable
FETCH FIRST FROM curTable INTO @table
WHILE (@@FETCH_STATUS = 0)
BEGIN
select @owner=user_name(uid) from sysobjects where name = @table
exec sp_spaceused '@owner.@table'
FETCH NEXT FROM curTable INTO @table
END
CLOSE curTable
DEALLOCATE curTable

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 1

Declare a temp var and set it to owner.table name

DECLARE @table varchar(64),

@sql varchar(150),

@owner varchar(20),

@temp varchar(90)

DECLARE curTable SCROLL CURSOR FOR SELECT name FROM sysobjects WHERE Type = 'U' And Name <> 'dtproperties' ORDER BY name

OPEN curTable

FETCH FIRST FROM curTable INTO @table

WHILE (@@FETCH_STATUS = 0)

BEGIN

select @owner=user_name(uid) from sysobjects where name = @table

set @temp = @owner +'.'+@table

exec sp_spaceused @temp

FETCH NEXT FROM curTable INTO @table

END

CLOSE curTable

DEALLOCATE curTable

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 2

I rolled the following procedure a few years back for the same thing. I place it in the master database and use the sp_prefix so I can call it from any database context.

The procedure includes switches to update statistics so the results are accurate. The first time you run it, pass 1 for both parameters. Depending on the size of your database, updating those statistics may take some time. If you trust the statistics (sp_spaceused uses statistics, not actual table data), then you can pass 0 for both parameters and it will run quickly.

USE MASTER
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_spaceused_Report]') 
              AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROC dbo.sp_spaceused_Report 
GO

CREATE PROC dbo.sp_spaceused_Report(
@UpdateUsageFirst smallint = 0,
@CountRowsFirst   smallint = 0
)
AS
-- egw 4.3.03 looking for a better way to cook up a table size report
-- Copyright ©2003 Eddie Wuerch/Mirai-tech, Inc.

SET NOCOUNT ON
SET STATISTICS IO OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @sCmd  nvarchar(500)
DECLARE @curTable  int, @LastTable int
DECLARE @tbls TABLE (rownum int IDENTITY(1,1) NOT NULL, TableName nvarchar(255) NOT NULL, 
                     OwnerName nvarchar(255) NOT NULL )

  IF IsNull(@UpdateUsageFirst, 0) = 1
    BEGIN
      PRINT 'Performing verification and updates to the sysindexes system table first.'
      IF IsNull(@CountRowsFirst, 0) = 1
        BEGIN
          PRINT 'Also updating rowcounts in sysindexes.'
          DBCC UPDATEUSAGE (0) WITH COUNT_ROWS
        END
      ELSE
        DBCC UPDATEUSAGE (0)
    END

  CREATE TABLE #Res (objname varchar(255), Rows varchar(255), Reserved varchar(255), 
                     Data varchar(255), Indexsize varchar(255), Unused varchar(255))
  INSERT @tbls (TableName, OwnerName)
  SELECT object_name(o.id), u.[name] 
    FROM sysobjects o INNER JOIN
         sysusers u ON u.uid = o.uid
   WHERE o.type='U'
  SET @LastTable = @@ROWCOUNT

  SET @curTable = 0
  WHILE @curTable < @LastTable
    BEGIN
      SET @curTable = @curTable + 1
      SELECT @sCmd = N'EXEC sp_spaceused ''' + LTRIM(RTRIM(OwnerName)) + N'.' + TableName + N'''' 
        FROM @tbls WHERE rownum = @curTable
      INSERT #Res 
      EXEC sp_executesql @sCmd
    END

  SELECT [TableName] = a.objname, [Rows] = CONVERT(int, a.Rows), 
         [Reserved] = CONVERT(int, replace(a.Reserved,'KB','')),
         [Data] = CONVERT(int, replace(a.Data,'KB','')), 
         [IndexSize] = CONVERT(int, replace(a.IndexSize,'KB','')),
         [Unused] = CONVERT(int, replace(a.Unused,'KB',''))
    FROM #Res a
   ORDER BY a.Data DESC



  DROP TABLE #Res

RETURN
GO

-Eddie

Eddie Wuerch
MCM: SQL

Chris Golla Ten Centuries Points: 1171 More actions · Answer 3

Chris Golla

Ten Centuries

Points: 1171

March 15, 2006 at 6:55 am

#626744

I got it working, thanks for your assistance!!