This is a very common problem to get space used by the Database objects.
This stored procedure uses the sp_spaceused.
This stored procedure has been tested and used on a SQL Server 2005 and it will work fine on SQL Server 2008 as well.
It’s a very much simple stored procedure.
This SP declare a cursor that will get the names of all user defined tables and Schemas (concatenating the schema and table names as two part name that is schema.tablename) in the current database.
Then Stored Procedure creates a temporary table to store the individual data elements for each table. Then loop through the created cursor and save the results of the sp_spaceused command to temporary table.
The last step includes closing and deallocating the cursor, selecting all rows from temp table and dropps that table.
This stored procedure uses the sp_spaceused.
This stored procedure has been tested and used on a SQL Server 2005 and it will work fine on SQL Server 2008 as well.
It’s a very much simple stored procedure.
This SP declare a cursor that will get the names of all user defined tables and Schemas (concatenating the schema and table names as two part name that is schema.tablename) in the current database.
Then Stored Procedure creates a temporary table to store the individual data elements for each table. Then loop through the created cursor and save the results of the sp_spaceused command to temporary table.
The last step includes closing and deallocating the cursor, selecting all rows from temp table and dropps that table.
-- =============================================
CREATE PROCEDURE utility.Proc_GetDBTableSizes
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TableName VARCHAR(200)
-- Insert statements for procedure here
DECLARE tableCursor CURSOR FOR
SELECT sys.schemas.[name]+'.'+sys.objects.[name]
FROM sys.schemas INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id
WHERE type='U' AND is_ms_shipped=0 ORDER BY sys.schemas.[name] -- WHERE is_ms_shipped is Microsoft generated objects
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(200),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first Record from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Insert the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next Record
FETCH NEXT FROM tableCursor INTO @TableName
END
--Close/Deallocate the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT *
FROM #TempTable
DROP TABLE #TempTable
END
GO
