Six Undocumented SQL Server 2014 Database Engine Stored Procedures
SQL Server 2014 supports the following useful undocumented database engine stored procedures:
- sp_bcp_dbcmptlevel
- sp_MSforeachdb
- sp_MSforeachtable
- sp_MSindexspace
- sp_MStablespace
- sp_objectfilegroup
sp_bcp_dbcmptlevel
The sp_bcp_dbcmptlevel database engine stored procedure is used to get the database compatibility
level for the specified database.
Syntax
sp_bcp_dbcmptlevel [ @dbname = ] ‘dbname’
Arguments
[ @dbname = ] ‘dbname’
The database name to get the compatibility level. dbname is sysname, with no default.
Return Code Values
None.
Result Sets
| Column name | Type | Description |
| cmptlevel | tinyint | Corresponding to the version of SQL Server for which behavior is compatible |
Remarks
This stored procedure exists in the master database, but can be invoked from any other databases.
Permissions
Requires membership in the public role.
Example
This is the example to get the compatibility level for the master database:
EXEC sp_bcp_dbcmptlevel @dbname = ‘master’
sp_MSforeachdb
The sp_MSforeachdb database engine stored procedure is used to perform the same actions for
all databases. Sometimes, you need to perform the same actions for all databases. You can
create cursor for this purpose, or you can use the sp_MSforeachdb stored procedure to accomplish
the same goal with less work.
Syntax
sp_MSforeachdb
[ @command1 = ] ‘command1’,
[ @replacechar = ] ‘replacechar’,
[ @command2 = ] ‘command2’,
[ @command3 = ] ‘command3’,
[ @precommand = ] ‘precommand’,
[ @postcommand = ] ‘postcommand’
Arguments
[ @command1 = ] ‘command1’
The first Transact-SQL command to execute. command1 is nvarchar(2000), with no default.
[ @replacechar = ] ‘replacechar’
The replacement character. replacechar is nchar(1), with default ‘?’.
[ @command2 = ] ‘command2’
The second Transact-SQL command to execute. command2 is nvarchar(2000), with default NULL.
[ @command3 = ] ‘command3’
The third Transact-SQL command to execute. command3 is nvarchar(2000), with default NULL.
[ @precommand = ] ‘precommand’
The Transact-SQL command to execute before command1. precommand is nvarchar(2000),
with default NULL.
[ @postcommand = ] ‘postcommand’
The Transact-SQL command to execute after command1, command2 and command3.
postcommand is nvarchar(2000), with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
This stored procedure exists in the master database, but can be invoked from any other databases.
Permissions
Requires membership in the public role.
Example
This is the example to run the DBCC CHECKDB statement for all the databases on your server:
EXEC sp_MSforeachdb @command1 = "print ‘?’ DBCC CHECKDB (‘?’)"
sp_MSforeachtable
The sp_MSforeachtable database engine stored procedure is used to perform the same actions for all
tables in the current database. Sometimes, you need to perform the same actions for all tables in
the database. You can create cursor for this purpose, or you can use the sp_MSforeachtable stored
procedure to accomplish the same goal with less work.
Syntax
sp_MSforeachtable
[ @command1 = ] ‘command1’,
[ @replacechar = ] ‘replacechar’,
[ @command2 = ] ‘command2’,
[ @command3 = ] ‘command3’,
[ @whereand = ] ‘whereand’,
[ @precommand = ] ‘precommand’,
[ @postcommand = ] ‘postcommand’
Arguments
[ @command1 = ] ‘command1’
The first Transact-SQL command to execute. command1 is nvarchar(2000), with no default.
[ @replacechar = ] ‘replacechar’
The replacement character. replacechar is nchar(1), with default ‘?’.
[ @command2 = ] ‘command2’
The second Transact-SQL command to execute. command2 is nvarchar(2000), with default NULL.
[ @command3 = ] ‘command3’
The third Transact-SQL command to execute. command3 is nvarchar(2000), with default NULL.
[ @whereand = ] ‘whereand’
The WHERE clause. whereand is nvarchar(2000), with default NULL.
[ @precommand = ] ‘precommand’
The Transact-SQL command to execute before command1. precommand is nvarchar(2000),
with default NULL.
[ @postcommand = ] ‘postcommand’
The Transact-SQL command to execute after command1, command2 and command3.
postcommand is nvarchar(2000), with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Remarks
This stored procedure exists in the master database, but can be invoked from any other databases.
Permissions
Requires membership in the public role.
Example
This is the example to rebuild all the indexes in the current database:
EXEC sp_MSforeachtable @command1 = "print ‘?’ DBCC DBREINDEX (‘?’)"
sp_MSindexspace
The sp_MSindexspace database engine stored procedure returns the index id, index name and the
index size in kilobytes.
Syntax
sp_MSindexspace [ @tablename = ] ‘tablename’, [ @index_name = ] ‘index_name’
Arguments
[ @tablename = ] ‘tablename’
The table name to get the index size. tablename is nvarchar(517), with no default.
[ @index_name = ] ‘index_name’
The index name to get the index size. index_name is nvarchar(258), with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| IndexID | tinyint | The index identifier |
| IndexName | nvarchar(128) | The name of the index |
| IndexSize | int | Index size in kilobytes |
| Comments | nvarchar(28) | Index comment, can be ‘(None)’ or ‘Size excludes actual data’ |
Remarks
This stored procedure exists in the master database, but can be invoked from any other databases.
Permissions
Requires membership in the public role.
Example
This is the example to get the index size for all the indexes in the Sales table:
EXEC sp_MSindexspace @tablename = ‘Sales’
sp_MStablespace
The sp_MStablespace database engine stored procedure returns the number of rows in the specified
table, the data space used in kilobytes and the index space used in kilobytes.
Syntax
sp_MStablespace [ @name = ] ‘name’, [ @id = ] ‘id’
Arguments
[ @name = ] ‘name’
The table name. name is nvarchar(517), with no default.
[ @id = ] ‘id’
The table identifier. id is int, with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| Rows | bigint | The number of rows in the table |
| DataSpaceUsed | int | The data space used in kilobytes |
| IndexSpaceUsed | int | The index space used in kilobytes |
Remarks
This stored procedure exists in the master database, but can be invoked from any other databases.
Permissions
Requires membership in the public role.
Example
This is the example to get the number of rows in the Sales table, the total table size and
the total indexes size:
EXEC sp_MStablespace @name = ‘Sales’
sp_objectfilegroup
The sp_objectfilegroup database engine stored procedure is used to return object’s data filegroup.
Syntax
sp_objectfilegroup [ @objid = ] ‘objid’
Arguments
[ @objid = ] ‘objid’
The object identifier. objid is int, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| Data_located_on_filegroup | sysname | Name of the object’s data filegroup |
Remarks
This stored procedure exists in the master database, but can be invoked from any other databases.
Permissions
Requires membership in the public role.
Example
This is the example to get the data filegroup for the Sales table from the Product database:
USE Product
DECLARE @id INT
SET @id = OBJECT_ID(‘Product.dbo.Sales’)
EXEC sp_objectfilegroup @objid = @id