SQL*XL: Excel and Indexing Service query |
|
The indexing service makes available information about files through SQL. The OLE DB provider for the indexing service is not providing a few features which makes it a little difficult. After researching the Indexing Service we found out how to use it effectively.
There are 2 features that the Indexing service does not allow and that make life a little difficult. The syntax Select * from table is not supported. That is a pitty because now we have to type out all the column names (find a list of them below). Furthermore it does not seem to support a table listing feature. There is no way to find out which tables are availalbe for querying. Due to this SQL*XL's Describe feature does not display any data.
Table Name:
The table name to use is called scope()
Example queries:
select filename from scope();
select path,vpath from scope();
select filename, size, doctitle from scope();
List of available columns:
|
Friendly name |
Description |
|---|---|
| Access | Last time the document was accessed |
| AllocSize | Size of disk allocation for document |
| Attrib | Document attributes. See the Windows 2000 Platform Software Development Kit. |
| ClassId | Class ID (such as Microsoft Word) of document object |
| Characterization | Characterization, or abstract, of document. This is generated by Indexing Service. |
| Contents | Main contents of document. This can be queried but not retrieved. |
| Created | The time the document was created |
| Directory | Physical path to the document, not including the document name |
| DocAppName | Name of application that created the document |
| DocAuthor | Author of document |
| DocByteCount | Number of bytes in document |
| DocCategory | Type of document (such as a memo, schedule, or white paper) |
| DocCharCount | Number of characters in document |
| DocComments | Comments about the document |
| DocCompany | Name of the company for which the document was written |
| DocCreatedTm | Time document was created |
| DocEditTime | Total time spent editing document |
| DocHiddenCount | Number of hidden slides in a Microsoft PowerPoint document |
| DocKeywords | Document keywords |
| DocLastAuthor | Most recent user who edited document |
| DocLastPrinted | Time document was last printed |
| DocLastSavedTm | Time document was last saved |
| DocLineCount | Number of lines contained in a document |
| DocManager | Name of the manager of the document's author |
| DocNoteCount | Number of pages with notes in a Microsoft PowerPoint document |
| DocPageCount | Number of pages in document |
| DocParaCount | Number of paragraphs in a document |
| DocPartTitles | Names of document parts, such as spreadsheet names in a Microsoft Excel document or slide titles in a Microsoft PowerPoint slide show. |
| DocPresentationTarget | Target format (such as 35mm, printer, video) for a presentation in Microsoft PowerPoint |
| DocRevNumber | Current version number of document |
| DocSlideCount | Number of slides in a Microsoft PowerPoint document |
| DocSubject | Subject of document |
| DocTemplate | Name of template for document |
| DocTitle | Title of document |
| DocWordCount | Number of words in document |
| FileIndex | Unique ID of document |
| FileName | Name of document |
| HitCount | Number of hits (elements in the results list) in document |
| HtmlHRef | Text of HTML link (HREF). This can be queried but not retrieved. |
| HtmlHeading1 to 6 | Text of HTML document in style H1 to H6. This can be queried but not retrieved. |
| MediaEditor | Name of the document's principal editor |
| MediaOwner | Owner of the document, such as the group that licensed the document |
| MediaProduction | Date the media was produced |
| MediaProject | Project to which the document's content belongs |
| MediaRating | Rating of quality or content |
| MediaSequence_No | Sequence number of the media document in the group |
| MediaSource | Name of the document's source, such as the photographer |
| MediaStatus | Status of the document in the project work flow, where 0=Draft, 1=Edit, 2=Final, 3=In Progress, 4=New, 5=Normal, 6=Other, 7=Preliminary, 8=Proof and 9=Review. |
| MediaSupplier | Name of the source of the document, such as the party from which the document was licensed |
| Path | Full physical path to document, including document name |
| Rank | Rank of how well an item in a result list matches query criteria. The range is from 0 to 1,000. Larger numbers indicate better matches. |
| RankVector | Ranks of individual components of a vector query. The range is from 0 to 1,000. Larger numbers indicate better matches. |
| ShortFileName | Short (8.3 format) document name. |
| Size | Size of document, in bytes. |
| VPath | Full virtual path to document, including document name. If there is more than one possible path, the best match for the specific query is chosen. |
| WorkId | Internal ID for document. This is used within Indexing Service. |
| Write | Last time document was modified (written) |
This information was found in an indexing service help file located in c:\winnt\help\isconcepts.chm. See topic: Indexing Serive | Advanced Topics | Indexing Service Query Language
See also: