修改数据库的常用SQL语句

admin管理员组

文章数量:1565284

2024年7月26日发(作者：)

修改数据库的常用SQL语句

修改数据库的常用SQL语句

1：删除表

if exists (select * from ects where id =

object_id(N'[dbo].[TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ TableName]

GO

2：创建表

CREATE TABLE [TableName] (表结构) ON [PRIMARY]

GO

例：

--创建虚拟通道表VirtualChannel，包括OrderID,ChannelName两列。

if exists (select * from ects where id =

object_id(N'[dbo].[VirtualChannel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[VirtualChannel]

GO

CREATE TABLE [VirtualChannel] (

[OrderID] [int] IDENTITY (1, 1) NOT NULL ,

[ChannelName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

CONSTRAINT [PK_VirtualChannel] PRIMARY KEY CLUSTERED

(

[OrderID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

3：删除视图

if exists (select * from ects where id

object_id(N'[dbo].[ViewName]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[ViewName]

=

GO

4：创建视图

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW me

AS

视图SQL语句

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

例：

--创建视图ObjectView

if exists (select * from ects where id =

object_id(N'[dbo].[ObjectView]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[ObjectView]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: View View Script Date: 2004-7-14 20:52:38 ******/

CREATE VIEW View

AS

SELECT , Id,

ta, dTable,

Time, /doc/,,

evel, ption, ,

, ff, rol,

lag FROM able INNER JOIN

lData ON =

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

5：删除触发器

if exists (select * from ects where id

object_id(N'[dbo].[TriggerName]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)

drop trigger [dbo].[ TriggerName]

=

GO

6：创建触发器

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[TableName]

FOR INSERT, UPDATE, DELETE

AS

触发器SQL语句

GO

例：

--创建触发器在AlarmHistory表中创建AlarmHistory_Insert

if exists (select * from ects where id =

object_id(N'[dbo].[AlarmHistory_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') =

1)

drop trigger [dbo].[AlarmHistory_Insert]

GO

CREATE TRIGGER AlarmHistory_Insert ON istory

FOR INSERT

AS

insert into AlarmNotify

select

o,n,Name,ime,,evel,o,

lg,ext,uf,ata,d,serName,

ime,

from Inserted a,alarmtable b where =

and = 0

GO

7：删除存储过程

if exists (select * from ects where id =

object_id(N'[dbo].[ ProcedureName]') and OBJECTPROPERTY(id, N'IsProcedure') =

1) drop procedure [dbo].[ ProcedureName]

GO

8：创建存储过程

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE ProcedureName

存储过程SQL语句

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

例：

--创建存储过程gy

if exists (select * from ects where id = object_id(N'[dbo].[gy]') and

OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[gy]

GO

CREATE PROCEDURE gy

@TableName Varchar(100) --数据库表名

AS

begin

SELECT

表名=case when er=1

/doc/, else '' end,

字段序号=er,

then

字段名=/doc/,,

标识=case when

COLUMNPROPERTY( ,/doc/,,'IsIdentity'

)=1 then '√'else '' end, 主键=case when exists(SELECT 1 FROM sysobjects where

xtype='PK' and name in ( SELECT name FROM sysindexes WHERE indid in(

SELECT indid FROM sysindexkeys WHERE id = AND colid= ))) then

'√' else '' end,

类型=/doc/,,

占用字节数=,

长度

=COLUMNPROPERTY(,/doc/,,'PRECISI

ON'),

小数位数

=isnull(COLUMNPROPERTY(,/doc/,,'Sc

ale'),0),

允许空=case when able=1 then '√'else '' end,

默认值=isnull(,''),

字段说明=isnull(g.[value],'')

FROM syscolumns a

left join systypes b on =ype

inner join sysobjects d on = and ='U'

left

and

join /doc/,<>'dtproperties'

syscomments e on lt=

left join sysproperties g on = and =d

where /doc/,= @TableName --表名

order by ,er

end

GO

说明：要创建表、视图、触发器和存储过程，先做一次删除操作，避免出现重复的情

况。创建这些，一般是在SQL SERVER企业管理器中创建，在生成SQL语句时，只需复

制某一对象，粘贴到文本就可以了。

9：在表中增加列

if col_length('[TableName]', '[ColumnName]') is NULL

ALTER TABLE TableName ADD [ColumnName] [Type] [NULLnot NULL]

DEFAULT (0) WITH VALUES

GO

写该列的描述

execute sp_addextendedproperty 'MS_Description',

'描述内容',

'user', 'dbo', 'table', 'Table', 'column', 'ColumnName'

GO

例：

--在Station表中增加一列

if col_length('Station', 'StaAttribute') is NULL

ALTER TABLE Station ADD StaAttribute int not NULL

DEFAULT (0) WITH VALUES

GO

--写描述

execute sp_addextendedproperty 'MS_Description',

'基站属性：0电表装在倒换开头之前；1电表在装倒换开关之后;其它预留', 'user',

'dbo', 'table', 'Station', 'column', 'StaAttribute'

GO

10：修改某一列

alter table TableName alter column [ColumnName] [Type] [NULLnot NULL]

GO

例：

--修改Device表的strid

alter table Device alter column StrID Varchar(20) NULL

GO

11：删除某一列

如果表中存在关于该列的约束，要先删除约束

ALTER TABLE TableName drop CONSTRAINT constraint

ALTER TABLE TableName drop column ColumnName

GO

例：

--删除Device表中是AmmeterPosition

ALTER TABLE Device drop CONSTRAINT DF_Device_AmmeterPosition

ALTER TABLE Device drop column AmmeterPosition

GO

12：删除索引

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_NewAlarmMask')

ALTER TABLE [dbo].[NewAlarmMask] DROP [IX_NewAlarmMask]

--删除是约束的索引

DROP INDEX [dbo].[NewAlarmMask].[IX_NewAlarmMask]

--删除非约束的索引GO

本文标签： 创建删除电表语句