在数据库DB1中有表A:含有主键
CREATE TABLE [dbo].[H_B_CErl](
[BeginTime] [smalldatetime] NOT NULL,
[ServerId] [int] NOT NULL,
[BssId] [tinyint] NOT NULL,
[BtsId] [int] NOT NULL,
[CellId] [tinyint] NOT NULL,
[CarrierId] [tinyint] NOT NULL DEFAULT ((0)),
[VocCallDur] [float] NULL DEFAULT ((0)),
[VocSHoDur] [float] NULL DEFAULT ((0)),
[VocSSHoDur] [float] NULL DEFAULT ((0)),
CONSTRAINT [PH_B_CErl] PRIMARY KEY CLUSTERED
(
[BeginTime] ASC,
[ServerId] ASC,
[BssId] ASC,
[BtsId] ASC,
[CellId] ASC,
[CarrierId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
在数据库DB2中有表A:没有主键
CREATE TABLE [dbo].[H_B_CErl](
[BeginTime] [smalldatetime] NOT NULL,
[ServerId] [int] NOT NULL,
[BssId] [tinyint] NOT NULL,
[BtsId] [int] NOT NULL,
[CellId] [tinyint] NOT NULL,
[CarrierId] [tinyint] NOT NULL,
[VocCallDur] [float] NULL,
[VocSHoDur] [float] NULL,
[VocSSHoDur] [float] NULL,
) ON [PRIMARY]
如何通过SQL代码根据在DB1中表A创建的主键,在DB2中表A中也同样创建,谢谢!因为在DB1与DB2中表的个数与内容都一样,就是DB2少了主键,该怎么办,非常感谢
CREATE TABLE [dbo].[H_B_CErl](
[BeginTime] [smalldatetime] NOT NULL,
[ServerId] [int] NOT NULL,
[BssId] [tinyint] NOT NULL,
[BtsId] [int] NOT NULL,
[CellId] [tinyint] NOT NULL,
[CarrierId] [tinyint] NOT NULL DEFAULT ((0)),
[VocCallDur] [float] NULL DEFAULT ((0)),
[VocSHoDur] [float] NULL DEFAULT ((0)),
[VocSSHoDur] [float] NULL DEFAULT ((0)),
CONSTRAINT [PH_B_CErl] PRIMARY KEY CLUSTERED
(
[BeginTime] ASC,
[ServerId] ASC,
[BssId] ASC,
[BtsId] ASC,
[CellId] ASC,
[CarrierId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
在数据库DB2中有表A:没有主键
CREATE TABLE [dbo].[H_B_CErl](
[BeginTime] [smalldatetime] NOT NULL,
[ServerId] [int] NOT NULL,
[BssId] [tinyint] NOT NULL,
[BtsId] [int] NOT NULL,
[CellId] [tinyint] NOT NULL,
[CarrierId] [tinyint] NOT NULL,
[VocCallDur] [float] NULL,
[VocSHoDur] [float] NULL,
[VocSSHoDur] [float] NULL,
) ON [PRIMARY]
如何通过SQL代码根据在DB1中表A创建的主键,在DB2中表A中也同样创建,谢谢!因为在DB1与DB2中表的个数与内容都一样,就是DB2少了主键,该怎么办,非常感谢
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE WITH OVERRIDE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'Ole Automation Procedures', 1
GO
--重新配置
RECONFIGURE WITH OVERRIDE
GO
if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
drop function fgetscript
go create function fgetscript(
@servername varchar(50) --服务器名
,@userid varchar(50)='sa' --用户名,如果为nt验证方式,则为空
,@password varchar(50)='' --密码
,@databasename varchar(50) --数据库名称
,@objectname varchar(250) --对象名 ) returns varchar(8000)
as
begin
declare @re varchar(8000) --返回脚本
declare @srvid int,@dbsid int --定义服务器、数据库集id
declare @dbid int,@tbid int --数据库、表id
declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量 --创建sqldmo对象
exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
if @err <>0 goto lberr --连接服务器
if isnull(@userid,'')='' --如果是 Nt验证方式
begin
exec @err=sp_oasetproperty @srvid,'loginsecure',1
if @err <>0 goto lberr exec @err=sp_oamethod @srvid,'connect',null,@servername
end
else
exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password if @err <>0 goto lberr --获取数据库集
exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
if @err <>0 goto lberr --获取要取得脚本的数据库id
exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
if @err <>0 goto lberr --获取要取得脚本的对象id
exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
if @err <>0 goto lberr --取得脚本
exec @err=sp_oamethod @tbid,'script',@re output
if @err <>0 goto lberr --print @re
return(@re)
lberr:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4)
set @errb=cast(@err as varbinary(4))
--exec master..xp_varbintohexstr @errb,@re out
set @re=--'错误号: '+@re
char(13)+'错误源: '+@src
+char(13)+'错误描述: '+@desc
return(@re)
end
go -------------------------------declare @name varchar(250)
declare #aa cursor for
select name from sys.tables
open #aa
fetch next from #aa into @name
while @@fetch_status=0
begin
print dbo.fgetscript(''/*servername*/,''/*userid*/,''/*password*/,''/*databasename*/,@name)
fetch next from #aa into @name
end
close #aa
deallocate #aa
INSERT TB
EXEC SP_HELPINDEX [H_B_CErl]DECLARE @STR VARCHAR(8000)
SET @STR=''SELECT @STR=' ALTER TABLE [H_B_CErl2] ADD CONSTRAINT [PH_B_CErl2] PRIMARY KEY CLUSTERED
('+INDEX_KEYS+'
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] '
FROM TB
--PRINT @STR
EXEC(@STR)DROP TABLE TB