Reports detailed usage information for columns accessed by the workload.
sp_iqcolumnuse
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
Columns from tables created in SYSTEM are not reported.
Column name |
Description |
|---|---|
TableName |
Table name |
ColumnName |
Column name |
Owner |
Username of column owner |
UID** |
Column Unique Identifier |
LastDT |
Date/time of last access |
NRef |
Number of query references |
**UID is a number assigned by the system that uniquely identifies the instance of the column (where instance is defined when an object is created).
Sample output from the sp_iqcolumnuse procedure:
TableName ColumnName Owner UID LastDT NRef orders o_orderdate DBA 151 20070917 22:41:22.. 13 orders o_shippriority DBA 154 20070917 22:41:22.. 13 lineitem l_orderkey DBA 186 20070917 22:41:22.. 13 lineitem l_extendedp.. DBA 191 20070917 22:41:22.. 13 lineitem l_discount DBA 192 20070917 22:41:22.. 13 lineitem l_shipdate DBA 196 20070917 22:41:22.. 13 #tmp1 expression DBA 10000000001218 20070917 22:57:36.. 1 #tmp1 expression DBA 10000000001222 20070917 22:41:58.. 1 ...
The long numbers in the example above are temporary
IDs.
“Monitoring workloads,” in Chapter 3, “Optimizing Queries and Deletions,” in the Performance and Tuning Guide.
“sp_iqdbspace procedure”, “sp_iqindexadvice procedure”, “sp_iqindexuse procedure”, “sp_iqtableuse procedure”, “sp_iqunusedcolumn procedure”, “sp_iqunusedindex procedure”, “sp_iqunusedtable procedure”, and “sp_iqworkmon procedure”
“INDEX_ADVISOR option” in Chapter 2, “Database Options,” in Reference: Statements and Options
