We do not recommend specifying USER as a level 0 type when you apply an extended property to a database object, because this can cause name resolution ambiguity.
#Adventureworks2012 logical schema how to#
For more information about how to replicate database objects, see Publish Data and Database Objects. If you add or modify an extended property after the initial synchronization, the change is not replicated. Replicating Extended PropertiesĮxtended properties are replicated only in the initial synchronization between the Publisher and the Subscriber. If all object types and names are null, the property belongs to the current database itself.Įxtended properties are not allowed on system objects, objects outside the scope of a user-defined database, or objects not listed in Arguments as valid inputs.Įxtended properties are not allowed on memory-optimized tables.
For example, when you add an extended property to a table column (level 2), you must also specify the table name (level 1) that contains the column and the schema (level 0) that contains the table. References to an object in one level must be qualified with the names of the higher level objects that own or contain them. Extended properties can be defined for objects at any of these levels. Level 1 objects are contained in a schema or user scope, and level 2 objects are contained by level 1 objects. Level 0 is the highest level and is defined as objects that are contained at the database scope.
#Adventureworks2012 logical schema code#
Return Code Valuesįor specifying extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. level2_object_name is sysname, with a default of NULL. Is the name of the level 2 object type specified. Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.
level2_object_type is varchar(128), with a default of NULL. level1_object_name is sysname, with a default of NULL. Is the name of the level 1 object type specified. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SEQUENCE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL. level1_object_type is varchar(128), with a default of NULL. level0_object_name is sysname with a default of NULL. Is the name of the level 0 object type specified. For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type. The ability to specify TYPE as level-0 type will be removed in a future version of SQL Server. For example, when defining an extended property on a table, specify the schema of the table instead of a user name. The ability to specify USER as a level 0 type in an extended property of a level 1 type object will be removed in a future version of SQL Server. Valid inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE, and NULL. level0_object_type is varchar(128), with a default of NULL. The size of value cannot be more than 7,500 bytes. value is sql_variant, with a default of NULL. Is the value to be associated with the property. Names can also include blank or non-alphanumeric character strings, and binary values. property_name is sysname and cannot be NULL. Applies to: SQL Server (all supported versions) Azure SQL DatabaseĪdds a new extended property to a database object.