dbo.fSQLStatistics

dbo.fSQLStatistics

This function returns as a result set a list of statistics about a table and the indexes associated with the table.

dbo.fSQLStatistics (<'database_qualifier' | null>, <'table_name' | null>, <'unique' | null>)

Table 122

dbo.fSQLStatistics Arguments

database_qualifier

Current database

Name of the database from which the details are to be obtained

(no default value)

Name of the table whose column information is required. Pattern matching is supported (see String Search Patterns).

(no default value)

0 = SQL_INDEX_UNIQUE

1 = SQL_INDEX_ALL

Returned Result Set

Table 123

dbo.fSQLStatistics Returned Result Set

TABLE_QUALIFIER

Name of the database containing the table to which the statistic or index applies; NULL if not applicable to the database.

Schema name of the table to which the statistic or index applies; NULL if not applicable to the database.

VARCHAR not NULL

Name of the table to which the statistic or index applies

Indicates whether the index does not allow duplicate values:

•

0 = SQL_FALSE if the index values must be unique.

•

1 = SQL_TRUE if the index values can be nonunique.

NULL is returned if TYPE is SQL_TABLE_STAT.

INDEX_QUALIFIER

The identifier that is used to qualify the index name doing a DROP INDEX; NULL is returned if an index qualifier is not supported by the database or if TYPE is SQL_TABLE_STAT.

Index name; NULL is returned if TYPE is SQL_TABLE_STAT.

SMALLINT not NULL

Type of information being returned:

•

0 = SQL_TABLE_STAT

•

3 = SQL_INDEX_OTHER

Column sequence number in index (starting with 1). NULL is returned if TYPE is SQL_TABLE_STAT.

Column name. If the column is based on an expression, such as PERSONID + NAME, the expression is returned. Iff the expression cannot be determined, an empty string is returned.

NULL is returned if TYPE is SQL_TABLE_STAT.

Sort sequence for the column:

NULL is returned if column sort sequence is not supported by the database or if TYPE is SQL_TABLE_STAT.

Cardinality of table or index.

Number of rows in table if TYPE is SQL_TABLE_STAT. Number of unique values in the index if TYPE is not SQL_TABLE_STAT

NULL is returned if the value is not available from the database.

Number of pages used to store the index or table.

Number of pages for the table if TYPE is SQL_TABLE_STAT. Number of pages for the index if TYPE is not SQL_TABLE_STAT

NULL is returned if the value is not available from the database or if not applicable to the database.

FILTER_CONDITION

If the index is a filtered index, this is the filter condition, such as CLASSID > 150. If the filter condition cannot be determined, this is an empty string.

NULL if the index is not a filtered index, it cannot be determined whether the index is a filtered index, or TYPE is SQL_TABLE_STAT.

This example returns statistics about all indexes for all tables that begin with the letter "c" in the default DEMODATA sample database. NULLs are excluded from INDEX_NAME. See also String Search Patterns.

SELECT * FROM dbo.fSQLStatistics ('Demodata', 'c%', 1) WHERE INDEX_NAME IS NOT NULL

Result Set (abbreviated for space considerations):

TABLE_NAME INDEX_NAME COLUMN_NAME

========== ================ ====================

Class Class_Name Name

Class Class_Name Section

Class Class_seg_Faculty Faculty_ID

Class Class_seg_Faculty Start_Date

Class Class_seg_Faculty Start_Time

Class Building_Room Building_Name

Class Building_Room Room_Number

Class Building_Room Start_Date

Class Building_Room Start_Time

Course Course_Name Name

Course DeptName Dept_Name

12 rows were affected.