Internal Tables in SQL Server 2005, what are they for?
By default SQL Server automatically creates the internal tables for Full-text search, XML indexes and Service broker. These are also created when a user query is written poorly that uses tempdb heavily.
At they are called internal tables but do not contain any user related data and schema for these tabls are always fixed. You will not be able to alter the schema at any time, also you should not or cannot refer the same within the TSQL statements you execute, such as referencing SELECT * FROM <sys.internal_table_name>.
You can view the metadata of these internal tables by using a system catalog view, sys.internal_tables. Books online documented the information such as " Because internal tables have many of the same characteristics as user tables, the sys.internal_tables view inherits columns from the sys.objects catalog view and have a type of 'IT'".
.gif)
To view the meta-data of internal tables the user needs CONTROL SERVER & must be a member of DB_OWNER or SYSADMIN group on SQL Server. Whereas if the user has access to view the parent entity such as XML index or full-text catalog then they can view the internal table for that entity.
To get internal tables information that inherit columns from the sys.objects catalog view, run:
SELECT * FROM sys.objects WHERE type = 'IT';