sp_spaceused2
An update to sp_spaceused2. This update fixes some problems related to dbs that are non-accessible such being offline. This stored proc can be run from any database when compiled in master and can report information on all databases at once. Get information on all dbs, one db, one db and all its tables or one db and one table. Use sp_spaceused2 'all' to get all dbs. Use sp_spaceused2 '?' to get back syntax information, a list of other databases on the server and a list of tables in the current db. Also, load the results periodically into a table created in msdb. There is a SQL 2K version and a SQL 70 version. Both are included below.
/*
sp_spaceused2 '?'
sp_spaceused2 'all'
sp_spaceused2 null
sp_spaceused2 null, sysobjects
sp_spaceused2 pubs, sysobjects
sp_spaceused2 pubs, 'all'
sp_spaceused2 pubs, 'user'
sp_spaceused2 pubs
sp_spaceused2 pubs, null
sp_spaceused2 null, 'all'
*/
-- For SQL 2000
if exists (select *
from dbo.sysobjects
where id = object_id('dbo.sp_spaceused2')
and sysstat & 0xf = 4)
drop procedure dbo.sp_spaceused2
Go
Create procedure dbo.sp_spaceused2
@vcDBName sysname = null,
@vcTable sysname = null,
@cUpdateUsage char(1) = '0',
@cUpdateRowCnt char(1) = '0',
@tySaveDbInfo tinyint = 0
as
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/--Name : sp_spaceused2 for SQL 2K
--
--Description : Similiar to sp_spaceused but returns more information. Use
-- sp_spaceused2 '?' to return the syntax and a list of dbs.
--
--Parameters : @vcDBName - Specified database or 'all' or null or '?'.
-- @vcTable - Specified table in current (null) or specified
-- database or 'all'.
-- @cUpdateUsage - 0 or 1; 1 to run Update Usage.
-- @cUpdateRowCnt - 0 or 1; 1 to include 'Count Rows' in Update
-- Usage.
-- @tySaveDbInfo - 0 or 1; 1 stores info in msdb table.
--
--Comments : The data returned is in bytes. The old sp_spacedused
-- returns data in computer KB (data*1024 = bytes).
--
--Date : 06/06/2001
--Author : Clinton Herring
--
--History : 06/27/2001 WCH Added @tySaveDbInfo and msdb.dbo.DbSizeInfo
-- in order to save data permanently.
--
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
Set nocount on
-- If temp table exists drop them before attempting to create them.
If (Select object_id('tempdb.dbo.#dbData')) > 0
Exec ('Drop table #dbData')
If (Select object_id('tempdb.dbo.#TableData')) > 0
Exec ('Drop table #TableData')
If (Select object_id('tempdb.dbo.#logspaceinfo')) > 0
Exec ('Drop table #logspaceinfo')
If (Select object_id('tempdb.dbo.#names')) > 0
Exec ('Drop table #names')
-- Create some holding tables for results.
Create table #dbData(
name sysname null,
db_tbl_cnt int null,
row_cnt int null,
db_file_size decimal(13) null,
db_space_free decimal(13) null,
total_reserved decimal(13) null,
data decimal(13) null,
indexes decimal(13) null,
unused decimal(13) null,
log_file_size decimal(13) null,
usable_log_space decimal(13) null,
log_space_free dec(10,6) null,
sort_order tinyint
)
-- Create some holding tables for results.
Create table #TableData(
name sysname null,
row_count bigint null,
reserved decimal(13) null,
data decimal(13) null,
indexes decimal(13) null,
unused decimal(13) null,
sort_order tinyint
)
Create table #logspaceinfo(
name sysname null,
lspaceused dec(15,9) null,
lspercent dec(10,6) null,
status int null
)
Create table #names(
name sysname null,
sort_order tinyint null
)
-- Create a holding table in msdb to store db data
If @tySaveDbInfo = 1 and datepart(weekday, GetDate()) = 7
If not exists (select * from msdb.dbo.sysobjects
where name = 'DbSizeInfo' and uid = 1)
Exec('Use msdb ' +
'Create table DbSizeInfo( ' +
'ServerName sysname null, ' +
'DbName sysname null, ' +
'DbFileSize decimal(15) null, ' +
'DbSpaceFree decimal(15) null, ' +
'TotalReserved decimal(15) null, ' +
'Data decimal(15) null, ' +
'Indexes decimal(15) null, ' +
'Unused decimal(15) null, ' +
'LogFileSize decimal(15) null, ' +
'UsableLogSpace decimal(15) null, ' +
'LogSpaceFree dec(10,6) null, ' +
'SortOrder tinyint, ' +
'SaveDate datetime ' +
') ')
-- Declare variables.
declare @iTblCnt int,
@dcRowCnt decimal(13),
@dcDBFileSpace decimal(13),
@dcFreeDBspace decimal(13),
@dcReservedSpace decimal(13),
@dcDataSpace decimal(13),
@dcIndexSpace decimal(13),
@dcUnusedSpace decimal(13),
@dcLogFileSpace decimal(13),
@dcActualLogSpace decimal(13),
@dcPercentFreeLogSpace dec(10,6),
@iTblCnt2 int,
@dcRowCnt2 decimal(13),
@dcDBFileSpace2 decimal(13),
@dcFreeDBspace2 decimal(13),
@dcReservedSpace2 decimal(13),
@dcDataSpace2 decimal(13),
@dcIndexSpace2 decimal(13),
@dcUnusedSpace2 decimal(13),
@dcLogFileSpace2 decimal(13),
@dcActualLogSpace2 decimal(13),
@dcPercentFreeLogSpace2 dec(10,6),
@vcBytesperPage varchar(5),
@vcObjCnt varchar(5),
@tiLoopRtn tinyint,
@vcName sysname,
@vcNote varchar(50),
@vcCmd varchar(4000)
-- Retrieve the bytes per page value.
Select @vcBytesperPage = str(low,5)
from master.dbo.spt_values
where number = 1 and type = 'E'
-- Print help information.
If @vcDBName = '?'
Begin
Print 'Syntax: dbo.sp_spaceused2'
Print ' @vcDBName = <database name|[all]|[?]|null> -- Leave null to get info about the current'
Print ' database or use [all] to get info about'
Print ' all databases. '
Print ' @vcTable = <table name|[all]|[user]|null> -- Any valid table within the specified database.'
Print ' Use [all] to get all tables for the specified database.'
Print ' Use [user] to get just user tables for the specified database.'
Print ' @cUpdateUsage = <0|1> -- Set to 1 to first run Update Usage. If you see'
Print ' negative results run again with this option'
Print ' set to one. The default is 0.'
Print ' @cUpdateRowCnt = <0|1> -- Set to 1 to update row counts in sysindexes.'
Print ' @tySaveDbInfo = <0|1> -- A value of 1 specifies that the results be saved'
Print ' to the table msdb.dbo.DbSizeInfo.'
Print ''
Print 'The current database is ' + db_name() + '.'
Print ''
Select name [Other databases] from master.dbo.sysdatabases order by name
Select name [Tables in the current database] from sysobjects where xtype = 'U' or xtype = 'S'
order by xtype desc, name asc
Return
End
-- If @vcDBName is null grab the current db name
If @vcDBName is null
Begin
Select @vcDBName = db_name()
End
-- Check for a valid database name.
If @vcDBName <> 'all'
Begin
-- Is the databse name valid?
If not exists (select * from master.dbo.sysdatabases where name = @vcDBName and
(status&32)|(status&64)|(status&128)|(status&256)|(status&512)|
(status&1024)|(status&32768)|(status&1073741824) = 0 )
Begin
Print '''' + @vcDBName + ''' is not a valid database on this server or is in ' +
'a non-accessible mode such as offline.'
Print ''
Select name [Valid accessible databases are...] from master.dbo.sysdatabases
where (status&32)|(status&64)|(status&128)|(status&256)|(status&512)|
(status&1024)|(status&32768)|(status&1073741824) = 0 order by name
Return
End
Else -- Check for a valid table.
Begin
-- Check for single user mode.
If (Select status&4096 from master.dbo.sysdatabases where name = @vcDBName) <> 0
Begin
Print 'Database ' + @vcDBName + ' is in single user mode ' +
'and this process requires a subordinate connection.'
Print 'A report cannot not be generated while the database is in single user mode.'
Return
End
If @vcTable is not null and @vcTable <> 'all' and @vcTable <> 'user'
Begin
-- Check for an owner otherwise assume dbo
If charindex('.',@vcTable) = 0
Set @vcTable = 'dbo.' + @vcTable
-- SQL functions are local to the db so use dynamic SQL to test existance
-- and capture the results in a temp table.
If (Select object_id('tempdb.dbo.#CaptureResults')) > 0
Exec ('Drop table #CaptureResults')
Create table #CaptureResults(value tinyint null)
Exec ('Use ' + @vcDBName + ' If exists (select * from ' +
@vcDBName + '.dbo.sysobjects ' +
'where id = object_id(''' + @vcTable + ''')) ' +
'Insert into #CaptureResults values (1) ' +
'Else Insert into #CaptureResults values (0) ')
-- Check the results table and print a message if 0.
If (select value from #CaptureResults) = 0
Begin
Print 'The table ''' + @vcDBName + '.' + @vcTable +
''' does not exist.'
Print ''
Exec('Select name [Tables in ' + @vcDBName + '] from ' +
@vcDBName + '.dbo.sysobjects ' +
'where xtype = ''U'' or xtype = ''S'' ' +
'order by xtype desc, name asc')
Return
End
Else -- I have a single table in a given database.
Begin
-- Check to see if we run UpdateUsage.
If @cUpdateUsage = '1'
Begin
-- Skip if the database is in read only mode or some other offline mode.
If (Select (status&32)|(status&64)|(status&128)|
(status&256)|(status&512)|(status&1024)|
(status&32768)|(status&1073741824)
from master.dbo.sysdatabases where name = @vcDBName) = 0
Begin
If @cUpdateRowCnt = '1'
Exec ('dbcc updateusage(' + @vcDBName + ') with COUNT_ROWS,NO_INFOMSGS ')
Else
Exec ('dbcc updateusage(' + @vcDBName + ') with NO_INFOMSGS ')
End
Else
Begin
Print 'UpdateUsage cannot be run against ' + @vcDBName +
' because the it is in read only mode.'
End
End
Insert into #names values(@vcTable,0)
Print 'Database ' + @vcDBName
Print ''
Goto GetTableInfo
End
End
Else -- I have a given database but no specific table so give all tables.
Begin
Set @tiLoopRtn = 2
Insert into #names(name) values(@vcDBName)
Goto GetDatabaseInfo
RtnToDB:
If @vcTable = 'all'
Begin
Print ''
Truncate table #names
Exec ('Use ' + @vcDBName + ' Insert into #names ' +
'Select name, case when xtype = ''U'' then 0 else 1 end ' +
'from sysobjects where xtype = ''U'' or xtype = ''S''')
Goto GetTableInfo
End
If @vcTable = 'user'
Begin
Print ''
Truncate table #names
Exec ('Use ' + @vcDBName + ' Insert into #names ' +
'Select name, 0 ' +
'from sysobjects where xtype = ''U'' ')
Goto GetTableInfo
End
End
End
End
Else
Begin
Set @tiLoopRtn = 1
Insert into #names(name)
Select name from master.dbo.sysdatabases
where (status&32)|(status&64)|(status&128)|(status&256)|
(status&512)|(status&32768)|(status&1073741824) = 0
Goto GetDatabaseInfo
--RtnToAllDbs:
End
Return
GetDatabaseInfo:
Select @vcName = min(name) from #names
While @vcName is not null
Begin
-- Check for single user mode and a connection
If (Select status&4096 from master.dbo.sysdatabases where name = @vcName) <> 0
Begin
Print 'Database ' + @vcName + ' is in single user mode.'
Print 'It cannot not be included in the report at this time.'
Print ''
Goto NextLoopdb
End
-- DB File Space = sum(size) sysfiles where 0x40&status <> 0x40
-- Reserved Space = sum(reserved) sysindexes where indid in (0,1,255)
-- Data Space = sum(dpages) sysindexes where indid < 2 + sum(used) where indid = 255
-- Index Space = sum(used) sysindexes where indid in (0,1,255) - @dcDataSpace
-- Unused Space = @dcReservedSpace - sum(used) sysindexes where indid in (0,1,255)
-- Log File Space = sum(size) sysfiles where 0x40&status = 0x40
-- Free DB Space = @dcDBFileSpace - @dcReservedSpace
Exec ('use ' + @vcName + ' ' +
'If ' + @cUpdateUsage + ' = 1 Begin If (Select status&1024 from master.dbo.sysdatabases ' +
'where name = ''' + @vcName + ''') = 0 Begin If ' + @cUpdateRowCnt + ' = 1 ' +
'Exec (''dbcc updateusage(' + @vcName + ') with COUNT_ROWS,NO_INFOMSGS '') ' +
'Else Exec (''dbcc updateusage(' + @vcName + ') with NO_INFOMSGS '') ' +
'End Else Begin Print ''UpdateUsage cannot be run against database ' + @vcName +
' because it is in read only mode.'' End End ' +
'declare @dcReservedSpace decimal(13), @dcDataSpace decimal(13), ' +
'@dcIndexSpace decimal(13), @dcUnusedSpace decimal(13), ' +
'@dcDBFileSpace decimal(13), @dcLogFileSpace decimal(13), ' +
'@dcFreeDBspace decimal(13), @iTblCnt int, @iRowCnt bigint ' +
'Select @iTblCnt = count(*) from sysobjects where type = ''U'' ' +
'Select @iRowCnt = isnull(sum(isnull(rowcnt,0)),0) from sysindexes i, sysobjects o ' +
'where o.id = i.id and o.type = ''U'' and i.indid in (0,1,255) ' +
'Select @dcDBFileSpace = sum(convert(dec(15),size))*' + @vcBytesperPage + ' '+
'from sysfiles where 0x40&status <> 0x40 ' +
'Select @dcReservedSpace = (select sum(convert(dec(15),reserved)) ' +
'from sysindexes where indid in (0, 1, 255))*' + @vcBytesperPage + ' ' +
'Select @dcDataSpace = ((select sum(convert(dec(15),dpages)) ' +
'from sysindexes where indid < 2) + ' +
'(select isnull(sum(convert(dec(15),used)), 0) ' +
'from sysindexes where indid = 255))*' + @vcBytesperPage + ' ' +
'Select @dcIndexSpace = (select sum(convert(dec(15),used)) ' +
'from sysindexes where indid in (0, 1, 255))*' + @vcBytesperPage + ' - @dcDataSpace ' +
'Select @dcUnusedSpace = @dcReservedSpace - (select sum(convert(dec(15),used)) ' +
'from sysindexes where indid in (0, 1, 255))*' + @vcBytesperPage + ' ' +
'Select @dcLogFileSpace = sum(convert(dec(15),size))*' + @vcBytesperPage + ' '+
'from sysfiles where 0x40&status = 0x40 ' +
'Select @dcFreeDBspace = @dcDBFileSpace - @dcReservedSpace ' +
'Insert into #dbData values(''' + @vcName +
''', @iTblCnt, @iRowCnt, @dcDBFileSpace, @dcFreeDBspace, @dcReservedSpace, @dcDataSpace,' +
'@dcIndexSpace, @dcUnusedSpace, @dcLogFileSpace, 0.0, 0.0, 3) '
)
NextLoopdb:
Select @vcName = min(name) from #names where name > @vcName
End
Insert into #logspaceinfo
Exec ('DBCC SQLPERF(LOGSPACE)')
Update #dbData
set usable_log_space = 1000000.*lspaceused,
log_space_free = 100.000 - lspercent
from #logspaceinfo b where #dbData.name = rtrim(b.name)
If (Select count(*) from #names) > 1
Begin
Select @iTblCnt = sum(db_tbl_cnt),
@dcRowCnt = sum(row_cnt),
@dcDBFileSpace = sum(db_file_size),
@dcFreeDBspace = sum(db_space_free),
@dcReservedSpace = sum(total_reserved),
@dcDataSpace = sum(data),
@dcIndexSpace = sum(indexes),
@dcUnusedSpace = sum(unused),
@dcLogFileSpace = sum(log_file_size),
@dcActualLogSpace = sum(usable_log_space),
@dcPercentFreeLogSpace = sum(log_space_free)/count(*)
from #dbData
Select @iTblCnt2 = sum(db_tbl_cnt),
@dcRowCnt2 = sum(row_cnt),
@dcDBFileSpace2 = sum(db_file_size),
@dcFreeDBspace2 = sum(db_space_free),
@dcReservedSpace2 = sum(total_reserved),
@dcDataSpace2 = sum(data),
@dcIndexSpace2 = sum(indexes),
@dcUnusedSpace2 = sum(unused),
@dcLogFileSpace2 = sum(log_file_size),
@dcActualLogSpace2 = sum(usable_log_space),
@dcPercentFreeLogSpace2 = sum(log_space_free)/count(*)
from #dbData
where name not in ('master','msdb','model','tempdb','northwind','pubs')
Insert into #dbData
Values (@@servername + '(all dbs)',
@iTblCnt,
@dcRowCnt,
@dcDBFileSpace,
@dcFreeDBspace,
@dcReservedSpace,
@dcDataSpace,
@dcIndexSpace,
@dcUnusedSpace,
@dcLogFileSpace,
@dcActualLogSpace,
@dcPercentFreeLogSpace,
1)
Insert into #dbData
Values (@@servername + '(nonsystem dbs)',
@iTblCnt2,
@dcRowCnt2,
@dcDBFileSpace2,
@dcFreeDBspace2,
@dcReservedSpace2,
@dcDataSpace2,
@dcIndexSpace2,
@dcUnusedSpace2,
@dcLogFileSpace2,
@dcActualLogSpace2,
@dcPercentFreeLogSpace2,
2)
End
Print ''
-- Display results only if we have any
If (select Count(*) from #dbData) > 0
Begin
Select convert(varchar(40), name) [Server or db],
str(db_tbl_cnt,4) [ # of user tbls],
str(row_cnt,13) [ total rows],
str(db_file_size,13) [ db_file_size],
str(db_space_free,13) [db_space_free],
str(total_reserved,13) [total_reserved],
str(data,13) [ data_space],
str(indexes,13) [ index_space],
str(unused,13) [unused_space],
str(log_file_size,13) [log_file_size],
str(usable_log_space,13) [usable_log_space],
str(log_space_free,8,4) [% log_space_free]
from #dbData
order by sort_order, name
If (select count(*) from #names) > 1
Begin
Select @vcObjCnt = str(count(*),4) from master.dbo.sysdatabases
Select @vcNote = '(' + @vcObjCnt + ' databases; numbers in bytes)'
Print @vcNote
Print ''
Exec master.dbo.xp_fixeddrives
End
Else
Begin
Select @vcNote = '(numbers in bytes)'
Print @vcNote
Print ''
End
-- Check to see if we should save the data
If @tySaveDbInfo = 1 and datepart(weekday, GetDate()) = 7
Exec ('Insert into msdb.dbo.DbSizeInfo ' +
'Select @@servername, *, getdate() ' +
'from #dbData order by sort_order, name ')
End
If @tiLoopRtn = 1
Return
If @tiLoopRtn = 2
goto RtnToDB
GetTableInfo:
Select @vcName = min(name) from #names
While @vcName is not null
Begin
-- Reserved Space = sum(reserved) sysindexes where indid in (0,1,255)
-- Data Space = sum(dpages) sysindexes where indid < 2 + sum(used) where indid = 255
-- Index Space = sum(used) sysindexes where indid in (0,1,255) - @dcDataSpace
-- Unused Space = @dcReservedSpace - sum(used) sysindexes where indid in (0,1,255)
-- Row count = rowcnt sysindexes where indid < 2
Exec ('use ' + @vcDBName + ' ' +
'declare @dcReservedSpace decimal(13), @dcDataSpace decimal(13), ' +
'@dcIndexSpace decimal(13), @dcUnusedSpace decimal(13) ' +
'Select @dcReservedSpace = (select sum(convert(dec(13),reserved)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid in (0, 1, 255) and a.id = object_id(''' +
@vcName + '''))*' + @vcBytesperPage + ' ' +
'Select @dcDataSpace = ((select sum(convert(dec(13),dpages)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid < 2 and a.id = object_id(''' +
@vcName + ''')) + ' +
'(select isnull(sum(convert(dec(15),used)), 0) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid = 255 and a.id = object_id(''' +
@vcName + ''')))*' + @vcBytesperPage + ' ' +
'Select @dcIndexSpace = (select sum(convert(dec(13),used)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid in (0, 1, 255) and a.id = object_id(''' +
@vcName + '''))*' + @vcBytesperPage +
' - @dcDataSpace ' +
'Select @dcUnusedSpace = @dcReservedSpace - (select sum(convert(dec(13),used)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid in (0, 1, 255) and a.id = object_id(''' +
@vcName + '''))*' + @vcBytesperPage + ' ' +
'Insert into #TableData ' +
'Select ''' + @vcName + ''', rowcnt, ' +
'@dcReservedSpace, @dcDataSpace, ' +
'@dcIndexSpace, @dcUnusedSpace, ' +
'(select sort_order from #names where name = ''' + @vcName + ''')' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid < 2 and a.id = object_id(''' +
@vcName + ''')'
)
Select @vcName = min(name) from #names where name > @vcName
End
Select cast(name as varchar) [Table],
str(row_count,13) [ Row count],
str(reserved,13) [ Reserved],
str(data,13) [ Data],
str(indexes,13) [ Indexes],
str(unused,13) [ Unused]
from #TableData
order by sort_order, name
If (select count(*) from #names) > 1
Begin
Select @vcObjCnt = str(count(*),4) from #names
Select @vcNote = '(' + @vcObjCnt + ' tables; numbers in bytes)'
Print @vcNote
Print ''
End
Else
Begin
Select @vcNote = '(numbers in bytes)'
Print @vcNote
Print ''
End
Return
go
grant exec on sp_spaceused2 to public
go
-- For SQL 7.0
if exists (select *
from dbo.sysobjects
where id = object_id('dbo.sp_spaceused2')
and sysstat & 0xf = 4)
drop procedure dbo.sp_spaceused2
Go
Create procedure dbo.sp_spaceused2
@vcDBName sysname = null,
@vcTable sysname = null,
@cUpdateUsage char(1) = '0',
@cUpdateRowCnt char(1) = '0',
@tySaveDbInfo tinyint = 0
as
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/--Name : sp_spaceused2 for SQL 7.0
--
--Description : Similiar to sp_spaceused but returns more information. Use
-- sp_spaceused2 '?' to return the syntax and a list of dbs.
--
--Parameters : @vcDBName - Specified database or 'all' or null or '?'.
-- @vcTable - Specified table in current (null) or specified
-- database or 'all'.
-- @cUpdateUsage - 0 or 1; 1 to run Update Usage.
-- @cUpdateRowCnt - 0 or 1; 1 to include 'Count Rows' in Update
-- Usage.
-- @tySaveDbInfo - 0 or 1; 1 stores info in msdb.dbo.DbSizeInfo.
--
--Comments : The data returned is in bytes. The old sp_spacedused
-- returns data in computer KB (data*1024 = bytes).
--
--Date : 06/06/2001
--Author : Clinton Herring
--
--History : 06/27/2001 WCH Added @tySaveDbInfo and msdb.dbo.DbSizeInfo
-- in order to save data permanently.
--
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
Set nocount on
-- If temp table exists drop them before attempting to create them.
If (Select object_id('tempdb.dbo.#dbData')) > 0
Exec ('Drop table #dbData')
If (Select object_id('tempdb.dbo.#TableData')) > 0
Exec ('Drop table #TableData')
If (Select object_id('tempdb.dbo.#logspaceinfo')) > 0
Exec ('Drop table #logspaceinfo')
If (Select object_id('tempdb.dbo.#names')) > 0
Exec ('Drop table #names')
-- Create some holding tables for results.
Create table #dbData(
name sysname null,
db_tbl_cnt int null,
row_cnt int null,
db_file_size decimal(13) null,
db_space_free decimal(13) null,
total_reserved decimal(13) null,
data decimal(13) null,
indexes decimal(13) null,
unused decimal(13) null,
log_file_size decimal(13) null,
usable_log_space decimal(13) null,
log_space_free dec(10,6) null,
sort_order tinyint
)
-- Create some holding tables for results.
Create table #TableData(
name sysname null,
row_count varchar(11) null,
reserved decimal(13) null,
data decimal(13) null,
indexes decimal(13) null,
unused decimal(13) null,
sort_order tinyint
)
Create table #logspaceinfo(
name sysname null,
lspaceused dec(15,9) null,
lspercent dec(10,6) null,
status int null
)
Create table #names(
name sysname null,
sort_order tinyint null
)
-- Create a holding table in msdb to store db data
If @tySaveDbInfo = 1 and datepart(weekday, GetDate()) = 7
Begin
If not exists (select * from msdb.dbo.sysobjects
where name = 'DbSizeInfo' and uid = 1)
Exec('Use msdb ' +
'Create table DbSizeInfo( ' +
'ServerName sysname null, ' +
'DbName sysname null, ' +
'DbFileSize decimal(15) null, ' +
'DbSpaceFree decimal(15) null, ' +
'TotalReserved decimal(15) null, ' +
'Data decimal(15) null, ' +
'Indexes decimal(15) null, ' +
'Unused decimal(15) null, ' +
'LogFileSize decimal(15) null, ' +
'UsableLogSpace decimal(15) null, ' +
'LogSpaceFree dec(10,6) null, ' +
'SortOrder tinyint, ' +
'SaveDate datetime ' +
') ')
End
-- Declare variables.
declare @iTblCnt int,
@dcRowCnt decimal(13),
@dcDBFileSpace decimal(13),
@dcFreeDBspace decimal(13),
@dcReservedSpace decimal(13),
@dcDataSpace decimal(13),
@dcIndexSpace decimal(13),
@dcUnusedSpace decimal(13),
@dcLogFileSpace decimal(13),
@dcActualLogSpace decimal(13),
@dcPercentFreeLogSpace dec(10,6),
@iTblCnt2 int,
@dcRowCnt2 decimal(13),
@dcDBFileSpace2 decimal(13),
@dcFreeDBspace2 decimal(13),
@dcReservedSpace2 decimal(13),
@dcDataSpace2 decimal(13),
@dcIndexSpace2 decimal(13),
@dcUnusedSpace2 decimal(13),
@dcLogFileSpace2 decimal(13),
@dcActualLogSpace2 decimal(13),
@dcPercentFreeLogSpace2 dec(10,6),
@vcBytesperPage varchar(5),
@vcObjCnt varchar(5),
@tiLoopRtn tinyint,
@vcName sysname,
@vcNote varchar(50),
@vcCmd varchar(4000)
-- Retrieve the bytes per page value.
Select @vcBytesperPage = str(low,5)
from master.dbo.spt_values
where number = 1 and type = 'E'
-- Print help information.
If @vcDBName = '?'
Begin
Print 'Syntax: dbo.sp_spaceused2'
Print ' @vcDBName = <database name|[all]|[?]|null> -- Leave null to get info about the current'
Print ' database or use [all] to get info about'
Print ' all databases. '
Print ' @vcTable = <table name|[all]|[user]|null> -- Any valid table within the specified database.'
Print ' Use [all] to get all tables for the specified database.'
Print ' Use [user] to get just user tables for the specified database.'
Print ' @tiUpdateUsage = <0|1> -- Set to 1 to first run Update Usage. If you see'
Print ' negative results run again with this option'
Print ' set to one. The default is 0.'
Print ' @tiUpdateRowCnt = <0|1> -- Set to 1 to update row counts in sysindexes.'
Print ' @tyCaptureInfo = <0|1> -- Set to 1 to capture db data in msdb.dbo.DbSizeInfo'
Print ''
Print 'The current database is ' + db_name() + '.'
Print ''
Select name [Other databases] from master.dbo.sysdatabases order by name
Select name [Tables in the current database] from sysobjects where xtype = 'U' or xtype = 'S'
order by xtype desc, name asc
Return
End
-- If @vcDBName is null grab the current db name
If @vcDBName is null
Begin
Select @vcDBName = db_name()
End
-- Check for a valid database name.
If @vcDBName <> 'all'
Begin
-- Is the databse name valid?
If not exists (select * from master.dbo.sysdatabases where name = @vcDBName and
(status&32)|(status&64)|(status&128)|(status&256)|(status&512)|
(status&1024)|(status&32768)|(status&1073741824) = 0 )
Begin
Print '''' + @vcDBName + ''' is not a valid database on this server or is in a non-accessible mode such as offline.'
Print ''
Select name [Valid accessible databases are...] from master.dbo.sysdatabases
where (status&32)|(status&64)|(status&128)|(status&256)|(status&512)|
(status&1024)|(status&32768)|(status&1073741824) = 0 order by name
Return
End
Else -- Check for a valid table.
Begin
-- Check for single user mode.
If (Select status&4096 from master.dbo.sysdatabases where name = @vcDBName) <> 0
Begin
Print 'Database ' + @vcDBName + ' is in single user mode ' +
'and this process requires a subordinate connection.'
Print 'A report cannot not be generated while the database is in single user mode.'
Return
End
If @vcTable is not null and @vcTable <> 'all' and @vcTable <> 'user'
Begin
-- Check for an owner otherwise assume dbo
If charindex('.',@vcTable) = 0
Set @vcTable = 'dbo.' + @vcTable
-- SQL functions are local to the db so use dynamic SQL to test existance
-- and capture the results in a temp table.
If (Select object_id('tempdb.dbo.#CaptureResults')) > 0
Exec ('Drop table #CaptureResults')
Create table #CaptureResults(value tinyint null)
Exec ('Use ' + @vcDBName + ' If exists (select * from ' +
@vcDBName + '.dbo.sysobjects ' +
'where id = object_id(''' + @vcTable + ''')) ' +
'Insert into #CaptureResults values (1) ' +
'Else Insert into #CaptureResults values (0) ')
-- Check the results table and print a message if 0.
If (select value from #CaptureResults) = 0
Begin
Print 'The table ''' + @vcDBName + '.' + @vcTable +
''' does not exist.'
Print ''
Exec('Select name [Tables in ' + @vcDBName + '] from ' +
@vcDBName + '.dbo.sysobjects ' +
'where xtype = ''U'' or xtype = ''S'' ' +
'order by xtype desc, name asc')
Return
End
Else -- I have a single table in a given database.
Begin
-- Check to see if we should run UpdateUsage.
If @cUpdateUsage = '1'
Begin
-- Skip if the database is in read only mode or some other offline mode.
If (Select (status&32)|(status&64)|(status&128)|
(status&256)|(status&512)|(status&1024)|
(status&32768)|(status&1073741824)
from master.dbo.sysdatabases where name = @vcDBName) = 0
Begin
If @cUpdateRowCnt = '1'
Exec ('dbcc updateusage(' + @vcDBName + ') with COUNT_ROWS,NO_INFOMSGS ')
Else
Exec ('dbcc updateusage(' + @vcDBName + ') with NO_INFOMSGS ')
End
Else
Begin
Print 'Cannot run UpdateUsage against ' + @vcDBName +
' because the databse is read-only or offline, etc.'
End
End
Insert into #names values(@vcTable,0)
Print 'Database ' + @vcDBName
Print ''
Goto GetTableInfo
End
End
Else -- I have a given database but no specific table so get all tables.
Begin
Set @tiLoopRtn = 2
Insert into #names(name) values(@vcDBName)
Goto GetDatabaseInfo
RtnToDB:
If @vcTable = 'all'
Begin
Print ''
Truncate table #names
Exec ('Use ' + @vcDBName + ' Insert into #names ' +
'Select name, case when xtype = ''U'' then 0 else 1 end ' +
'from sysobjects where xtype = ''U'' or xtype = ''S''')
Goto GetTableInfo
End
If @vcTable = 'user'
Begin
Print ''
Truncate table #names
Exec ('Use ' + @vcDBName + ' Insert into #names ' +
'Select name, 0 ' +
'from sysobjects where xtype = ''U'' ')
Goto GetTableInfo
End
End
End
End
Else
-- Get all databasses
Begin
Set @tiLoopRtn = 1
Insert into #names(name)
Select name from master.dbo.sysdatabases
where (status&32)|(status&64)|(status&128)|(status&256)|
(status&512)|(status&32768)|(status&1073741824) = 0
Goto GetDatabaseInfo
--RtnToAllDbs:
End
Return
GetDatabaseInfo:
Select @vcName = min(name) from #names
While @vcName is not null
Begin
-- Check for single user mode and a connection
If (Select status&4096 from master.dbo.sysdatabases where name = @vcName) <> 0
Begin
Print 'Database ' + @vcName + ' is in single user mode.'
Print 'It cannot not be included in the report at this time.'
Print ''
Goto NextLoopdb
End
-- DB File Space = sum(size) sysfiles where 0x40&status <> 0x40
-- Reserved Space = sum(reserved) sysindexes where indid in (0,1,255)
-- Data Space = sum(dpages) sysindexes where indid < 2 + sum(used) where indid = 255
-- Index Space = sum(used) sysindexes where indid in (0,1,255) - @dcDataSpace
-- Unused Space = @dcReservedSpace - sum(used) sysindexes where indid in (0,1,255)
-- Log File Space = sum(size) sysfiles where 0x40&status = 0x40
-- Free DB Space = @dcDBFileSpace - @dcReservedSpace
Exec ('use ' + @vcName + ' ' +
'If ' + @cUpdateUsage + ' = 1 Begin If (Select status&1024 from master.dbo.sysdatabases ' +
'where name = ''' + @vcName + ''') = 0 Begin If ' + @cUpdateRowCnt + ' = 1 ' +
'Exec (''dbcc updateusage(' + @vcName + ') WITH COUNT_ROWS, NO_INFOMSGS '') ' +
'Else Exec (''dbcc updateusage(' + @vcName + ') with NO_INFOMSGS '') ' +
'End Else Begin Print ''Updateusaage cannot be run against database ' + @vcName +
' because it is in read only mode.'' End End ' +
'declare @dcReservedSpace decimal(13), @dcDataSpace decimal(13), ' +
'@dcIndexSpace decimal(13), @dcUnusedSpace decimal(13), ' +
'@dcDBFileSpace decimal(13), @dcLogFileSpace decimal(13), ' +
'@dcFreeDBspace decimal(13), @iTblCnt int, @iRowCnt int ' +
'Select @iTblCnt = count(*) from sysobjects where type = ''U'' ' +
'Select @iRowCnt = isnull(sum(isnull(rows,0)),0) from sysindexes i, sysobjects o ' +
'where o.id = i.id and o.type = ''U'' and i.indid in (0,1,255) ' +
'Select @dcDBFileSpace = sum(convert(dec(15),size))*' + @vcBytesperPage + ' '+
'from sysfiles where 0x40&status <> 0x40 ' +
'Select @dcReservedSpace = (select sum(convert(dec(15),reserved)) ' +
'from sysindexes where indid in (0, 1, 255))*' + @vcBytesperPage + ' ' +
'Select @dcDataSpace = ((select sum(convert(dec(15),dpages)) ' +
'from sysindexes where indid < 2) + ' +
'(select isnull(sum(convert(dec(15),used)), 0) ' +
'from sysindexes where indid = 255))*' + @vcBytesperPage + ' ' +
'Select @dcIndexSpace = (select sum(convert(dec(15),used)) ' +
'from sysindexes where indid in (0, 1, 255))*' + @vcBytesperPage + ' - @dcDataSpace ' +
'Select @dcUnusedSpace = @dcReservedSpace - (select sum(convert(dec(15),used)) ' +
'from sysindexes where indid in (0, 1, 255))*' + @vcBytesperPage + ' ' +
'Select @dcLogFileSpace = sum(convert(dec(15),size))*' + @vcBytesperPage + ' '+
'from sysfiles where 0x40&status = 0x40 ' +
'Select @dcFreeDBspace = @dcDBFileSpace - @dcReservedSpace ' +
'Insert into #dbData values(''' + @vcName +
''', @iTblCnt, @iRowCnt, @dcDBFileSpace, @dcFreeDBspace, @dcReservedSpace, @dcDataSpace,' +
'@dcIndexSpace, @dcUnusedSpace, @dcLogFileSpace, 0.0, 0.0, 3) '
)
NextLoopdb:
Select @vcName = min(name) from #names where name > @vcName
End
Insert into #logspaceinfo
Exec ('DBCC SQLPERF(LOGSPACE)')
Update #dbData
set usable_log_space = 1000000.*lspaceused,
log_space_free = 100.000 - lspercent
from #logspaceinfo b where #dbData.name = rtrim(b.name)
If (Select count(*) from #names) > 1
Begin
Select @iTblCnt = sum(db_tbl_cnt),
@dcRowCnt = sum(row_cnt),
@dcDBFileSpace = sum(db_file_size),
@dcFreeDBspace = sum(db_space_free),
@dcReservedSpace = sum(total_reserved),
@dcDataSpace = sum(data),
@dcIndexSpace = sum(indexes),
@dcUnusedSpace = sum(unused),
@dcLogFileSpace = sum(log_file_size),
@dcActualLogSpace = sum(usable_log_space),
@dcPercentFreeLogSpace = sum(log_space_free)/count(*)
from #dbData
Select @iTblCnt2 = sum(db_tbl_cnt),
@dcRowCnt2 = sum(row_cnt),
@dcDBFileSpace2 = sum(db_file_size),
@dcFreeDBspace2 = sum(db_space_free),
@dcReservedSpace2 = sum(total_reserved),
@dcDataSpace2 = sum(data),
@dcIndexSpace2 = sum(indexes),
@dcUnusedSpace2 = sum(unused),
@dcLogFileSpace2 = sum(log_file_size),
@dcActualLogSpace2 = sum(usable_log_space),
@dcPercentFreeLogSpace2 = sum(log_space_free)/count(*)
from #dbData
where name not in ('master','msdb','model','tempdb','northwind','pubs')
Insert into #dbData
Values (@@servername + '(all dbs)',
@iTblCnt,
@dcRowCnt,
@dcDBFileSpace,
@dcFreeDBspace,
@dcReservedSpace,
@dcDataSpace,
@dcIndexSpace,
@dcUnusedSpace,
@dcLogFileSpace,
@dcActualLogSpace,
@dcPercentFreeLogSpace,
1)
Insert into #dbData
Values (@@servername + '(nonsystem dbs)',
@iTblCnt2,
@dcRowCnt2,
@dcDBFileSpace2,
@dcFreeDBspace2,
@dcReservedSpace2,
@dcDataSpace2,
@dcIndexSpace2,
@dcUnusedSpace2,
@dcLogFileSpace2,
@dcActualLogSpace2,
@dcPercentFreeLogSpace2,
2)
End
Print ''
-- Display results only if we have any
If (select Count(*) from #dbData) > 0
Begin
Select convert(varchar(40), name) [Server or db],
str(db_tbl_cnt,4) [ # of user tbls],
str(row_cnt,13) [ total rows],
str(db_file_size,13) [ db_file_size],
str(db_space_free,13) [db_space_free],
str(total_reserved,13) [total_reserved],
str(data,13) [ data_space],
str(indexes,13) [ index_space],
str(unused,13) [unused_space],
str(log_file_size,13) [log_file_size],
str(usable_log_space,13) [usable_log_space],
str(log_space_free,8,4) [% log_space_free]
from #dbData
order by sort_order, name
If (select count(*) from #names) > 1
Begin
Select @vcObjCnt = str(count(*),4) from master.dbo.sysdatabases
Select @vcNote = '(' + @vcObjCnt + ' databases; size numbers in bytes)'
Print @vcNote
Print ''
Exec master.dbo.xp_fixeddrives
End
Else
Begin
Select @vcNote = '(size numbers in bytes)'
Print @vcNote
Print ''
End
-- Check to see if we should save the data
If @tySaveDbInfo = 1 and datepart(weekday, GetDate()) = 7
Exec ('Insert into msdb.dbo.DbSizeInfo ' +
'Select @@servername, *, getdate() ' +
'from #dbData order by sort_order, name ')
End
If @tiLoopRtn = 1
Return
If @tiLoopRtn = 2
goto RtnToDB
GetTableInfo:
Select @vcName = min(name) from #names
While @vcName is not null
Begin
-- Reserved Space = sum(reserved) sysindexes where indid in (0,1,255)
-- Data Space = sum(dpages) sysindexes where indid < 2 + sum(used) where indid = 255
-- Index Space = sum(used) sysindexes where indid in (0,1,255) - @dcDataSpace
-- Unused Space = @dcReservedSpace - sum(used) sysindexes where indid in (0,1,255)
-- Row count = rows sysindexes where indid < 2
Exec ('use ' + @vcDBName + ' ' +
'declare @dcReservedSpace decimal(13), @dcDataSpace decimal(13), ' +
'@dcIndexSpace decimal(13), @dcUnusedSpace decimal(13) ' +
'Select @dcReservedSpace = (select sum(convert(dec(13),reserved)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid in (0, 1, 255) and a.id = object_id(''' +
@vcName + '''))*' + @vcBytesperPage + ' ' +
'Select @dcDataSpace = ((select sum(convert(dec(13),dpages)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid < 2 and a.id = object_id(''' +
@vcName + ''')) + ' +
'(select isnull(sum(convert(dec(15),used)), 0) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid = 255 and a.id = object_id(''' +
@vcName + ''')))*' + @vcBytesperPage + ' ' +
'Select @dcIndexSpace = (select sum(convert(dec(13),used)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid in (0, 1, 255) and a.id = object_id(''' +
@vcName + '''))*' + @vcBytesperPage +
' - @dcDataSpace ' +
'Select @dcUnusedSpace = @dcReservedSpace - (select sum(convert(dec(13),used)) ' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid in (0, 1, 255) and a.id = object_id(''' +
@vcName + '''))*' + @vcBytesperPage + ' ' +
'Insert into #TableData ' +
'Select ''' + @vcName + ''', convert(char(11),rows), ' +
'@dcReservedSpace, @dcDataSpace, ' +
'@dcIndexSpace, @dcUnusedSpace, ' +
'(select sort_order from #names where name = ''' + @vcName + ''')' +
'from sysindexes a join sysobjects b on a.id = b.id ' +
'where indid < 2 and a.id = object_id(''' +
@vcName + ''')'
)
Select @vcName = min(name) from #names where name > @vcName
End
Select cast(name as varchar) [Table],
Convert(varchar(13),space(13 - len(row_count)) + row_count) [ Row count],
str(reserved,13) [ Reserved],
str(data,13) [ Data],
str(indexes,13) [ Indexes],
str(unused,13) [ Unused]
from #TableData
order by sort_order, name
If (select count(*) from #names) > 1
Begin
Select @vcObjCnt = str(count(*),4) from #names
Select @vcNote = '(' + @vcObjCnt + ' tables; size numbers in bytes)'
Print @vcNote
Print ''
End
Else
Begin
Select @vcNote = '(size numbers in bytes)'
Print @vcNote
Print ''
End
Return
go
grant exec on sp_spaceused2 to public
go 
