December 18, 2012

Microsoft SQL Server Management Studio 2012 (ssms.exe) Hangs, Not Responsive, Freezes after connecting to, servers

Problem:

Ok so today, I had strange encounter with the (Ssms.exe) Microsoft SQL Server Management Studio 2012.
After connecting to a server and then clicking on anything in the Object Explorer treeview, the program simply stopped working, it froze up, got the "Not responding" flag and I was only able to quit the the program from the task manager. By now I though that it was some kind of upgrade from Microsoft, because this morning there was alot of updates. But no, it was not the upgrade, or to tell you the truth, i don't really know if it was.
But i narrowed it down to some faulty files in my profile belonging to SSMS. after deleting them the whole thing worked again, and I was able to continue my work.

Solution 1: will reset the SSMS totaly
Solution 2: will reset the SSMS but keep your connection settings

Solution 1:

  1. Close the SSMS
  2. Delete this folder:
    %userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0
  3. Start the SSMS again

Solution 2:

  1. Close the SSMS
  2. Backup this file:
    %userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin
  3. Delete this folder:
    %userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0
  4. Start the SSMS and close it again, without connecting to any server.
  5. Restore the file:
    %userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin
  6. Start the SSMS again.
 


Indsendt af kl. 1 comment:
Etiketter: , , , , , , ,

December 7, 2012

XML Schemas (XML Type System) Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.column_xml_schema_collection_usages

column that is validated by an XML schema.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_id • • • • int The ID of the object to which this column belongs.
column_id • • • • int The ID of the column. Is unique within the object.
xml_collection_id • • • • int The ID of the collection that contains the validating XML schema namespace of the column.

TSQL

Sql 2005
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages
Sql 2008
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages
Sql 2008 R2
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages
Sql 2012
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages

Back to Top


sys.parameter_xml_schema_collection_usages

parameter that is validated by an XML schema.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_id • • • • int The ID of the object to which this parameter belongs.
parameter_id • • • • int The ID of the parameter. Is unique within the object.
xml_collection_id • • • • int The ID of the XML schema collection that contains the validating XML schema namespace of the parameter.

TSQL

Sql 2005
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages
Sql 2008
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages
Sql 2008 R2
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages
Sql 2012
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages

Back to Top


sys.xml_indexes

Returns one row per XML index.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
object_id • • • • int sys.indexes ID of the object to which this index belongs.
name • • • • sysname sys.indexes Name of the index. name is unique only within the object.
NULL = Heap
index_id • • • • int sys.indexes ID of the index. index_id is unique only within the object.
0 = Heap
1 = Clustered index
> 1 = Nonclustered index
type • • • • tinyint sys.indexes Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
type_desc • • • • nvarchar(60) sys.indexes Description of index type:
HEAP
CLUSTERED
NONCLUSTERED
XML
is_unique • • • • bit sys.indexes 1 = Index is unique.
0 = Index is not unique.
data_space_id • • • • int sys.indexes ID of the data space for this index. Data space is either a filegroup or partition scheme.
0 = object_id is a table-valued function.
ignore_dup_key • • • • bit sys.indexes 1 = IGNORE_DUP_KEY is ON.
0 = IGNORE_DUP_KEY is OFF.
is_primary_key • • • • bit sys.indexes 1 = Index is part of a PRIMARY KEY constraint.
is_unique_constraint • • • • bit sys.indexes 1 = Index is part of a UNIQUE constraint.
fill_factor • • • • tinyint sys.indexes > 0 = FILLFACTOR percentage used when the index was created or rebuilt.
0 = Default value
is_padded • • • • bit sys.indexes 1 = PADINDEX is ON.
0 = PADINDEX is OFF.
is_disabled • • • • bit sys.indexes 1 = Index is disabled.
0 = Index is not disabled.
is_hypothetical • • • • bit sys.indexes 1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.
0 = Index is not hypothetical.
allow_row_locks • • • • bit sys.indexes 1 = Index allows row locks.
0 = Index does not allow row locks.
allow_page_locks • • • • bit sys.indexes 1 = Index allows page locks.
0 = Index does not allow page locks.
using_xml_index_id • • • • int   NULL = Primary XML index.
Nonnull = Secondary XML index.
Nonnull is a self-join reference to the primary XML index.
secondary_type • • • • char(1)   Type description of secondary index:
P = PATH secondary XML index
V = VALUE secondary XML index
R = PROPERTY secondary XML index
NULL = Primary XML index
secondary_type_desc • • • • nvarchar(60)   Type description of secondary index:
PATH = PATH secondary XML index
VALUE = VALUE secondary XML index
PROPERTY = PROPERTY secondary xml indexes.
NULL = Primary XML index
has_filter   • • • bit sys.indexes 1 = Index has a filter and only contains rows that satisfy the filter definition.
0 = Index does not have a filter.
filter_definition   • • • nvarchar(max) sys.indexes Expression for the subset of rows included in the filtered index.
NULL for heap or non-filtered index.

