The Table Properties module gathers data space used, index space used, rowcounts, and much more for every table, in every database, in every managed instance. For the most recent collection of table data, query Collector.TablePropertiesCurrent.

Tables

Collector.TableProperties

Stores the collections of table data.

Definitions for many of the Collector.TableProperties columns can be found in the MSDN article for the Table class: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx

 
Column Type Description
ID bigint Primary key row identifier.
ExecutionDateTime datetime The execution date and time, common to the run of a collection for a single instance.
InstanceID bigint The instance ID of the instance in question, as defined in the table dbo.Servers.
DBName sysname Database name.
SchemaName sysname Schema name.
TableID bigint Object ID of the table.
TableName sysname Table name.
AnsiNullsStatus bit See the Table Class MSDN article.
ChangeTrackingEnabled bit See the Table Class MSDN article.
CreateDate datetime See the Table Class MSDN article.
DataSpaceUsedInKB bigint See the Table Class MSDN article.
DateLastModified datetime See the Table Class MSDN article.
DistributionName nvarchar See the Table Class MSDN article.
Durability nvarchar See the Table Class MSDN article.
Events nvarchar See the Table Class MSDN article.
ExtendedProperties nvarchar See the Table Class MSDN article.
FakeSystemTable bit See the Table Class MSDN article.
FederationColumnID int See the Table Class MSDN article.
FederationColumnName nvarchar See the Table Class MSDN article.
FileGroup varchar See the Table Class MSDN article.
FileStreamFileGroup int See the Table Class MSDN article.
FileStreamPartitionScheme varchar See the Table Class MSDN article.
FileTableDirectoryName varchar See the Table Class MSDN article.
FileTableNameColumnCollation varchar See the Table Class MSDN article.
FileTableNamespaceEnabled bit See the Table Class MSDN article.
FullTextIndex varchar See the Table Class MSDN article.
HasAfterTrigger bit See the Table Class MSDN article.
HasClusteredColumnStoreIndex bit See the Table Class MSDN article.
HasClusteredIndex bit See the Table Class MSDN article.
HasCompressedPartitions bit See the Table Class MSDN article.
HasDeleteTrigger bit See the Table Class MSDN article.
HasIndex bit See the Table Class MSDN article.
HasInsertTrigger bit See the Table Class MSDN article.
HasInsteadOfTrigger bit See the Table Class MSDN article.
HasUpdateTrigger bit See the Table Class MSDN article.
IndexSpaceUsedInKB bigint See the Table Class MSDN article.
IsFileTable bit See the Table Class MSDN article.
IsIndexable bit See the Table Class MSDN article.
IsMemoryOptimized bit See the Table Class MSDN article.
IsPartitioned bit See the Table Class MSDN article.
IsSchemaOwned bit See the Table Class MSDN article.
IsSystemObject bit See the Table Class MSDN article.
IsVarDecimalStorageFormatEnabled bit See the Table Class MSDN article.
LockEscalation varchar See the Table Class MSDN article.
LowPriorityAbortAfterWait varchar See the Table Class MSDN article.
LowPriorityMaxDuration int See the Table Class MSDN article.
MaximumDegreeOfParallelism int See the Table Class MSDN article.
OnlineHeapOperation bit See the Table Class MSDN article.
Owner nvarchar See the Table Class MSDN article.
PartitionScheme nvarchar See the Table Class MSDN article.
PartitionSchemeParameters nvarchar See the Table Class MSDN article.
PhysicalPartitions nvarchar See the Table Class MSDN article.
QuotedIdentifierStatus bit See the Table Class MSDN article.
Replicated bit See the Table Class MSDN article.
RowCount bigint See the Table Class MSDN article.
State varchar See the Table Class MSDN article.
TextFileGroup nvarchar See the Table Class MSDN article.
TrackColumnsUpdatedEnabled bit See the Table Class MSDN article.

Stored procedures

Collector.TablePropertiesInsert

The script calls this procedure to perform the insert into the Collector.TableProperties table.

Important: This procedure is meant to be used by automated collectors; we recommend against using it manually. Therefore, the parameter set is only documented internally.
 

Views

Collector.TablePropertiesCurrent

Provides the most recent collection of table data.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Collector.TablePropertiesPrevious

Provides the next-to-most recent collection of table data.

Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
  • ServerName
  • ServiceLevel
  • Version
  • Edition
  • Descr
 

Jobs

CollectorTableProperties-GOLD

Calls the executable TablePropertiesGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve table information.
 

CollectorTableProperties-SILVER

Calls the executable TablePropertiesGET.exe for all servers with ServiceLevel = ‘Silver’, to retrieve table information.
 

CollectorTableProperties-BRONZE

Calls the executable TablePropertiesGET.exe for all servers with ServiceLevel = ‘Bronze’, to retrieve table information.
 

Executables

TablePropertiesGET.exe

Perform the table data collection for the servers in the specified service level. Log the results to Collector.TableProperties via the stored procedure Collector.TablePropertiesInsert.

Input parameters:
  • $Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.
 
Example execution:
C:\MinionByMidnightDBA\Collector\TablePropertiesGET.exe Gold

 
https://minionware.desk.com/customer/portal/articles/2378935-tables-module
http://www.MinionWare.net