ANSI SQL Schema Views

for RuBoard

SQL Server provides a number of views for accessing the system catalogs. These objects provide an ANSI SQL-92 compliant means of retrieving meta- data and system-level information from the server. You should use these rather than querying system catalog tables for two reasons:

1. You can depend on them not to change across releases of SQL Server, even though their underlying system tables may change.

2. The ANSI SQL-92 specification defines these views, so they should work similarly between different DBMS platforms.

The SQL-92-compliant views that SQL Server provides are as follows :

• CHECK_CONSTRAINTS

• COLUMN_DOMAIN_USAGE

• COLUMN_PRIVILEGES

• COLUMNS

• CONSTRAINT_COLUMN_USAGE

• CONSTRAINT_TABLE_USAGE

• DOMAIN_CONSTRAINTS

• DOMAINS

• KEY_COLUMN_USAGE

• PARAMETERS

• REFERENTIAL_CONSTRAINTS

• ROUTINE_COLUMNS

• ROUTINES

• SCHEMATA

• TABLE_CONSTRAINTS

• TABLE_PRIVILEGES

• TABLES

• VIEW_COLUMN_USAGE

• VIEW_TABLE_USAGE

• VIEWS

Note that you must refer to these objects using the INFORMATION_SCHEMA database schema. In SQL Server parlance, a schema and an owner are synonymous. This means that you must use

 SELECT * FROM INFORMATION_SCHEMA.TABLES 

rather than

 SELECT * FROM TABLES 

Even though the views themselves reside only in the master database, they run in the context of the current database. This makes them similar to system stored procedures. On a related note, if you check an INFORMATION_SCHEMA view's IsMSShipped property, you'll find that SQL Server considers it a system object. (See Chapter 22 for more info on system objects).

Creating Your Own INFORMATION_SCHEMA Views

The ability to create a view in the master database and have it run in the context of the current database has lots of practical applications. For example, if you had a large number of customer-specific databases with the same set of views in each one, you could lessen your administrative headaches and disk space requirements if you could keep them in master (instead of in each customer database) and have them run in the proper database context when queried. Fortunately, there is a way to do this, but it's undocumented. So, as with all undocumented techniques, keep in mind that it may change or not be available in future releases.

To create your own system views, follow these steps:

1. Enable updates to the system tables through a call to sp_configure:

    sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE 

2. Enable automatic system object creation by calling the undocumented procedure sp_MS_upd_sysobj_category (you must be the database owner or a member of the setupadmin role):

    sp_MS_upd_sysobj_category 1 

   This procedure turns on trace flag 1717 to cause all objects created to have their IsMSShipped bits turned on automatically. This is necessary because you can't create a nonsystem object that belongs to INFORMATION_SCHEMA. If you could, you could simply call sp_MS_marksystemobject (covered in Chapter 22) after creating each view to enable its system bit. Instead, because SQL Server won't create nonsystem INFORMATION_SCHEMA objects, we have to enable a special server mode wherein each object created is automatically flagged as a system object.

3. Create your view in the master database, specifying INFORMATION_SCHEMA as the owner, as demonstrated in Listing 9-4.

4. Disable automatic system object creation by calling sp_MS_upd_sysobj_category again:

    sp_MS_upd_sysobj_category 2 

5. Disable 'allow updates' by calling sp_configure:

 sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE 

Here's the code for a new INFORMATION_SCHEMA view called DIRECTORY. It lists the objects and data types in a database in a format similar to the OS DIR command:

