l Create a database table
The following example shows a full table definition of the three tables (JOBS, Employee and Publishers) created in the PUBS database, which contains all constraint definitions.
CREATE TABLE Name
(
Field name 1 Data type is allowed to be NULL,
Field name 2 data type is allowed to be NULL
)
E.g:
CREATE TABLE JOBS
(
JOB_ID Smallint Identity (1, 1) Primary Key Clustered,
Job_Desc VARCHAR (50) Not Null Default 'New Position - Title Not Formalized Yet',
Min_lvl tinyint not null check (min_lvl> = 10),
Max_lvl tinyint not null check (max_lvl <= 250)
)
SQLServer2000 SQLANYwhere
l Modify the database table
E.g:
Alter Table Jobs
(
JOB_ID Smallint Identity (1, 1) Primary Key Clustered,
Job_Desc VARCHAR (50) Not Null Default 'New Position - Title Not Formalized Yet',
Min_lvl tinyint not null check (min_lvl> = 10),
Max_lvl tinyint not null check (max_lvl <= 250)
)
SQLServer2000 SQLANYwhere
l Add primary key for the table
ALTER TABLE Name
Add constraint [Profile Name (Default PK_ "prefix)] PRIMARY Key Clustered (Is it a cluster index)
(
[Field Name 1], [Field Name 2], [Field Name ...]
)
Go
E.g:
Alter Table [DBO]. [M_p_checkprepotency] with nocheck
Add constraint [PK_M_P_CHECKPREPOTENCY] PRIMARY Key Clustered
(
[GUID],
[Sequencenum]
)
Go
SQLServer2000 SQLANYwhere
l new fields
ALTER TABLE Name
Add field name data type properties
Go
E.g:
SQLSERVER2000 SQLANYWHEREALTER TABLE SKILL Add Skill_Description Char (254)
l Delete field
SQLSERVER2000 SQLANYWHEREALTER TABLE Name DROP field name
l Create an index
CREATE INDEX Index Name ON Name (Field Name 1, Field Name 2, Field Name 3 ...)
E.g:
Create Index [ix_m_p_checkprepotency] on [dbo]. [M_p_checkprepotency] ([UnitCoding], [FamilyId], [PersonnelCoding]) on [primary]
Go
SQLServer2000 SQLANYwhere
l Delete index
IF exists (select name from sysindexes where name = 'index ")
Drop Index Table Name. Index Name
Go
E.g:
If EXISTS (SELECT NAME from sysindexes where name = 'au_id_ind') Drop Index Authors.au_Id_ind
Go
SQLServer2000 SQLANYwhere
l Delete view
If EXISTS (SELECT TABLE_NAME from Information_Schema.Views
Where table_name = 'view name')
Drop View View Name
Go
E.g:
If EXISTS (SELECT TABLE_NAME from Information_Schema.Views
Where Table_name = 'Titles_View')
Drop View Titles_View
Go
SQLServer2000 SQLANYwhere
l Add foreign keys and cascades for the fields of the table.
ALTER TABLE Name 1 Add
Constraint [fk_ foreign key name] Foreign Key
(
[Field name]
References Table 2
(
[Field name]
)
On Delete Cascade - Cascade Delete
On Update Cascade - Cascading Update
Go
ConsTRAINT [fk_m_p_checkprepotency_o_prepotencyResult] Foreign Key
(
[CheckPrepotencyResult]
References [dbo]. [O_prepotencyResult] (
[CheckResultid]
ON Update Cascade
Go
E.g:
Alter Table [DBO]. [M_p_checkprepotency] add
ConsTRAINT [FK_M_P_CHECKPREPOTENCY_M_PERSONNELINFO] FOREIGN KEY
(
[Guid]
) References [dbo]. [M_personnelinfo] (
[Guid]
) On Delete Cascade ON Update Cascade,
ConsTRAINT [fk_m_p_checkprepotency_o_prepotencyResult] Foreign Key
(
[CheckPrepotencyResult]
References [dbo]. [O_prepotencyResult] (
[CheckResultid]
ON Update Cascade
Go
SQLServer2000 SQLANYwhere
l Modify the column data type / size / whether it is allowed to null
S2K:
SQLANYwhere:
SQLSERVER2000 RALTER TABLE S_ANACHOR ALTER Column Anchorname Varchar (100) Not NullsqlanywhereAlTer Table S_Anchor Modify Anchorname VARCHAR (100) Not Null
l Modify column name
SQLSERVER2000 SQLANYWHEREALTER TABLE SKILL RENAME SKILL_TYPE TO CLASSIFICTION
l Modify the table name
SQLSERVER2000 SQLANYWHEREALTER TABLE SKILL RENAME Qualification
l Delete columns
SQLServer2000 SQLANYWHEREALTER TABLE SKILL DROP CLASSIFICATION
l Determine if the primary key of a table exists
SQLServer2000 SQLANYWHEREIF (SELECT Primary_Root from S_Anchor 'and Table_Type =' Base ') <> 0 If PRIMARY_ROOT does not specify all primary keys in the table existing primary key L delete tables
SQLSERVER2000 SQLANYWHEREALTER TABLE SKILL DELETE PRIMARY Key
l Add a table primary key
SQLSERVER2000 SQLANYWHEREALTER TABLE SKILL Add Primary Key, "Skill_Type")
l Judge whether a field exists in a table
SqlServer2000 SqlAnyWhereif not exists (select * from syscolumn sc join systable st on sc.table_id = st.table_id where st.table_name = 'm_p_PregnancyInfo' and sc.column_name = 'PunishSequenceNum')
l Judge whether a table exists
SQLSERVER2000 SQLANYWHEREIF NOTSTS (SELECT * from Systable Where Table_name = 's_anchor' and Table_Type = 'Base')
l Judge whether a view exists
SqlServer2000if not exists (select * from dbo.sysobjects where xtype = 'V' and name = 'v_s_Marriage') SqlAnyWhereif not exists (select * from systable where table_name = 's_Anchor' and table_type = 'VIEW')
l Judge whether a global temporary table exists
SQLServer2000 SQLANYWHEREIF NOTSTS (SELECT * from Systable Where Table_name = 's_anchor' and Table_Type = 'GBL TEMP')
l Judge whether a trigger exists
SQLServer2000 SQLANYWHEREIF NOTSTS (SELECT *WHERE TABLE_NAME = 's_anchor' and Table_Type = 'TR')
l Judging whether a function / stored procedure exists
SQLSERVER2000 SQLANYWHEREIF EXISTS (SELECT * from sysprocedure where proc_name = 'sp_seeaboutservicecar')
l Determines if a field of a table exists
SqlServer2000 SqlAnyWhereif exists (select * from syscolumn where column_name = 'DogID' and table_id = (select table_id from systable where table_name = 's_Users' and table_type = 'BASE')) l add a field for a table
SQLServer2000 SQLANYWHEREALTER TABLE S_USERS Add Dogid Char (32) NULL
