Five undocumented SQL Server 2014 distributed queries stored procedures
SQL Server 2014 supports the following five useful undocumented distributed queries stored procedures:
– sp_catalogs_rowset
– sp_catalogs_rowset2
– sp_catalogs_rowset_rmt
– sp_linkedservers_rowset
– sp_linkedservers_rowset2
sp_catalogs_rowset
The sp_catalogs_rowset distributed queries stored procedure returns the database name for
the specified database if the current user has access for this database.
Syntax
sp_catalogs_rowset [ @catalog_name = ] ‘catalog_name’
Arguments
[ @catalog_name = ] ‘catalog_name’
The name of the catalog. Catalog is equivalent to databases in SQL Server 2014.
catalog_name is sysname, with no default.
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| CATALOG_NAME | nvarchar(128) | Is the catalog name |
| DESCRIPTION | nvarchar(1) | Always return NULL. |
Remarks
This stored procedure exists in the master database.
Permissions
Requires SELECT permission on the schema.
Example
This example returns the Sales as the database (catalog) name if the current user has access
to the Sales database:
USE master
EXEC sp_catalogs_rowset @catalog_name = ‘Sales’
sp_catalogs_rowset2
The sp_catalogs_rowset2 distributed queries stored procedure returns the list of the
databases the current user has access to.
Syntax
sp_catalogs_rowset2
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| CATALOG_NAME | nvarchar(128) | Is the catalog name |
| DESCRIPTION | nvarchar(1) | Always return NULL. |
Remarks
This stored procedure is similar to the sp_catalogs_rowset procedure. The difference
is that the sp_catalogs_rowset2 procedure returns all the databases the current user
has access to, when the sp_catalogs_rowset procedure checks only the specified database.
This stored procedure exists in the master database.
Permissions
Requires SELECT permission on the schema.
Example
This example returns all databases the current user has access to
USE master
EXEC sp_catalogs_rowset2
sp_catalogs_rowset_rmt
The sp_catalogs_rowset_rmt distributed queries stored procedure returns the catalog
name and the catalog description for the specified catalog if the current user has
access for this catalog. If the catalog name was not specified this procedure returns
the list of catalogs in the specified linked server the current user has access to.
Catalog is equivalent to database in SQL Server 2014.
Syntax
sp_catalogs_rowset_rmt [ @server_name = ] ‘linked_svr’,
[ @catalog_name = ] ‘catalog_name’
Arguments
[ @server_name = ] ‘linked_svr’
The name of a linked server. linked_svr is sysname, with no default.
[ @catalog_name = ] ‘catalog_name’
The name of the catalog. catalog_name is sysname, with default NULL.
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| CATALOG_NAME | nvarchar(128) | Is the catalog name |
| DESCRIPTION | nvarchar(4000) | Is the description of the catalog |
Remarks
If the catalog name is not specified, this stored procedure is similar to the sp_catalogs
documented distributed queries stored procedure. The difference is that the sp_catalogs_rowset_rmt
procedure returns only the catalogs the current user has access to, when the sp_catalogs procedure
returns all catalogs. This stored procedure exists in the master database.
Permissions
Requires SELECT permission on the schema.
Example
This example returns the list of catalogs in the SalesPub linked server the current user
has access to:
USE master
EXEC sp_catalogs_rowset_rmt @server_name = ‘SalesPub’
sp_linkedservers_rowset
The sp_linkedservers_rowset distributed queries stored procedure is used to return the
properties of the specified linked server.
Syntax
sp_linkedservers_rowset [ @srvname = ] ‘server_name’
Arguments
[ @srvname = ] ‘server_name’
The name of the linked server to show properties. server_name is sysname, with no default.
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| SRV_NAME | sysname | Is the name of the linked server. |
| SRV_PRODUCT | nvarchar(128) | Is the product name of the linked server. |
| SRV_PROVIDERNAME | nvarchar(128) | Is the OLE DB provider name. |
| SRV_DATASOURCE | nvarchar(4000) | Is the OLE DB data source property corresponding to the linked server |
| SRV_PROVIDERSTRING | nvarchar(4000) | Is the OLE DB provider string property corresponding to the linked server |
| SRV_LOCATION | nvarchar(4000) | Is the OLE DB location property corresponding to the specified linked server |
| SVR_CATALOG | sysname | Is the OLE DB catalog property corresponding to the specified linked server |
Remarks
The sp_linkedservers_rowset procedure returns the linked server’s properties for only the
linked server that was enabled for distributed query access. This stored procedure exists
in the master database.
Permissions
Requires SELECT permission on the schema.
Example
This is the example to return the properties of the SalesPub linked server:
USE master
EXEC sp_linkedservers_rowset @srvname = ‘SalesPub’
sp_linkedservers_rowset2
The sp_linkedservers_rowset2 distributed queries stored procedure returns the list of linked
servers defined in the local server and enabled for distributed query access.
Syntax
sp_linkedservers_rowset2
Return Code Values
None.
Result Sets
| Column name | Data type | Description |
| SRV_NAME | sysname | Is the name of the linked server. |
| SRV_PRODUCT | nvarchar(128) | Is the product name of the linked server. |
| SRV_PROVIDERNAME | nvarchar(128) | Is the OLE DB provider name. |
| SRV_DATASOURCE | nvarchar(4000) | Is the OLE DB data source property corresponding to the linked server |
| SRV_PROVIDERSTRING | nvarchar(4000) | Is the OLE DB provider string property corresponding to the linked server |
| SRV_LOCATION | nvarchar(4000) | Is the OLE DB location property corresponding to the specified linked server |
| SVR_CATALOG | sysname | Is the OLE DB catalog property corresponding to the specified linked server |
Remarks
The sp_linkedservers_rowset2 procedure is similar to the sp_linkedservers procedure.
The difference is that the sp_linkedservers procedure returns all linked servers defined
in the local server when the sp_linkedservers_rowset2 procedure returns only the servers
that were enabled for distributed query access. This stored procedure exists in
the master database.
Permissions
Requires SELECT permission on the schema.
Example
This is the example to return the list of linked servers defined in the local server and enabled
for distributed query access:
USE master
EXEC sp_linkedservers_rowset2