Listing 9-4 A user -defined INFORMATION_SCHEMA view.

 USE master GO EXEC sp_configure 'allow', 1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_MS_upd_sysobj_category 1 GO IF OBJECT_ID('INFORMATION_SCHEMA.DIRECTORY') IS NOT NULL  DROP VIEW INFORMATION_SCHEMA.DIRECTORY GO CREATE VIEW INFORMATION_SCHEMA.DIRECTORY /* Object: DIRECTORY Description: Lists object catalog information similarly to the OS DIR command. Usage: SELECT * FROM INFORMATION_SCHEMA.DIRECTORY  WHERE Name LIKE name mask AND Type LIKE object type ORDER BY T, Name name mask=pattern of object names to list object type=type of objects to list The following object types are listed:  U=User tables  S=System tables  V=Views  P=Stored procedures  X=Extended procedures  RF=Replication filter stored procedures  TR=Triggers  D=Default objects  R=Rule objects  T=User-defined data types  IF=Inline user-defined function  TF=Table-valued user-defined function  FN=Scalar user-defined function Created by: Ken Henderson. Email: [e-mail] Version: 8.0 Example usage:  SELECT * FROM INFORMATION_SCHEMA.DIRECTORY  WHERE Name LIKE 'ORD%' AND Type='U'  ORDER BY T, Name Created: 1992-06-12. Last changed: 2000-11-12. */ AS SELECT TOP 100 PERCENT CASE GROUPING(T) WHEN 1 THEN '*' ELSE T END AS T,        Name, Type, DateCreated,        SUM(Rows) AS Rows,        SUM(RowLenInBytes) AS RowLenInBytes,        SUM(TotalSizeInKB) AS TotalSizeInKB,        SUM(DataSpaceInKB) AS DataSpaceInKB,        SUM(IndexSpaceInKB) AS IndexSpaceInKB,        SUM(UnusedSpaceInKB) AS UnusedSpaceInKB,         Owner  FROM (SELECT -- Get regular objects  ' ' AS T,  Name=LEFT(o.name,30),  Type=o.type,  DateCreated=o.crdate,  Rows=ISNULL(rows,0),  RowLenInBytes=ISNULL((SELECT SUM(length)        FROM syscolumns        WHERE id=o.id AND o.type in ('U','S')),0),  TotalSizeInKB=ISNULL((SELECT SUM(reserved)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id),0)*2,  DataSpaceInKB=ISNULL(((SELECT SUM(dpages)        FROM sysindexes        WHERE indid < 2 AND id=o.id)+        (SELECT ISNULL(SUM(used), 0)        FROM sysindexes        WHERE indid=255 AND id=o.id)),0)*2,  IndexSpaceInKB=ISNULL(((SELECT SUM(used)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id) -        ((SELECT SUM(dpages)        FROM sysindexes        WHERE indid < 2 AND id=o.id)+        (SELECT ISNULL(SUM(used), 0)        FROM sysindexes        WHERE indid=255 AND id=o.id))),0)*2,  UnusedSpaceInKB=ISNULL(((SELECT SUM(reserved)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id) -        (SELECT SUM(used)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id)),0)*2,        Owner=USER_NAME(o.uid)  FROM sysobjects o, sysindexes i  WHERE o.id*=i.id AND i.indid<=1  UNION ALL -- Get user-defined data types  SELECT ' ',  LEFT(name,30), 'T', NULL, NULL,  NULL, NULL, NULL, NULL, NULL, USER_NAME(uid)  FROM systypes st  WHERE (usertype & 256)<>0) D GROUP BY T, Name,Type, DateCreated, Owner WITH ROLLUP HAVING (T+Name+Type+Owner IS NOT NULL) OR (COALESCE(T,Name,Type,Owner) IS NULL) ORDER BY T,name GO EXEC sp_MS_upd_sysobj_category 2 GO EXEC sp_configure 'allow', 0 GO RECONFIGURE WITH OVERRIDE GO 

Once the view is created, you can query it like any other INFORMATION_SCHEMA view (Listing 9-5):

Listing 9-5 Our new INFORMATION_SCHEMA view in action.

 USE pubs GO SELECT * FROM INFORMATION_SCHEMA.DIRECTORY GO 

