对于mssql中的对象,都存储在系统表sysobjects中,if exists(select * from sysobjects where upper(name)=N'object_name') alter function/procedure else create function/procedure 对于表一般不说有此表,就修改没有就添加 不太好做 虽然表中的所有列都保存在当前数据库的syscolumns表中但一个一个添加不太好做, 要想做就是: [code=SQL] if exists(select * from syscolumns where id=object_id('表名') and name='列名') alter table 表名 add 列名 类型 ... [code=SQL]
不好意思上边写错了,应该是不存在这个列就新加: 比如在"表名"表中不存在"列名"列就新加该列if not exists(select * from syscolumns where id=object_id('表名') and name='列名') alter table 表名 add 列名 类型 ...
创建表:USE [XXX] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U')) DROP TABLE [dbo].[TableName]Create Table......------------------------------------------------ 创建存储过程:USE [XXX] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[ProcName]Create Proc ....-------------------------------------------------- 创建函数:USE [XXX] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[FuncName]Create Function ...
如果系统中有此表,则修改,否则添加 if object_id('dbname..tbname') is null create table tb .... else alter table tb ....--不过建议,还不如删除此表,重新创建.
过程或函数:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcName/Fun_name]') AND type in (N'P/F', N'PC/FN')) Alter proc ProcName/Fun_name ... else create proc procname/Fun_name ....表:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U')) begin select * into # from tablename drop table tablename create table tablename.... insert tablename(col....) select [function](col),.... from # drop table # end else create table tablename .....
if not exists (select * from sysobjects where name='V_Test' and type='v') begin create view V_Test as select * from Test end 这样为什么会报错? 在关键字 'view' 附近有语法错误
if not exists (select 1 from sysobjects where name='V_Test' and type='v') begin exec('create view V_Test as select * from ta ') end select * from v_testdrop view v_test
应该:if not exists (select * from sysobjects where name='V_Test' and type='v') begin exec(' create view V_Test as select * from Test ') end
相信通过楼上这么多的回复,楼主已经能搞定存储过程、函数和视图。对于表字段的修改,应该分情况写语句, 比如表ta增加了字段fa,应该判断是否存在该表,和判断是否存在该字段IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'ta') AND type in (N'U')) --表不存在 begin exec(' create table ta(...) ') end else begin IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID(N'ta') AND name='FA') --字段不存在 begin exec(' alter table ta add fa int ') end end 还有一种最复杂的,就是表的字段经过多次修改,这样就必须判断字段类型, 比如表Ta最早设计没有Fa字段,后来增加成int字段,再修改成varchar(20),最后修改成varchar(50),应该如下写语句IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'ta') AND type in (N'U')) --表不存在 begin exec(' create table ta(...,FA varchar(50)) ') end else begin IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID(N'ta') AND name='FA') --字段不存在 begin exec(' alter table ta add fa varchar(50) ') end else IF NOT EXISTS (SELECT * FROM syscolumns c,systypes t WHERE c.id = OBJECT_ID(N'ta') AND c.name='Fa' and c.xusertype=t.xtype and t.name='varchar' and c.prec = 50 ) --字段类型不同 begin exec(' alter table ta alter columns fa varchar(50) ') endend
表可以这样建,视图为什么不可以?if not exists (select * from sysobjects where name='V_Test' and type='v') begin exec(' create view V_Test as select * from Test ') end 要这样写的具体是什么原因啊?
alter function/procedure
else
create function/procedure
对于表一般不说有此表,就修改没有就添加 不太好做
虽然表中的所有列都保存在当前数据库的syscolumns表中但一个一个添加不太好做,
要想做就是:
[code=SQL]
if exists(select * from syscolumns where id=object_id('表名') and name='列名')
alter table 表名 add 列名 类型 ...
[code=SQL]
比如在"表名"表中不存在"列名"列就新加该列if not exists(select * from syscolumns where id=object_id('表名') and name='列名')
alter table 表名 add 列名 类型 ...
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
DROP TABLE [dbo].[TableName]Create Table......------------------------------------------------
创建存储过程:USE [XXX]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ProcName]Create Proc ....--------------------------------------------------
创建函数:USE [XXX]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncName]Create Function ...
create table tb ....
else
alter table tb ....--不过建议,还不如删除此表,重新创建.
Alter proc ProcName/Fun_name ...
else
create proc procname/Fun_name ....表:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
begin
select * into # from tablename
drop table tablename
create table tablename....
insert tablename(col....) select [function](col),.... from #
drop table #
end
else
create table tablename .....
begin
create view V_Test
as
select * from Test
end
这样为什么会报错?
在关键字 'view' 附近有语法错误
begin
exec('create view V_Test
as
select * from ta ')
end select * from v_testdrop view v_test
应该:if not exists (select * from sysobjects where name='V_Test' and type='v')
begin
exec('
create view V_Test
as
select * from Test
')
end
比如表ta增加了字段fa,应该判断是否存在该表,和判断是否存在该字段IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'ta') AND type in (N'U')) --表不存在
begin
exec('
create table ta(...)
')
end
else
begin
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID(N'ta') AND name='FA') --字段不存在
begin
exec('
alter table ta add fa int
')
end
end
还有一种最复杂的,就是表的字段经过多次修改,这样就必须判断字段类型,
比如表Ta最早设计没有Fa字段,后来增加成int字段,再修改成varchar(20),最后修改成varchar(50),应该如下写语句IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'ta') AND type in (N'U')) --表不存在
begin
exec('
create table ta(...,FA varchar(50))
')
end
else
begin
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID(N'ta') AND name='FA') --字段不存在
begin
exec('
alter table ta add fa varchar(50)
')
end
else
IF NOT EXISTS (SELECT * FROM syscolumns c,systypes t
WHERE c.id = OBJECT_ID(N'ta') AND c.name='Fa'
and c.xusertype=t.xtype
and t.name='varchar'
and c.prec = 50
) --字段类型不同
begin
exec('
alter table ta alter columns fa varchar(50)
')
endend
begin
exec('
create view V_Test
as
select * from Test
')
end
要这样写的具体是什么原因啊?
使用动态语句 exec @sql 就可以了