TSQL

Sql 2005
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes
Sql 2008
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes
Sql 2008 R2
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes
Sql 2012
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes

Back to Top


sys.xml_schema_attributes

Returns a row per XML schema component that is an attribute, symbol_space of A.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id • • • • int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id • • • • int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id • • • • int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified • • • • bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name • • • • nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space • • • • char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc • • • • nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind • • • • char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc • • • • nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation • • • • char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc • • • • nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id • • • • int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id • • • • int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
is_default_fixed • • • • bit   1 = The default value is a fixed value. This value cannot be overridden in an XML instance.
0 = The default value is not a fixed value for the attribute. (default)
must_be_qualified • • • • bit   1 = The attribute must be explicitly namespace qualified.
0 = The attribute may be implicitly namespace qualified. (default)
default_value • • • • nvarchar (4000)   Default value of the attribute. Is NULL if a default value is not supplied.

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes

Back to Top


sys.xml_schema_collections

Returns a row per XML schema collection. An XML schema collection is a named set of XSD definitions. The XML schema collection itself is contained in a relational schema, and it is identified by a schema-scoped Transact-SQL name. The following tuples are unique: xml_collection_id, and schema_id and name.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_collection_id • • • • int ID of the XML schema collection. Unique within the database.
schema_id • • • • int ID of the relational schema that contains this XML schema collection.
principal_id • • • • int ID of the individual owner if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner may be specified by using the ALTER AUTHORIZATION statement to change ownership.
NULL = No alternate individual owner.
name • • • • sysname Name of the XML schema collection.
create_date • • • • datetime Date the XML schema collection was created.
modify_date • • • • datetime Date the XML schema collection was last altered.

TSQL

Sql 2005
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections
Sql 2008
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections
Sql 2008 R2
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections
Sql 2012
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections

Back to Top


sys.xml_schema_component_placements

Returns a row per placement for XML schema components.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id • • • • int ID of the XML schema component that owns this placement.
placement_id • • • • int ID of the placement. This is unique within the owning XML schema component.
placed_xml_component_id • • • • int ID of the placed XML schema component.
is_default_fixed • • • • bit 1 = The default value is a fixed value. This value cannot be overridden in an XML instance.
0 = The value can be overridden.(default)
min_occurrences • • • • int Minimum number of placed component occurs.
max_occurrences • • • • int Maximum number of placed component occurs.
default_value • • • • nvarchar (4000) Default value if one is supplied. Is NULL if a default value is not supplied.

TSQL

Sql 2005
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements
Sql 2008
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements
Sql 2008 R2
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements
Sql 2012
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements

Back to Top


sys.xml_schema_components

Returns a row per component of an XML schema. The pair (collection_id, namespace_id) is a compound foreign key to the containing namespace. For named components, the values for symbol_space, name, scoping_xml_component_id, is_qualified, xml_namespace_id, xml_collection_id are unique.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id • • • • int Unique ID of the XML schema component in the database.
xml_collection_id • • • • int ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id • • • • int ID of the XML namespace within the collection.
is_qualified • • • • bit 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name • • • • nvarchar (4000) Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space • • • • char(1) Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc • • • • nvarchar (60) Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind • • • • char(1) Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc • • • • nvarchar (60) Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation • • • • char(1) Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc • • • • nvarchar (60) Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id • • • • int ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id • • • • int Unique ID of the scoping component. NULL if there is none (global scope).

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components

