Metadata Collection - SQL Server Integration Services Design Patterns - page 5

The sys.tables catalog view contains information about every table that exists
within the database.
sys.indexes
The sys.indexes catalog view contains information about every index in the data-
base. This includes information such as whether an index is clustered or nonclustered
and whether the index is unique or nonunique.
sys.partitions
The sys.partitions catalog view gives visibility into the partitioning structure of
an index. When an index has more than one partition, the data in the index is split into
multiple physical structures that can be accessed using the single logical name. This
technique is especially useful for dealing with large tables, such as a transaction history
table. If a table is not partitioned, the table will still have a single row in
sys.partitions .
sys.allocation_units
The sys.allocation_units catalog view contains information about the number
of pages and rows that exist for an object. This information can be joined to the
sys.partitions catalog view by joining the container_id to the parti-
tion_id .
Setting Up the Central Repository
Before you can begin development on your Integration Services package, you need to
set up some prerequisites in SQL Server. First and foremost, you need to create a data-
base that will act as your central data repository. This is where your list of SQL Server
instances will reside and where you will store the metadata you retrieve for each SQL
Server instance. Many enterprises also find it convenient to store all error and package
logging to this same central database. This is especially beneficial in environments
where there are numerous DBAs, developers, and servers, because it makes it easy for