SQL Server Performance

Can I Use sp_spaceused to Return Number of Records into a Variable?

Discussion in 'SQL Server 2005 General DBA Questions' started by jbates99, Aug 17, 2010.

  1. jbates99 Member

    hi all,

    My question is for 2005.

    I have a table that contains information about some tables: table name, number of rows, index name and fragmentation percent.

    Now I need to update the "number of rows" value.

    EXEC sp_spaceused @name;

    sp_spaceused returns the number of records along with some other columns.

    **How can I use sp_spaceused to return a value for rows which I can store into a variable for use in updating my table? **

    This needs to be dynamic so I can loop thru several table names, retrieivng the row count for each table...

    Thanks, John
  2. FrankKalis Moderator

    I've wrote something about this a couple of years ago: http://www.insidesql.org/faq/sp_spaceused-in-tabelle-unleiten. It's almost "code-only" so easy to understand even for non-German speakers.
    However, since then, things have evolved and nowadays I use something like the following script to get the rowcount in my tables.
    SELECT
    S.name, P.NumberOfRows, S2.name
    FROM
    sys.sysobjects S
    JOIN
    sys.schemas S2 ON S.uid = S2.schema_id
    JOIN
    (SELECT
    P.[object_id], P.rows AS NumberOfRows, O.schema_id
    FROM
    sys.partitions P
    JOIN
    sys.objects O ON P.[object_id] = O.[object_id]
    WHERE
    P.index_id = 1) P ON S2.schema_id = P.schema_id AND S.id = P.object_id
    WHERE
    S.xtype = 'U'
    For getting the fragment numbers, there is a dedicated dynamic maangement function for it that provides you with all information you need:
    SELECT
    DB_NAME(IXStats.database_id) AS DatabaseName,
    OBJECT_NAME (IXStats.[object_id]) AS TabellenName,
    SIX.[Name] AS IndexName,
    IXStats.avg_fragmentation_in_percent,
    IXStats.index_type_desc,
    IXStats.record_count
    ,IXStats.index_level
    FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') IXStats
    JOIN
    sys.indexes SIX ON IXStats.[object_id] = SIX.[object_id] AND IXStats.index_id = SIX.index_id
    WHERE
    IXStats.database_id = DB_ID()
    ORDER BY
    2
    Be aware though, that this may run for quite some time in 'DETAILED' mode on a larger database. If you are only interested in the fragmentation and not the number of rows, just switch 'DETAILED' to NULL and it should run very quick.
  3. satya Moderator

    Cool tip Frank, the second one extends further information ...[:)]

Share This Page