Back to Top


sys.xml_schema_elements

Returns a row per XML schema component that is a Type, symbol_space of E.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id • • • • int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id • • • • int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id • • • • int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified • • • • bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name • • • • nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space • • • • char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc • • • • nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind • • • • char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc • • • • nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation • • • • char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc • • • • nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id • • • • int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id • • • • int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
is_default_fixed • • • • bit   1 = Default value is a fixed value. This value cannot be overridden in XML instance.
0 = Default value is not a fixed value for the element. (default).
is_abstract • • • • bit   1 = Element is abstract and cannot be used in an instance document. A member of the substitution group of the element must appear in the instance document.
0 = Element is not abstract. (default).
is_nillable • • • • bit   1 = Element is nillable.
0 = Element is not nillable. (default)
must_be_qualified • • • • bit   1 = Element must be explicitly namespace qualified.
0 = Element may be implicitly namespace qualified. (default)
is_extension_blocked • • • • bit   1 = Replacement with an instance of an extension type is blocked.
0 = Replacement with extension type is allowed. (default)
is_restriction_blocked • • • • bit   1 = Replacement with an instance of a restriction type is blocked.
0 = Replacement with restriction type is allowed. (default)
is_substitution_blocked • • • • bit   1 = Instance of a substitution group cannot be used.
0 = Replacement with substitution group is permitted. (default)
is_final_extension • • • • bit   1 = Replacement with an instance of an extension type is disallowed.
0 = Replacement in an instance of an extension type is allowed. (default)
is_final_restriction • • • • bit   1 = Replacement with an instance of a restriction type is disallowed.
0 = Replacement in an instance of a restriction type is allowed. (default)
default_value • • • • nvarchar (4000)   Default value of the element. NULL if a default value is not supplied.

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements

Back to Top


sys.xml_schema_facets

Returns a row per facet (restriction) of an xml-type definition (corresponds to sys.xml_types).
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id • • • • int ID of XML component (type) to which this facet belongs.
facet_id • • • • int ID (1-based ordinal) of facet, unique within component-id.
kind • • • • char(2) Kind of facet:
LG = Length
LN = Minimum Length
LX = Maximum Length
PT = Pattern (regular expression)
EU = Enumeration
IN = Minimum Inclusive value
IX = Maximum Inclusive value
EN = Minimum Exclusive value
EX = Maximum Exclusive value
DT = Total Digits
DF = Fraction Digits
WS = White Space normalization
kind_desc • • • • nvarchar (60) Description of kind of facet:
LENGTH
MINIMUM_LENGTH
MAXIMUM_LENGTH
PATTERN
ENUMERATION
MINIMUM_INCLUSIVE_VALUE
MAXIMUM_INCLUSIVE_VALUE
MINIMUM_EXCLUSIVE_VALUE
MAXIMUM_EXCLUSIVE_VALUE
TOTAL_DIGITS
FRACTION_DIGITS
WHITESPACE_NORMALIZATION
is_fixed • • • • bit 1 = Facet has a fixed, prespecified value.
0 = No fixed value. (default)
value • • • • nvarchar (4000) Fixed, pre-specified value of the facet.

TSQL

Sql 2005
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets
Sql 2008
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets
Sql 2008 R2
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets
Sql 2012
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets

Back to Top


sys.xml_schema_model_groups

Returns a row per XML schema component that is a Model-Group, symbol_space of M..
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id • • • • int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id • • • • int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id • • • • int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified • • • • bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name • • • • nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space • • • • char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc • • • • nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind • • • • char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc • • • • nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation • • • • char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc • • • • nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id • • • • int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id • • • • int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
compositor • • • • char(1)   Compositor kind of group:
A = XSD Group
C = XSD Group
S = XSD Group
compositor_desc • • • • nvarchar (60)   Description of compositor kind of group:
XSD_ALL_GROUP

