sp_SOS is a stored procedure in the master database on CITRMANSQL2. It can be used to calculate SQL server object space. It can take 8 parameters breifly described below.
| Variable |
Data type |
Nullable |
Default |
Default implication |
| @DbName |
sysname |
Yes |
NULL |
Current database |
| @SchemaName |
sysname |
Yes |
NULL |
All schemas |
| @ObjectName |
sysname |
Yes |
% |
Including all objects in "LIKE" clause |
| @TopClause |
nvarchar(20) |
Yes |
NULL |
All objects. Can be "TOP N" or "TOP N PERCENT" |
| @ObjectType |
nvarchar(50) |
Yes |
NULL |
All objects that can be sized. Valid values are S(system), U(user), V(indexed view), SQ(service broker queue), IT(internal table) or any combination of them |
| @ShowInternalTable |
nvarchar(3) |
Yes |
NULL |
Includes internal table. The Parent excludes it in size |
| @OrderBy |
nvarchar(100) |
Yes |
NULL |
By object name, can be any size related column. Valid short terms are N(name), R(row), T(total), U(used), I(index), D(data), F(free or unused) and Y(type) |
| @UpdateUsage |
bit |
Yes |
0 |
Do not run "DBCC UPDATEUSAGE" |
Typical scenario for simple useage
@DbName is the database name where you want to find object space in SQL Server. If it's not supplied, it will use the current database
USE
WBSC
EXEC
dbo.sp_SOS
Type =S(system), U(user), V(indexed view), SQ(service broker queue), IT(internal table)
The formula for space is "Total(MB) - Unused(MB) == Used(MB) = Index(MB) + Data(MB)", i.e., the used space is the simultaneous result of total minus unused as well as index plus data
See also: Find size of SQL Server tables and other objects with stored procedure to find SQL database and log file size
See also full instructions available in downloads section: http://license.citruslime.com/cs/files/folders/sample_files/default.aspx