(Results abridged)

 T   Name                           Type DateCreated              Rows RowLe --- ------------------------------ ---- ------------------------ ---- -----     authors                        U    2000-03-21 12:02:24.057  30   151     byroyalty                      P    2000-03-21 12:02:26.510  0    0     CK__authors__au_id__77BFCB91   C    2000-03-21 12:02:24.077  0    0     CK__authors__zip__79A81403     C    2000-03-21 12:02:24.077  0    0     DF__publisher__count__7D78A4E7 D    2000-03-21 12:02:24.097  0    0     DF__titles__pubdate__023D5A04  D    2000-03-21 12:02:24.197  0    0     DF__titles__type__00551192     D    2000-03-21 12:02:24.107  0    0     discounts                      U    2000-03-21 12:02:24.247  3    53     empid                          T    NULL                     NULL NULL     employee                       U    2000-03-21 12:02:24.277  43   75     employee_insupd                TR   2000-03-21 12:02:24.287  0    0     FK__discounts__stor___0F975522 F    2000-03-21 12:02:24.247  0    0     FK__employee__job_id__1BFD2C07 F    2000-03-21 12:02:24.277  0    0     FK__employee__pub_id__1ED998B2 F    2000-03-21 12:02:24.277  0    0     FK__pub_info__pub_id__173876EA F    2000-03-21 12:02:24.267  0    0     id                             T    NULL                     NULL NULL     jobs                           U    2000-03-21 12:02:24.257  14   54     PK__jobs__117F9D94             K    2000-03-21 12:02:24.257  0    0     roysched                       U    2000-03-21 12:02:24.237  86   18     sales                          U    2000-03-21 12:02:24.227  21   52     stores                         U    2000-03-21 12:02:24.217  6    111     tid                            T    NULL                     NULL NULL     titleauthor                    U    2000-03-21 12:02:24.207  25   22     titles                         U    2000-03-21 12:02:24.107  18   334     titleview                      V    2000-03-21 12:02:26.500  0    0     UPK_storeid                    K    2000-03-21 12:02:24.217  0    0     UPKCL_auidind                  K    2000-03-21 12:02:24.057  0    0     UPKCL_pubind                   K    2000-03-21 12:02:24.097  0    0     UPKCL_pubinfo                  K    2000-03-21 12:02:24.267  0    0     UPKCL_sales                    K    2000-03-21 12:02:24.227  0    0     UPKCL_taind                    K    2000-03-21 12:02:24.207  0    0     UPKCL_titleidind               K    2000-03-21 12:02:24.107  0    0 *   NULL                           NULL NULL                     2424 88784 

As with system stored procedures, you can prefix a system view with a database name (even databases other than the one in which is resides), and it will run in the context of that database. For example, we can do this (Listing 9-6):

Listing 9-6 INFORMATION_SCHEMA views behave similar to system procedures.

 USE pubs GO /* other code goes here */ SELECT * FROM Northwind.INFORMATION_SCHEMA.DIRECTORY GO 

(Results abridged)

 T   Name                           Type DateCreated              Rows RowLe --- ------------------------------ ---- ------------------------ ---- -----     Alphabetical list of products  V    2000-08-06 01:34:09.420  0    0     Categories                     U    2000-08-06 01:34:05.077  8    66     Category Sales for 1997        V    2000-08-06 01:34:11.530  0    0     CK_Birthdate                   C    2000-08-06 01:34:04.653  0    0     CK_Discount                    C    2000-08-06 01:34:08.470  0    0     CK_Products_UnitPrice          C    2000-08-06 01:34:07.700  0    0     CK_Quantity                    C    2000-08-06 01:34:08.470  0    0     ...     Shippers                       U    2000-08-06 01:34:06.060  3    132     Summary of Sales by Quarter    V    2000-08-06 01:34:12.187  0    0     Summary of Sales by Year       V    2000-08-06 01:34:12.403  0    0     Suppliers                      U    2000-08-06 01:34:06.187  29   546     Ten Most Expensive Products    P    2000-08-06 01:34:12.623  0    0     Territories                    U    2000-08-06 01:34:54.077  53   144 *   NULL                           NULL NULL                     6860 73623 

Even though the current database is pubs, the view runs in the context of Northwind because of our prefix.

Creating Your Own INFORMATION_SCHEMA User-Defined Function

We're not limited to building INFORMATION_SCHEMA views. We can also build INFORMATION_SCHEMA UDFs. The two concepts are similar because table-valued UDFs can provide a kind of parameterized view functionality. Here's an INFORMATION_SCHEMA UDF that works like a parameterized view and can be executed from any database context (Listing 9-7):

