لطفا منتظر باشید ...
ANSI SQL Schema Views
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:
You can depend on them not to change across releases of SQL Server, even though their underlying system tables may change.
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:
Enable updates to the system tables through a call to sp_configure:
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
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.
Create your view in the master database, specifying INFORMATION_SCHEMA as the owner, as demonstrated in Listing 9-4.
Disable automatic system object creation by calling sp_MS_upd_sysobj_category again:
sp_MS_upd_sysobj_category 2
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: khen@khen.com
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: khen@khen.com
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 BY…WITH 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 BY…CASE 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:
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:
Right-click the Linked Servers node under your server's Securities node and select New Linked Server.
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.
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.
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.
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.
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.
Click OK to exit the dialogyour new linked server is defined.
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.
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
