| Sanjana 2006-02-07, 7:23 am |
| I wanted to know the equivalent for the following Oracle queries in SQL
Server perferably if I can get the data directly from a system table
like in Oracle
1. As part of this select we need the following to be returned
Number of distinct values - Is there a system table which stores this
value for any column
Number of endpoints for the frequent value histogram.
Average column size - Is there a system table which stores this value
for any column
Type of the column - this is available in information_schema.COLUMNS
or syscolumns
Number of endpoints for the height balanced histogram.
SELECT NUM_DISTINCT, NUM_BUCKETS + 1, AVG_COL_LEN, DATA_TYPE,
(SELECT MAX (ENDPOINT_NUMBER) + 1 FROM ALL_TAB_HISTOGRAMS
WHERE OWNER = ATC.OWNER AND TABLE_NAME = ATC.TABLE_NAME AND
COLUMN_NAME = ATC.COLUMN_NAME) as NumEPs
FROM ALL_TAB_COLUMNS ATC WHERE
OWNER = :1 AND TABLE_NAME = :2 AND COLUMN_NAME = :3;
2. As part of this query we need
Index Name.
Cluster factor.
Number of Keys.
Number of leaf pages.
Uniqueness Information.
Number of Columns.
SELECT UPPER (INDEX_NAME), CLUSTERING_FACTOR, DISTINCT_KEYS,
LEAF_BLOCKS, BLEVEL, UNIQUENESS
FROM ALL_INDEXES WHERE
OWNER = :1 and TABLE_NAME = :2 AND LAST_ANALYZED IS NOT NULL;
|