Listing 9-7 You can create INFORMATION_SCHEMA functions as well as views.

 USE master GO EXEC sp_configure 'allow', 1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_MS_upd_sysobj_category 1 GO IF OBJECT_ID('INFORMATION_SCHEMA.OBJECTS') IS NOT NULL  DROP FUNCTION INFORMATION_SCHEMA.OBJECTS GO CREATE FUNCTION INFORMATION_SCHEMA.OBJECTS(@mask sysname='%', @obtype  varchar(3)='%', @orderby varchar(1000)='/N') /* Object: OBJECTS Description: Lists object catalog information similarly to the OS DIR command. Usage: SELECT * FROM INFORMATION_SCHEMA.OBJECTS()  WHERE Name LIKE name mask AND Type LIKE object type ORDER BY T, Name name mask=pattern of object names to list object type=type of objects to list The following object types are listed:  U=User tables  S=System tables  V=Views  P=Stored procedures  X=Extended procedures  RF=Replication filter stored procedures  TR=Triggers  D=Default objects  R=Rule objects  T=User-defined data types  IF=Inline user-defined function  TF=Table-valued user-defined function  FN=Scalar user-defined function Created by: Ken Henderson. Email: [e-mail] Version: 8.0 Example usage:  SELECT * FROM INFORMATION_SCHEMA.OBJECTS('ORD%','U',DEFAULT)  ORDER BY T, Name Created: 1992-06-12. Last changed: 2000-11-12. The following orderings are supported: /N  =  by name /R  =  by number of rows /S  =  by total object size /D  =  by date created /A  =  by total size of data pages /X  =  by total size of index pages /U  =  by total size of unused pages /L  =  by maximum row length /O  =  by owner /T  =  by type */ RETURNS TABLE AS RETURN(SELECT TOP 100 PERCENT CASE GROUPING(T) WHEN 1 THEN '*' ELSE T END AS T,        Name, Type, DateCreated,        SUM(Rows) AS Rows,        SUM(RowLenInBytes) AS RowLenInBytes,        SUM(TotalSizeInKB) AS TotalSizeInKB,        SUM(DataSpaceInKB) AS DataSpaceInKB,        SUM(IndexSpaceInKB) AS IndexSpaceInKB,        SUM(UnusedSpaceInKB) AS UnusedSpaceInKB,         Owner  FROM (SELECT -- Get regular objects  ' ' AS T,  Name=LEFT(o.name,30),  Type=o.type,  DateCreated=o.crdate,  Rows=ISNULL(rows,0),  RowLenInBytes=ISNULL((SELECT SUM(length)        FROM syscolumns        WHERE id=o.id AND o.type in ('U','S')),0),  TotalSizeInKB=ISNULL((SELECT SUM(reserved)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id),0)*2,  DataSpaceInKB=ISNULL(((SELECT SUM(dpages)        FROM sysindexes        WHERE indid < 2 AND id=o.id)+        (SELECT ISNULL(SUM(used), 0)        FROM sysindexes        WHERE indid=255 AND id=o.id)),0)*2,  IndexSpaceInKB=ISNULL(((SELECT SUM(used)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id) -        ((SELECT SUM(dpages)        FROM sysindexes        WHERE indid < 2 AND id=o.id)+        (SELECT ISNULL(SUM(used), 0)        FROM sysindexes        WHERE indid=255 AND id=o.id))),0)*2,  UnusedSpaceInKB=ISNULL(((SELECT SUM(reserved)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id) -        (SELECT SUM(used)        FROM sysindexes        WHERE indid in (0, 1, 255) AND id=o.id)),0)*2,        Owner=USER_NAME(o.uid)  FROM sysobjects o, sysindexes i  WHERE o.name LIKE @mask  AND o.Type LIKE @obtype  AND o.id*=i.id AND i.indid<=1  UNION ALL -- Get user-defined data types  SELECT ' ',  LEFT(name,30), 'T', NULL, NULL,  NULL, NULL, NULL, NULL, NULL, USER_NAME(uid)  FROM systypes st  WHERE name LIKE @mask  AND 'T' LIKE @obtype  AND (usertype & 256)<>0) D GROUP BY T, Name,Type, DateCreated, Owner WITH ROLLUP HAVING (T+Name+Type+Owner IS NOT NULL) OR (COALESCE(T,Name,Type,Owner) IS NULL) ORDER BY T, CASE UPPER(LEFT(@orderby,2))            WHEN '/D' THEN CONVERT(CHAR(30),DateCreated,121)            WHEN '/R' THEN REPLACE(STR(SUM(Rows),10,0),' ','0')            WHEN '/A' THEN REPLACE(STR(SUM(DataSpaceInKB),10,0),' ','0')            WHEN '/S' THEN REPLACE(STR(SUM(TotalSizeInKB),10,0),' ','0')            WHEN '/X' THEN REPLACE(STR(SUM(IndexSpaceInKB),10,0),' ','0')            WHEN '/U' THEN REPLACE(STR(SUM(UnusedSpaceInKB),10,0),' ','0')            WHEN '/L' THEN REPLACE(STR(SUM(RowLenInBytes),10,0),' ','0')            WHEN '/T' THEN Type            WHEN '/O' THEN Owner            END,            Name -- Always sort by Name to break ties) GO EXEC sp_MS_upd_sysobj_category 2 GO EXEC sp_configure 'allow', 0 GO RECONFIGURE WITH OVERRIDE GO SELECT * FROM Northwind.INFORMATION_SCHEMA.OBJECTS('Ord%',DEFAULT,'/N') 

