Defining Tables - Schema-Smith/SchemaSmithEnterpriseDemos GitHub Wiki
Tables are defined via JSON files in an intuitive metadata format.
Sample from the AdventureWorks Person.Person table
{
"Schema": "[Person]",
"Name": "[Person]",
"CompressionType": "NONE",
"Columns": [
{
"Name": "[AdditionalContactInfo]",
"DataType": "XML([Person].[AdditionalContactInfoSchemaCollection])",
"Nullable": true,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Additional contact information about the person stored in xml format. "
}
},
{
"Name": "[BusinessEntityID]",
"DataType": "INT",
"Nullable": false,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Primary key for Person records."
}
},
{
"Name": "[Demographics]",
"DataType": "XML([Person].[IndividualSurveySchemaCollection])",
"Nullable": true,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis."
}
},
{
"Name": "[EmailPromotion]",
"DataType": "INT",
"Nullable": false,
"Default": "0",
"CheckExpression": "[EmailPromotion]>=(0) AND [EmailPromotion]<=(2)",
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners. "
}
},
{
"Name": "[FirstName]",
"DataType": "NAME",
"Nullable": false,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "First name of the person."
}
},
{
"Name": "[LastName]",
"DataType": "NAME",
"Nullable": false,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Last name of the person."
}
},
{
"Name": "[MiddleName]",
"DataType": "NAME",
"Nullable": true,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Middle name or middle initial of the person."
}
},
{
"Name": "[ModifiedDate]",
"DataType": "DATETIME",
"Nullable": false,
"Default": "getdate()",
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Date and time the record was last updated."
}
},
{
"Name": "[NameStyle]",
"DataType": "NAMESTYLE",
"Nullable": false,
"Default": "0",
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order."
}
},
{
"Name": "[PersonType]",
"DataType": "NCHAR(2)",
"Nullable": false,
"CheckExpression": "[PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC')",
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact"
}
},
{
"Name": "[rowguid]",
"DataType": "UNIQUEIDENTIFIER ROWGUIDCOL",
"Nullable": false,
"Default": "newid()",
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample."
}
},
{
"Name": "[Suffix]",
"DataType": "NVARCHAR(10)",
"Nullable": true,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "Surname suffix. For example, Sr. or Jr."
}
},
{
"Name": "[Title]",
"DataType": "NVARCHAR(8)",
"Nullable": true,
"Persisted": false,
"ExtendedProperties": {
"MS_Description": "A courtesy title. For example, Mr. or Ms."
}
}
],
"Indexes": [
{
"Name": "[AK_Person_rowguid]",
"CompressionType": "NONE",
"PrimaryKey": false,
"Unique": true,
"UniqueConstraint": false,
"Clustered": false,
"ColumnStore": false,
"FillFactor": 0,
"IndexColumns": "[rowguid]",
"ExtendedProperties": {
"MS_Description": "Unique nonclustered index. Used to support replication samples."
}
},
{
"Name": "[IX_Person_LastName_FirstName_MiddleName]",
"CompressionType": "NONE",
"PrimaryKey": false,
"Unique": false,
"UniqueConstraint": false,
"Clustered": false,
"ColumnStore": false,
"FillFactor": 0,
"IndexColumns": "[LastName],[FirstName],[MiddleName]"
},
{
"Name": "[PK_Person_BusinessEntityID]",
"CompressionType": "NONE",
"PrimaryKey": true,
"Unique": true,
"UniqueConstraint": false,
"Clustered": true,
"ColumnStore": false,
"FillFactor": 0,
"IndexColumns": "[BusinessEntityID]",
"ExtendedProperties": {
"MS_Description": "Primary key (clustered) constraint Clustered index created by a primary key constraint."
}
}
],
"XmlIndexes": [
{
"Name": "PXML_Person_AddContact",
"Column": "AdditionalContactInfo",
"IsPrimary": true
},
{
"Name": "PXML_Person_Demographics",
"Column": "Demographics",
"IsPrimary": true
},
{
"Name": "XMLPATH_Person_Demographics",
"Column": "Demographics",
"IsPrimary": false,
"PrimaryIndex": "PXML_Person_Demographics",
"SecondaryIndexType": "PATH"
},
{
"Name": "XMLPROPERTY_Person_Demographics",
"Column": "Demographics",
"IsPrimary": false,
"PrimaryIndex": "PXML_Person_Demographics",
"SecondaryIndexType": "PROPERTY"
},
{
"Name": "XMLVALUE_Person_Demographics",
"Column": "Demographics",
"IsPrimary": false,
"PrimaryIndex": "PXML_Person_Demographics",
"SecondaryIndexType": "VALUE"
}
],
"ForeignKeys": [
{
"Name": "[FK_Person_BusinessEntity_BusinessEntityID]",
"Columns": "[BusinessEntityID]",
"RelatedTableSchema": "[Person]",
"RelatedTable": "[BusinessEntity]",
"RelatedColumns": "[BusinessEntityID]",
"CascadeOnDelete": false,
"CascadeOnUpdate": false,
"ExtendedProperties": {
"MS_Description": "Foreign key constraint referencing BusinessEntity.BusinessEntityID."
}
}
],
"ExtendedProperties": {
"MS_Description": "Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts."
}
}
Supported concepts
- Schema
- Table compression
- Should Apply Expression
- All column data types
- Check and column expressions
- Persisted columns
- Indexes and statistics
- Optionally update index fill factor at the table or index level (to allow slow rolling the true up)
- Foreign keys
- Table level check constraints
- Full text indexes
- Custom properties fully supported at the table level and all components.
If you do not require a property, for example, table compression, simply do not include it in the definition. In most cases, as long it is not something that is required (like name), you can reasonably expect it to default as you would think. If there is any doubt, you can always look at the update logic.
Custom property support
You can project your own metadata on top of the standard table definition by adding custom properties and types to the table schema. This allows you to do things like add your own documentation property to each table. When you view a table in SchemaHammer you would see this documentation property like it just another property of the table. Upon quenching the table, that property will become available as a TableSchema script token for you to handle in a custom manner at runtime (ie, you could add this documentation as an extended property to the table in question).