订单号数据类型修改--删除和创建复制订阅项目(只需要操作指定字段作为主键的那些)

本文主要是介绍订单号数据类型修改--删除和创建复制订阅项目(只需要操作指定字段作为主键的那些)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

select db,sch,tbl,col,
--编写删除语句
'
use ['+publisher_db+']
exec sp_dropsubscription @publication = N'''+publication+''', @article = N'''+article+'''
, @subscriber = N''all'', @destination_db = N''all''

exec sp_droparticle @publication = N'''+publication+''', @article = N'''+source_object+''', @force_invalidate_snapshot = 1
' as dropscripts,
--编写生成语句
'
use ['+publisher_db+']
exec sp_addarticle @publication = N'''+publication+''', @article = N'''+article+''',
@source_owner = N'''+source_owner+''', @source_object = N'''+source_object+''', @type = N''logbased'',
@description = N'''', @creation_script = N'''', @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N''none'', @destination_table = N'''+destination_object+'''
, @destination_owner = N'''+isnull(destination_owner,source_owner)+''', @status = 24,
@vertical_partition = N''false'', @ins_cmd = N''CALL [dbo].[sp_MSins_dbo'+source_object+']''
, @del_cmd = N''CALL [dbo].[sp_MSdel_dbo'+source_object+']'',
@upd_cmd = N''SCALL [dbo].[sp_MSupd_dbo'+source_object+']''
' as addscripts
from
(
--查询复制订阅项目信息
SELECT a.article,a.publisher_db,b.publication,a.source_owner,a.source_object,a.destination_object,a.destination_owner,
s.db,s.sch,s.tbl,s.col
FROM [distribution].[dbo].[MSarticles] a inner join
   [distribution].[dbo].[MSpublications] b
   on a.publication_id=b.publication_id

inner join
(
--查询主键信息
select distinct db_name() as db, c.name sch,sp.name tbl,sc.name col from dbo.sysindexes si INNER JOIN
               dbo.sysindexkeys sik    ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
               dbo.syscolumns sc   ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
               dbo.sysobjects so   ON so.name = si.name AND so.xtype = 'PK'

                inner join sys.sysobjects sp on so.parent_obj=sp.id
                inner join sys.schemas c on c.schema_id=sp.uid
intersect
--查询定义好的信息做交集
select db,sch,tbl,col from master.dbo.UnionALL
) s
on a.publisher_db=s.db and a.source_owner=s.sch and a.source_object=s.tbl)
aa