(Results abridged)

 T  Name                    Type DateCreated             Rows  RowLen -- ----------------------- ---- ----------------------- ----- ------    Order Details           U    2000-08-06 01:34:08.470 2155  22    Order Details Extended  V    2000-08-06 01:34:10.873 0     0    Order Subtotals         V    2000-08-06 01:34:11.093 0     0    Orders                  U    2000-08-06 01:34:06.610 830   364    Orders Qry              V    2000-08-06 01:34:09.780 0     0 *  NULL                    NULL NULL                    2985  386 (6 row(s) affected) 

This code exhibits several techniques worth discussing. First, note the method I used to merge the sysobjects and systypes tables. I used a UNION ALL to join the two tables, supplying constants and dummy values for systypes as necessary. This union was then wrapped in a derived table so that I could order the result set using my SELECT TOP 100 PERCENT trick. As I've said, the row ordering isn't guaranteed when you use ORDER BY from inside a view, derived table, or function, but at least it's a start. In my initial tests, it is preserved when returned to the client, and that's better than no order at all.

Next, take a look at the special handling given the T column. T is a sorting placeholder. It allows us to force the totals row to the bottom of the listing. Here's how it works: We use GROUP BYWITH ROLLUP to have SQL Server compute totals for the numeric columns in the listing. Next, we use a HAVING clause to filter out all the ROLLUP rows except the grand total. We then wrap the T column in a CASE expression so that it can detect when it's being grouped (i.e., the grand total is being computed). When it's being grouped, we return an asterisk; otherwise , we just return a space. The query's ORDER BY then includes T as the high-order sort key, regardless of the chosen sort order. Because an asterisk sorts after a space (ASCII 42 versus 32), this has the effect of forcing the totals row to the bottom of the listing while still allowing the user to specify a different sort order for the other rows.

Let's finish up by examining the CASE expression used to formulate the ORDER BY clause. It allows us to specify the default sort order as a parameter to the function. Notice that we converted all the columns to the same data type and left- padded the numeric columns with zeros. This is necessary because a CASE expression that involves multiple data types assumes the type with the highest precedence (see the Books Online topic, "Data Type Precedence"). In this case, the data type with the highest precedence is datetime (of the DateCreated column). Once the CASE expression has assumed a given data type, it evaluates the conditional expressions that make it up, and if it attempts to return a result that's incompatible with this chosen data type, you'll see a syntax or type conversion error. Here, for example, sorting on Name would have caused an error if datetime had not already been cast as a char type.

What the ORDER BYCASE combo gives us is a flexible way to change the default order of a result set via a parameter to the routine without having to resort to dynamic T-SQL. I include it here for demonstration purposes onlyit would be preferable just to supply the order you want using an ORDER BY clause when you query the function.

Calling a Stored Procedure from a View

Although some DBMSs allow you to SELECT from a stored procedure as though it were a table, SQL Server doesn't. You can probably think of times when this ability would have been very handy. Using a stored procedure, you can do pretty much anything you want with respect to the data in your databases; having it accessible in an object you can query like a table would make it all the more useful.

There's no direct way to do this, but there is a workaround: You can call a stored procedure from a view. To do so, follow these steps:

