目录:
以下案例围绕表Test2020开展
IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID('Test2020') AND type = 'U' ) CREATE TABLE [Test2020] ( [A] [uniqueidentifier] PRIMARY KEY CLUSTERED NOT NULL , [B] [varchar](50) , [C] [varchar](50) ) ; GO
2.如果不存在该字段,则新增(D为字段名)
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('Test2020') AND name = 'D') ALTER TABLE [Test2020] ADD [D] [varchar] (50) ; GO
表字段处理
-- 增加字段 alter table Test2020 add filedName1 char(200) -- 删除字段 ALTER TABLE table_NAME DROP COLUMN column_NAME -- 修改字段类型 ALTER TABLE table_name ALTER COLUMN column_name new_data_type -- sp_rename 改名 EXEC sp_rename '[dbo].[Table_1].[filedName1]', 'filedName2', 'COLUMN' -- EXEC sp_rename '[dbo].[Test2020].[B1]', 'B', 'COLUMN' -- ALTER TABLE Test2020 ALTER COLUMN filedName1 varchar(50)
IF EXISTS (SELECT * FROM sysobjects WHERE id =OBJECT_ID('v_test2020') AND type = 'V' ) DROP VIEW v_test2020 GO /* 创建人:chenze 创建时间: 2020-05-01 说明: 获取测试信息列表 修改人:hjp 修改时间:2020-05-15 修改说明:增加...操作 */ CREATE VIEW v_test2020 AS SELECT A,B,C FROM Test2020 GO
IF ( SELECT OBJECT_ID('uspTest2020')) IS NOT NULL DROP PROC uspTest2020 GO /* 创建人:chenze 创建时间: 2020-05-01 说明: 获取测试信息列表 修改人:hjp 修改时间:2020-05-15 修改说明:增加...操作 */ CREATE PROCEDURE uspTest2020 @A uniqueidentifier AS BEGIN SELECT * FROM Test2020 WHERE A=@A END GO
IF (SELECT OBJECT_ID('[fn_Test2020]')) IS NOT NULL DROP FUNCTION [fn_Test2020] GO /* 创建人:chenze 创建时间: 2020-05-01 说明: 获取测试信息列表 修改人:hjp 修改时间:2020-05-15 修改说明:增加...操作 */ CREATE FUNCTION [fn_Test2020] (@a INT ) RETURNS VARCHAR(10) AS BEGIN RETURN CASE @a WHEN 0 THEN '00000000' WHEN 1 THEN '11111111' END END GO
执行存储过程
DECLARE @return_value int EXEC @return_value = [dbo].[uspTest2020] @A = 'D196177C-F25E-49BB-A4ED-1AC55E8553D4' SELECT 'Return Value' = @return_value
执行函数
select dbo.[fn_Test2020](0)