Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: equivalent SQL Server statements
Sanjana wrote:
> 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;
And you think this information is made available to SQL Server users? Perhaps you have it confused with an Oracle product.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Tue Feb 07 2006 - 10:50:18 CST