TFS, Visual Studio, SQL Server, BizTalk, SharePoint, .Net, and more ...
Here is one way I have found in order to get all tables space used. Effectively it queries the sp_tables and sp_spacedused in a loop to return the data.
Declare
@Table_Name varchar(255),
@SQL varchar(max)
---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables
---------------------------------------------------------------------------
Create
Table #tServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)
CREATE
TABLE #tSpaceUsed
(
[name] varchar(255),
rows varchar(255),
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255)
)
---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables command
-- NOTE: the paramater passed to sp_spaceused MUST be the name of a
-- valid table
---------------------------------------------------------------------------
Insert
Into #tServerTables exec sp_tables
---------------------------------------------------------------------------
-- Create cursor for selecting the Table names
---------------------------------------------------------------------------
Declare
crsServerTables Cursor For
Select table_name
From #tServerTables
WHERE TABLE_TYPE='TABLE'
-- Open the cursor defined above
Open
crsServerTables
Fetch
Next From crsServerTables Into @Table_Name
While
@@Fetch_Status = 0 Begin -- 0 = more records to process
INSERT INTO #tSpaceUsed exec sp_spaceused @Table_Name
-- Move to the next record
Fetch Next From crsServerTables Into @Table_Name
End
SELECT
* FROM #tSpaceUsed
---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------
drop
table #tServerTables
drop table #tSpaceUsed
Close crsServerTables
Deallocate crsServerTables
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
