Microsoft SQL Server 2008 Bible
|
|
This section provides BNF notation for DDL statements.
Tables
The notations below are to create, modify, and drop database tables, respectively:
CREATE TABLE <table_name> ( column_name <datatype> [<column_constraint>,...] [DEFAULT <default_value>],... [<table_constraint>,...] [physical_options] )
ALTER TABLE <table_name> { <vendor_specific_add_column_clause> | <vendor_specific_alter_column_clause> | <vendor_specific_add_constraint_clause> | <vendor_specific_drop_constraint_clause> }
| Note | ALTER TABLE statement clauses vary for different implementations and can hardly be generalized. See Chapter 5 for more information. DROP TABLE <table_name> |
Indexes
The following two notations are to create and drop database indexes:
CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column_name> [ASC|DESC],...)
DROP INDEX <index_name>
Views
The notations below are to create, modify, and drop database views, respectively:
CREATE VIEW <view_name> [(column_name,...)] AS <select_statement> [WITH CHECK OPTION]
ALTER VIEW <view_name> <vendor_specific_alter_view_clause>
DROP VIEW <view_name>
Schemas
The following two notations are to create and to drop database schemas:
CREATE SCHEMA <schema_name> AUTHORIZATION <authorization_id> <create_object_statement>,... <grant_privilege_statement>,...
| Note | In Oracle, the schema_name token is invalid. You can create schemas in Oracle in your own schema only, and only with your own authorization_id. DROP SCHEMA <schema_name> RESTRICT |
| Note | The foregoing syntax is for DB2 only; Oracle and MS SQL Server don't have DROP SCHEMA statements in their syntaxes. |
Stored procedures
The BNF notation to create a stored procedure follows:
CREATE PROCEDURE <procedure_name> [<parameter_section>] <procedure_definition>
| Note | The preceding specification is generic; the actual syntax is implementation-specific. Refer to vendor-specific documentation for details. |
The following notation is to drop a stored procedure:
DROP PROCEDURE <procedure_name>
User-defined functions
The BNF notation to create a user-defined function follows:
CREATE FUNCTION <function_name> <function_definition_includes_return_statement>
| Note | The preceding specification is generic; the actual syntax is implementation-specific. Refer to vendor-specific documentation for details. |
The following notation is to drop a user-defined function:
DROP FUNCTION <function_name>
Triggers
The BNF notation to create a trigger follows:
CREATE TRIGGER <trigger_name> [BEFORE | AFTER] {INSERT | UPDATE | DELETE} ON <table_name> [FOR EACH ROW] <trigger_body>
| Note | The preceding syntax describes only basic trigger functionality; the actual implementations have more options. |
The following notation is to drop a trigger:
DROP TRIGGER [qualifier.]<trigger_name>
|
|