Sql Server中与表相关的Sql语句模板

创建时间：2012年01月03日 16:02 阅读次数：(5192)

分享到：

以下是利用sql语句对表，列，约束，主键进行操作的语句模板，来源于msdn。具体语法约定请参考下面链接：
Transact-SQL 语法约定 (Transact-SQL)

创建表sql语句模板：

IF OBJECT_ID('<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
<columns_in_primary_key, , c1 > <column1_datatype, , int > <column1_nullability,, NOT NULL >,
<column2_name, sysname, c2 > <column2_datatype, , char(10) > <column2_nullability,, NULL >,
<column3_name, sysname, c3 > <column3_datatype, , datetime > <column3_nullability,, NULL >,
CONSTRAINT <contraint_name, sysname, PK_sample_table > PRIMARY KEY (<columns_in_primary_key, , c1 >)
)
GO

新增列的sql语句模板：

IF OBJECT_ID('<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
column1 int NOT NULL,
column2 char(10) NULL
)
GO

-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
ADD <new_column_name, sysname, column3 > <new_column_datatype,, datetime > <new_column_nullability,, NULL >
GO

新增约束的sql语句模板：

IF OBJECT_ID('<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
column1      int      NOT NULL,
<constraint_column_name,sysname,phone >        char(8)  NOT NULL
)
GO

-- Add a new CHECK CONSTRAINT to the table
ALTER TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
ADD CONSTRAINT <contraint_name, sysname, phone_chk > <constraint_type,,CHECK > (<constraint_column_name,sysname,phone > <logical_expression,,LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') >
GO

新增主键的sql语句模板：

IF OBJECT_ID('<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
<columns_in_primary_key, , column1 >      int      NOT NULL,
column2      char(8)  NOT NULL
)
GO

-- Add a new PRIMARY KEY CONSTRAINT to the table
ALTER TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
  ADD CONSTRAINT <constraint_name, sysname, PK_sample_table > PRIMARY KEY (<columns_in_primary_key, , column1 >)
GO

删除列的sql语句模板：

IF OBJECT_ID('<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
column1 int NOT NULL,
column2 char(10) NULL,
<new_column_name, sysname, column3 > datetime NULL
)
GO

-- Drop a column from the table
ALTER TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
DROP COLUMN <new_column_name, sysname, column3 >
GO

删除约束的sql语句模板：

IF OBJECT_ID('<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
column1      int      NOT NULL,
salary       money    NOT NULL CONSTRAINT <constraint_name, sysname, salary_cap > CHECK (salary < 500000)
)
GO

-- Drop CHECK CONSTRAINT from the table
ALTER TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
DROP CONSTRAINT <constraint_name, sysname, salary_cap >
GO

删除主键的sql语句模板：

IF OBJECT_ID(N'<schema_name, sysname, dbo >.<table_name, sysname, sample_table >', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
GO

CREATE TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
(
c1 int      NOT NULL,
c2 char(10) NULL,
c3 datetime NULL,
    CONSTRAINT <primary_key_name, sysname, primary_key_name > PRIMARY KEY(c1)
)
GO

ALTER TABLE <schema_name, sysname, dbo >.<table_name, sysname, sample_table >
  DROP CONSTRAINT <primary_key_name, sysname, primary_key_name >
GO

删除表的的sql语句模板：

IF EXISTS (
  SELECT *
FROM sys.tables
JOIN sys.schemas
  ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'<schema_name, sysname, your_schema_name >'
AND sys.tables.name = N'<table_name, sysname, your_table_name >'
)
  DROP TABLE <schema_name, sysname, your_schema_name >.<table_name, sysname, your_table_name >
GO

来源：
说明：所有来源为 .net学习网的文章均为原创，如有转载，请在转载处标注本页地址，谢谢！

【编辑：Wyf】

打赏