[thelist] TIP: Finding Table Space Info within MS SQL....

Anthony Baratta anthony at baratta.com
Mon Apr 30 17:59:05 CDT 2007


<tip author="Anthony Baratta" type="MS SQL: Finding Table Space Info">

So - on my quest to drill deeper into MS SQL I found an undocumented stored procedure that allows you to iterate through a database's set of tables. The funny thing about this undocumented SP is that because of the internet, it's now fairly well documented in how to use it, even if MS does not official support it's use.

The SP in question is called "sp_msforeachtable" and lives in the Master Database System Stored Procedures. You switch over to the DB you want to iterate through and give the SP the command that you want to run on each table. (Very handy for blowing away all the tables in a DB with one call.)

The challenge was crafting an SQL statement that was DB agnostic and replacing the DB Name info on the fly.

Anywho - In order to beef up my quick and dirty dotNEt that I built around the SP from my last tip, I wanted the table sizes of the individual tables in a target database. So I needed an SP that wrapped the sp_msforeachtable and the sizing SP you might be already familiar with "sp_spaceused".

So I came up with this:

CREATE PROCEDURE [dbo].[sp_GetTableSpaceInfo]
	  @DBName as varchar(200)
	, @Format as varchar(5) = Null
AS
BEGIN
	Create Table #TableInfo (
		  table_name sysname ,
		  row_count int,
		  reserved_size varchar(50),
		  data_size varchar(50),
		  index_size varchar(50),
		  unused_size varchar(50))

	DECLARE @SQL as nvarchar(1000)
	SET NOCOUNT ON
	SET @SQL = 'insert #TableInfo exec [' + @DBName + ']..sp_msforeachtable ''sp_spaceused ''''?'''' '' '
	exec sp_executesql @SQL

	if @Format = 'XML'
		select 
			  TableSpaceInfo.table_name as tableName
			, TableSpaceInfo.row_count as tableRows
			, CAST(CAST(Replace(TableSpaceInfo.data_size, ' KB', '') as decimal(20,3)) / 1024 as decimal(20,3)) as tableSizeMb
			, CAST(CAST(Replace(TableSpaceInfo.index_size, ' KB', '') as decimal(20,3)) / 1024 as decimal(20,3)) as indexSizeMb
		from #TableInfo as TableSpaceInfo
		Order by CAST(Replace(TableSpaceInfo.data_size, ' KB', '') as integer) desc
		FOR XML AUTO, ELEMENTS		
	else
		select 
			  TableSpaceInfo.table_name as tableName
			, TableSpaceInfo.row_count as tableRows
			, CAST(CAST(Replace(TableSpaceInfo.data_size, ' KB', '') as decimal(20,3)) / 1024 as decimal(20,3)) as tableSizeMb
			, CAST(CAST(Replace(TableSpaceInfo.index_size, ' KB', '') as decimal(20,3)) / 1024 as decimal(20,3)) as indexSizeMb
		from #TableInfo TableSpaceInfo
		Order by CAST(Replace(TableSpaceInfo.data_size, ' KB', '') as integer) desc

		drop table #TableInfo
END

I placed this new SP in the Master DB Stored Procedures area and call it on demand via my dotNet web application. Make sure the user connecting to the SQL Instance holding your DBs has read access to all the target DBs. We use an impersonation routine to switch the user context to a higher level privileged user for the scan and switch back to the fred bloggs user when done.

</tip>



More information about the thelist mailing list