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

--判断是否存在某条数据

if not exists (select * from [DictPublic] where Type='默认密码')
insert into DictPublic
(Id, Type, TypeCode, TypeName, TypeLevel, IsEnable, Remark, PTypeCode)
values(NEWID(),'默认密码','PassWord','666666','1','1','用户默认密码','')
go

--新增视图
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id(N'[VIEW_USERINFO]') AND OBJECTPROPERTY(id, N'IsView') = 1)
   DROP View [VIEW_USERINFO]
GO
CREATE VIEW [dbo].[VIEW_USERINFO]
AS 
SELECT U.ID, LoginName, TrueName, PassWord, OrganizationCode, 
Organization, DepartMent, SystemClass, UserType, StuffID, 
UserParam, U.RoleCode
RoleName, RoleRemark, PermissionCodes
FROM [dbo].[UserMEAS] U,[dbo].[UserRole]
WHERE U.RoleCode=UserRole.RoleCode  
GO

--left join
ALTER VIEW [dbo].[VIEW_USERINFO]
AS 
SELECT U.ID, LoginName, TrueName, PassWord, OrganizationCode, 
Organization, DepartMent, SystemClass, UserType, StuffID, 
UserParam, U.RoleCode,
RoleName, RoleRemark, PermissionCodes
FROM [dbo].[UserMEAS] U
--,[dbo].[UserRole]
--WHERE U.RoleCode=UserRole.RoleCode  
LEFT JOIN [dbo].[UserRole] 
ON U.RoleCode=UserRole.RoleCode  

GO