How to Select a Subset of Columns from a Stored Procedure’s Result Set (T-SQL)

Have you ever run a stored procedure, only to be overwhelmed at the number of columns returned? Maybe you only needed one or two columns, but it presented you with way too many columns for your needs on this particular occasion.

Fortunately, there’s a little trick you can use to retrieve selected columns from a stored procedure. This enables you to get just the columns you need.

And the best part is, it doesn’t involve having to create temporary tables and shuffling the data around.

All you need to do is pass your stored procedure to the OPENROWSET() function.

The same concept can be applied to the OPENQUERY() function.

Example

Take the sp_columns system stored procedure for example. It returns 19 columns.

EXEC sp_columns Cats;

Result:

+-------------------+---------------+--------------+---------------+-------------+--------------+-------------+----------+---------+---------+------------+-----------+--------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | COLUMN_NAME   | DATA_TYPE   | TYPE_NAME    | PRECISION   | LENGTH   | SCALE   | RADIX   | NULLABLE   | REMARKS   | COLUMN_DEF   | SQL_DATA_TYPE   | SQL_DATETIME_SUB   | CHAR_OCTET_LENGTH   | ORDINAL_POSITION   | IS_NULLABLE   | SS_DATA_TYPE   |
|-------------------+---------------+--------------+---------------+-------------+--------------+-------------+----------+---------+---------+------------+-----------+--------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------|
| Pets              | dbo           | Cats         | CatId         | 4           | int identity | 10          | 4        | 0       | 10      | 0          | NULL      | NULL         | 4               | NULL               | NULL                | 1                  | NO            | 56             |
| Pets              | dbo           | Cats         | CatName       | 12          | varchar      | 60          | 60       | NULL    | NULL    | 1          | NULL      | NULL         | 12              | NULL               | 60                  | 2                  | YES           | 39             |
+-------------------+---------------+--------------+---------------+-------------+--------------+-------------+----------+---------+---------+------------+-----------+--------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------+

Procedures like this often require us to scroll sideways in a wild-goose chase to find the columns we’re interested in.

Maybe we just want to see 4 specific columns instead of all 19.

We can run the following query to do just that.

SELECT 
    c.COLUMN_NAME,
    c.TYPE_NAME,
    c.PRECISION,
    c.LENGTH
FROM OPENROWSET(
    'SQLOLEDB', 
    'SERVER=localhost;Trusted_Connection=yes;', 
    'EXEC Pets.dbo.sp_columns Cats'
    ) c;

Result:

+---------------+--------------+-------------+----------+
| COLUMN_NAME   | TYPE_NAME    | PRECISION   | LENGTH   |
|---------------+--------------+-------------+----------|
| CatId         | int identity | 10          | 4        |
| CatName       | varchar      | 60          | 60       |
+---------------+--------------+-------------+----------+

The OPENROWSET() function was designed mainly for retrieving remote data, but you can also use it on the local instance, by specifying localhost in the connection string (like I’ve done in this example).

The OPENQUERY() Function

As mentioned, the same concept can be applied to the OPENQUERY() function.

SELECT 
    c.COLUMN_NAME,
    c.TYPE_NAME,
    c.PRECISION,
    c.LENGTH
FROM OPENQUERY(
    Homer,
    'EXEC Pets.dbo.sp_columns Cats'
    ) c;

Result:

+---------------+--------------+-------------+----------+
| COLUMN_NAME   | TYPE_NAME    | PRECISION   | LENGTH   |
|---------------+--------------+-------------+----------|
| CatId         | int identity | 10          | 4        |
| CatName       | varchar      | 60          | 60       |
+---------------+--------------+-------------+----------+

In this case, I specified a linked server called Homer instead of the local server.

If you get an error that says “Server is not configured for DATA ACCESS”, you’ll need to enable data access for the server, even if you’re querying your local instance. See How to Enable/Disable Data Access for instructions on how to do this.