1. Create a linked server entry on your server that refers back to itself. That is, on an instance named Foo, create a linked server entry whose Data source property references Foo. Here are the specific steps:

   1. Right-click the Linked Servers node under your server's Securities node and select New Linked Server.

   2. In the Linked Server Properties dialog, click the Other data source radio button and select the Microsoft OLEDB Provider for SQL Server entry manually because, by default, SQL Server tries to keep you from configuring a local server as a linked server.

   3. For clarity, supply a name in the Linked server entry box that indicates the server is a loopback server (I named mine "Loopback"). This will keep you from mistaking it for a remote server down the road.

   4. Set the new linked server's Data source property to your current server name and instance or to an alias you've created for it in the Client Network utility.

   5. Set the security information correctly on the Security tab (I usually skip the local-to-remote server login mappings and select the Be made using the login's security context option.

   6. Because your new linked server will be talking to a server that couldn't be any more like ititselfcheck the first five check boxes on the Server options tab. This will make your linked server entry as compatible with itself as possible.

   7. Click OK to exit the dialogyour new linked server is defined.

2. Once the linked server entry is created, click the Tables node under the new linked server entry in Enterprise Manager. If the tables display in the right pane, you're ready to use the loopback connection. Behind the scenes, Enterprise Manager runs sp_tables_ex against a linked server to list its tables.

3. Create a view that uses your new linked server and the OPENQUERY() rowset function to execute the desired stored procedure. Listing 9-8 provides some sample code:

Listing 9-8 You can use OPENQUERY() to call a stored procedure from a view.

 USE Northwind GO DROP VIEW ViewActivity GO CREATE VIEW ViewActivity AS SELECT * FROM OPENQUERY(Loopback,'EXEC dbo.sp_who') GO SELECT * FROM ViewActivity 

(Results abridged)

 spid   ecid   status     loginame             hostname  blk ------ ------ ---------- -------------------- --------- ----- 1      0      background sa                             0 2      0      sleeping   sa                             0 3      0      background sa                             0 4      0      background sa                             0 5      0      background sa                             0 6      0      background sa                             0 7      0      sleeping   sa                             0 8      0      background sa                             0 9      0      background sa                             0 10     0      background sa                             0 11     0      background sa                             0 12     0      background sa                             0 51     0      sleeping   HENDERSON\ khen       KHEN     0 52     0      sleeping   HENDERSON\ khen       KHEN     0 

NOTE

You could use OPENROWSET() here instead of OPENQUERY(). This would alleviate the need for the loopback linked server, but would necessitate that you hard-code connection info into your T-SQLa very poor practice, to say the least. Hard-wiring connection specifics into your code means that the code will break if that connection info ever changes. For example, if you use a regular SQL Server login account to connect to the server and you hard code its password into your T-SQL, not only are you creating the possibility that someone might discover the password who shouldn't, but you're also virtually guaranteeing that that code will break in the future. How many passwords never need changed? OPENQUERY() is the preferred tool because it uses SQL Server's answer to the problem of hard-coded passwords and system security. With a linked server definition, SQL Server stores this info in system tables and provides a graphical tool (Enterprise Manager) to edit it.

One of the many drawbacks to this approach is that there's no way to send parameters to the stored procedure. The query string in the OPENQUERY() call must be a string literal; it cannot be a variable. Another drawback has to do with distributed transactions. Because we're circumventing SQL Server's own mechanisms for managing distributed transactions, we run the risk of causing deadlocks or resource issues on the server because we are using it in ways it was not intended to be used. That said, the ability to call a stored procedure from a view is a powerful feature and should not be completely ruled out. If you run into a situation where you absolutely have to be able to query a stored procedure in the same way that you'd query a table, this is a good technique to know about.

Note that you can extend this technique to uses outside of views. For example, because OPENQUERY() returns a rowset, you can open a cursor against it, like this (Listing 9-9):

Listing 9-9 You can extend the OPENQUERY() technique to create cursors on stored procedures.

 USE Northwind GO DECLARE c CURSOR FOR SELECT * FROM OPENQUERY(Loopback,'EXEC dbo.sp_who') OPEN c FETCH c WHILE @@FETCH_STATUS=0 BEGIN   FETCH c END CLOSE c DEALLOCATE c 

(Results abridged)

 spid   ecid   status      loginame ------ ------ ----------- -------------- 11     0      background  sa spid   ecid   status      loginame ------ ------ ----------- -------------- 12     0      background  sa spid   ecid   status      loginame ------ ------ ----------- -------------- 51     0      sleeping    HENDERSON\ KHEN spid   ecid   status      loginame ------ ------ ----------- -------------- 52     0      sleeping    HENDERSON\ KHEN spid   ecid   status      loginame ------ ------ ----------- -------------- 53     0      sleeping    HENDERSON\ KHEN spid   ecid   status      loginame ------ ------ ----------- -------------- 54     0      runnable    HENDERSON\ KHEN spid   ecid   status      loginame ------ ------ ----------- -------------- 55     0      runnable    HENDERSON\ KHEN 

for RuBoard