sql server 常用语句

--新增字段

use [MEASDatabase]
go
if not exists(select * from syscolumns where id=object_id('DictCheckItems') and name='OfDepartName') begin
ALTER TABLE DictCheckItems ADD OfDepartName VARCHAR(50) default '';
end
go

--修改字段长度

alter table DictCheckItems alter column OfDepartName varchar(60)

 --新增表结构

IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = object_id('TableInfo')
AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
CREATE TABLE [dbo].TableInfo(
[ID] [varchar](50) NOT NULL,
[PARAMTYPE] [varchar](50) NOT NULL,
[PARAMNAME] [varchar](100) NOT NULL,
PARAMITEMS [varchar](500) not NULL
CONSTRAINT [PK_DICT_PARAM] PRIMARY KEY CLUSTERED
([ID] ASC
)ON [PRIMARY]
)

GO

--删除字段

alter table 表名 drop column 字段名;

--修改字段名称

exec sp_rename 'UserMEAS.PermissionID' , 'RoleCode', 'column'

eg:

if exists(select * from syscolumns where id=object_id('UserMEAS') and name='PermissionID') begin
exec sp_rename 'UserMEAS.PermissionID' , 'RoleCode', 'column'
end
go

if exists(select * from syscolumns where id=object_id('UserMEAS') and name='RoleId') begin
alter table UserMEAS drop column [RoleId];
end
go