AdminSky.org

Delete an entire XML schema collection and all of its components.

Syntax
      DROP XML SCHEMA COLLECTION [relational_schema.]sql_identifier

Key
   relational_schema   The relational schema name.
   sql_identifier      The XML schema collection to drop.

You cannot drop an XML schema collection if it is associated with any xml type parameter or column, specified in any table constraints or referenced in a schema-bound function / stored procedure (CREATE FUNCTION … WITH SCHEMABINDING)

Example

DROP XML SCHEMA COLLECTION MySchemaCollection
GO

Add new schema components to an existing XML schema collection.

Syntax
      ALTER XML SCHEMA COLLECTION [relational_schema.]sql_identifier ADD 'Schema Component'

Key
   relational_schema  The relational schema name.

   sql_identifier     SQL identifier for the XML schema collection.

   Schema Component   The schema component to insert.

This command can be used to add an entirely new XML schema, (namespace not currently in the XML schema collection), or add a new component to an existing namespace in the collection.

Example

ALTER XML SCHEMA COLLECTION MyCollection ADD '
  <schema xmlns="http://www.w3.org/2001/XMLSchema"
         targetNamespace="http://MySchema/my_xml_schema">
     <element name="anotherElement" type="byte"/>
 </schema>'

Import schema components into a database.

Syntax
      CREATE XML SCHEMA COLLECTION [relational_schema.]sql_identifier AS Expression

Key
   relational_schema  The relational schema name.

   sql_identifier     SQL identifier for the XML schema collection.

   Expression         A string constant or scalar variable.
                      (varchar, varbinary, nvarchar, nvarbinary, or xml type)

The variable in the example below is of nvarchar(max) type. The variable can also be of xml data type, in which case, it is implicitly converted to a string.

Example

DECLARE @MySchemaCollection nvarchar(max)
Set @MySchemaCollection  = N' copy the schema collection here'
CREATE XML SCHEMA COLLECTION MyCollection AS @MySchemaCollection

Remove one or more views from the current database.

Syntax
      DROP VIEW [schema.] view [...,n ] [;]

Key
   view    Name of the view to be dropped.

DROP VIEW can be executed against indexed views.

Example

DROP VIEW MyView;

Modify an existing view.

Syntax
      ALTER VIEW [schema.] view [ (column [,...n] ) ]
         [WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA] [,...n] ]
            AS select_statement [;]
               [WITH CHECK OPTION]

Key
   view    Name of the view to be altered.

   column  Name to be used for a column in a view.
           defaults to the same name as the column(s) in the SELECT statement.

   select_statement The view definition. This SELECT statement can reference
                    more than one table/view. Appropriate permissions are required.
                    cannot include a COMPUTE or ORDER BY clause (unless SELECT TOP..)

   CHECK OPTION   Enforce the criteria set with select_statement even
                  when modifying the data. This only affects the view not the underlying table.

   ENCRYPTION     Encrypts the text of the CREATE VIEW statement. 

   SCHEMABINDING  Bind the view to the schema of the underlying table or tables.

   VIEW_METADATA  Return metadata information about the view to client APIs
                  this allows updatable client-side cursors to address the view.

If a view is dropped and re-created, any GRANT or similar permission statements applied to the view must be re-entered.
Using ALTER VIEW will retain all the permission settings.

Example

ALTER VIEW emp_view
AS
SELECT c.FirstName, c.LastName, s.StaffID
FROM MySchema.Staff S
JOIN MySchema.Customers c
on c.CustomerID = c.StaffID ;
GO