CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Lews Therin
Lews Therin

Reputation: 3777

How can I use sp_spaceused on specific tables?

I'm trying to call sp_spaceused to get the number of rows in a few specific tables that I want to monitor, but I'm trying to avoid using a cursor.

I've made a table to hold all the tables I want to monitor:

CREATE TABLE MonitoredTable 
(
    MonitoredTableID INT PRIMARY KEY IDENTITY(1, 1) NOT NULL
  , DatabaseName NVARCHAR(128) NOT NULL
  , SchemaName NVARCHAR(128) NOT NULL
  , TableName NVARCHAR(128) NOT NULL
  , RowNumberThreshold INT NOT NULL
  , IsActive BIT NOT NULL
)

My problem is: I want to create a function that will only return MonitoredTableIDs for tables that their row counts exceed the defined RowNumberThreshold.

Here's what I'd like to do, but this is invalid SQL: 

CREATE FUNCTION dbo.GetTablesLargerThanThreshold()
RETURNS @tablesLargerThanThreshold TABLE
(
    MonitoredTableID INT NOT NULL
)
AS
BEGIN
    INSERT INTO @tablesLargerThanThreshold
    SELECT MonitoredTableID
    FROM MonitoredTable
    WHERE IsActive = 1
      AND (SELECT [rows] FROM (EXEC sp_spaceused DatabaseName + '.' + SchemaName + '.' + TableName)) > RowNumberThreshold

    RETURN
END

Is there a way I can check if the number of rows in a MonitoredTable are greater than the defined RowNumberThreshold without resorting to a cursor?

Upvotes: 2

Views: 1062

Answers (1)

Neo
Neo

Reputation: 3399

Something like this?

-- See the following pages for documentation on the tables used in this query:
--
-- sys.indexes              https://msdn.microsoft.com/en-us/library/ms173760.aspx
-- sys.partitions           https://msdn.microsoft.com/en-us/library/ms175012.aspx
-- sys.allocation_units     https://msdn.microsoft.com/en-us/library/ms189792.aspx
-- sys.tables               Only columns inherited from sys.object, see link below
-- sys.object               https://msdn.microsoft.com/en-us/library/ms190324.aspx
SELECT OBJECT_NAME(i.OBJECT_ID) AS [TableName]
     , p.[rows] AS [Num_Rows]
FROM sys.indexes AS i
    INNER JOIN sys.partitions AS p 
        ON p.OBJECT_ID = i.OBJECT_ID 
       AND p.index_id = i.index_id
    INNER JOIN sys.allocation_units AS a 
        ON a.container_id = p.partition_id
    INNER JOIN sys.tables AS t 
        ON i.OBJECT_ID = t.OBJECT_ID
WHERE i.type <= 1 -- Heap or clustered index
  AND a.type = 1 -- In-row data
  AND t.type = 'U' -- User-defined table
  AND t.is_ms_shipped = 0 -- sys.object was not created by an internal SQL Server component

Upvotes: 1

Related Questions