XSD_CHOICE_GROUP

XSD_SEQUENCE_GROUP

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups

Back to Top


sys.xml_schema_namespaces

Returns a row per XSD-defined XML namespace. The following tuples are unique: collection_id, namespace_id, and collection_id, and name.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_collection_id • • • • int ID of the XML schema collection that contains this namespace.
name • • • • nvarchar (4000) Name of XML namespace. Blank name indicates no target namespace.
xml_namespace_id • • • • int 1-based ordinal that uniquely identifies the XML namespace in the database.

TSQL

Sql 2005
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces
Sql 2008
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces
Sql 2008 R2
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces
Sql 2012
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces

Back to Top


sys.xml_schema_types

Returns a row per XML schema component that is a Type, symbol_space of T.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id • • • • int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id • • • • int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id • • • • int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified • • • • bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name • • • • nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space • • • • char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc • • • • nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind • • • • char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc • • • • nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation • • • • char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc • • • • nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id • • • • int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id • • • • int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
is_abstract • • • • bit   1 = Type is an abstract type. All instances of an element of this type must use xsi:type to indicate a derived type that is not abstract.
0 = Type is not abstract. (default)
allows_mixed_content • • • • bit   1 = Mixed content is allowed
0 = Mixed content is not allowed. (default)
is_extension_blocked • • • • bit   1 = Replacement with an extension of the type is blocked in instances when the block attribute on the complexType definition or the blockDefault attribute of the ancestor element information item is set to "extension" or "#all".
0 =Replacement with extension is not blocked.
is_restriction_blocked • • • • bit   1 = Replacement with a restriction of the type is blocked in instances when the block attribute on the complexType definition or the blockDefault attribute of the ancestor element information item is set to "restriction" or "#all".
0 = Replacement with restriction is not blocked. (default)
is_final_extension • • • • bit   1 = Derivation by extension of the type is blocked when the final attribute on the complexType definition or the finalDefault attribute of the ancestor element information item is set to "extension" or "#all".
0 = Extension is allowed. (default)
is_final_restriction • • • • bit   1 = Derivation by restriction of the type is blocked when the final attribute on the simple or complexType definition or the finalDefault attribute of the ancestor element information item is set to "restriction" or "#all".
0 = Restriction is allowed. (default)
is_final_list_member • • • • bit   1 = This simple type cannot be used as the item type in a list.
0 = This type is a complex type, or it can be used as list item type. (default)
is_final_union_member • • • • bit   1 = This simple type cannot be used as the member type of a union type.
0 = This type is a complex type. or it can be used as union member type. (default)

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types

Back to Top


sys.xml_schema_wildcard_namespaces

Returns a row per enumerated namespace for an XML schema wildcard.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id • • • • int ID of the XML schema component (wildcard) to which this applies.
namespace • • • • nvarchar(4000) Name or URI of the namespace that is used by the XML wildcard.

TSQL

Sql 2005
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces
Sql 2008
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces
Sql 2008 R2
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces
Sql 2012
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces

Back to Top


sys.xml_schema_wildcards

Returns a row per XML schema component that is an Attribute-Wildcard (kind of V) or Element-Wildcard (kind of W), both with symbol_space of N.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id • • • • int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id • • • • int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id • • • • int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified • • • • bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name • • • • nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space • • • • char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc • • • • nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind • • • • char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc • • • • nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation • • • • char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc • • • • nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id • • • • int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id • • • • int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
process_content • • • • char(1)   Indicates how contents are processed.
S = Strict validation (must validate)
L = Lax validation (validate if possible)
P = Skip validation
process_content_desc • • • • nvarchar(60)   Description of how contents are processed:
STRICT_VALIDATION

LAX_VALIDATION

SKIP_VALIDATION
disallow_namespaces • • • • bit   0 = Namespaces enumerated in sys.xml_schema_wildcard_namespaces2 are the only ones allowed.
1 = Namespaces are the only ones disallowed.

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards

Back to Top


Indsendt af kl. No comments:
Etiketter: , , , ,
Subscribe to: Posts (Atom)

Total Pageviews