高手帮忙:
小弟有个想法,将一个建表和该表的一个触发器一起通过生成脚本导出,内容如下:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_b]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[T_b]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOCREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER [T_b] ON [dbo].[b]
FOR INSERT
AS
select * from a
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
以上程序代码在查询分析器里面可以执行,但是如果将此代码存放到表里面,用一个语句将内容取出为@sql,然后用sp_executesql @sql执行该代码则执行不过去。
如果单独的将建表脚本if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOCREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
存储这么执行,把“GO”字符去掉,则可以这样执行建立表。如果单独将CREATE TRIGGER [T_b] ON [dbo].[b]
FOR INSERT
AS
select * from a
GO语句通过上面的方法执行,也可以执行(将“GO”去掉)
现问题这样,有什么方法直接可以执行所有导出的代码(不用查询分析器),高手指点。
小弟有个想法,将一个建表和该表的一个触发器一起通过生成脚本导出,内容如下:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_b]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[T_b]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOCREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER [T_b] ON [dbo].[b]
FOR INSERT
AS
select * from a
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
以上程序代码在查询分析器里面可以执行,但是如果将此代码存放到表里面,用一个语句将内容取出为@sql,然后用sp_executesql @sql执行该代码则执行不过去。
如果单独的将建表脚本if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOCREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
存储这么执行,把“GO”字符去掉,则可以这样执行建立表。如果单独将CREATE TRIGGER [T_b] ON [dbo].[b]
FOR INSERT
AS
select * from a
GO语句通过上面的方法执行,也可以执行(将“GO”去掉)
现问题这样,有什么方法直接可以执行所有导出的代码(不用查询分析器),高手指点。
set @sql=replace(@sql,'GO','')
sp_executesql @sql
服务器: 消息 111,级别 15,状态 1,行 20
'CREATE TRIGGER' 必须是批查询中的第一条语句。
如果单独执行建表脚本这么执行是可以的,加上建立触发器的脚本就不行了,谢谢
exec master.dbo.xp_cmdshell ' osql -U sa -P 123 -i c:\123.sql'
但是必须在脚本里面指定数据库,也就是加上use test go语句,这样就可以执行了,不知道还有没有其他的方法
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_b]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[T_b]
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GO CREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO CREATE TRIGGER [T_b] ON [dbo].[b]
FOR INSERT
AS
select * from a
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 存储到表里面当有问题的时候,直接执行这个代码将表恢复到原始状态
高手帮忙哦!!
truncate table tb_name
--还有触发器没必要重建