How can I view an internal table in SQL Server 2008?

Question

I'm specifically looking for a way to view the full text index fragments and the xml index, which, according to the documentation, are stored as internal tables. This page describes how to view the internal table metadata. What I'm hoping for is a way to view the data in the internal tables.

Answer 1

You can get the schema name and table names from sys.internal_tables

SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.internal_tables

Then if you are connected via the DAC you can simply select from them

select * from [sys].[xml_index_nodes_1765581328_256000]
select * from [sys].[fulltext_avdl_1077578877]
select * from [sys].[ifts_comp_fragment_610101214_4]

There may well be better ways of getting whatever the information is that you want though. e.g. the DMVs related to full text indexing in SQL Server 2008 are listed here.