主页 / dba / 问题 / 343428
Accepted
Marcello Miorelli
Asked: 2024-11-06 03:33:04 +0800 CST

如何列出所有复制的视图和功能？

  • 772

我有一个脚本可以向我展示出版物的复制文章：

declare @publication_name sysname = 'my_pub'
declare @database_name sysname = 'my_db'
 use [my_db]


select  ServerName = @@servername
       ,publication_name=p.name
       ,p.repl_freq
       ,p.status
       ,p.sync_method
       --,snapshot_job_name=j.name
       ,p.independent_agent
       ,p.immediate_sync
       ,p.allow_anonymous
       ,p.replicate_ddl
       ,article_name=OBJECT_SCHEMA_NAME(a.objid) + '.' + OBJECT_NAME(a.objid)
       ,a.pre_creation_cmd 
       ,pre_creation_cmd_desc  = CASE a.pre_creation_cmd 
         WHEN 0 THEN 'none      - Doesn''t use a command.'
         WHEN 1 THEN 'drop      - Drops the destination table.'
         WHEN 2 THEN 'delete    - Deletes the destination table.'
         WHEN 3 THEN 'truncate  - Truncates the destination table.'
         ELSE 'Not known - Radhe Radhe'
        END


            FROM dbo.syspublications P 
            INNER JOIN dbo.sysarticles A 
                    ON P.pubid = A.pubid

           left outer  JOIN msdb.dbo.sysjobs j
                    ON j.job_id = p.snapshot_jobid

 where p.name = @publication_name

但是，在我目前正在研究的这个特定出版物中，我也有功能，而这些对象没有显示在上面的查询中？

我知道它们在那里，我怎样才能看到它们？

sql-server

1 个回答

  1. Best Answer

    查看它的一种方法是使用sp_helparticle

    declare @publication_name sysname = 'my_pub'
declare @database_name sysname = 'my_db'
 use [my_db]
exec sp_helparticle @publication = @publication_name

    您还可以使用sp_helpsubscription

    但如果您想要查询，那么您可以使用此处的脚本：

    SELECT o.object_id
      ,schema_name(o.schema_id) + '.' + name AS published_object
      , o.type_desc
FROM sys.objects o 
WHERE o.is_schema_published = 1
  or o.is_published = 1

    在此处输入图片描述

    如果您想要已发布文章旁边的专栏列表，那么您可以在此处使用我的脚本：

                            print @@servername
                        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                        SET NOCOUNT OFF



SELECT 
   ---------------------------------------
   -- script to list objects in a publication
   -- including views and functions
   -- and their columns
   -- marcello miorelli v20-feb-2023
   ---------------------------------------
   syspublications.name AS "Publication", 
   RADHE.name AS "Article", 
   radhe.type_desc,
   STUFF( 
       ( 
           SELECT ', ' + syscolumns.name AS [text()] 
           FROM sys.objects o WITH (NOLOCK) 
               inner JOIN syscolumns WITH (NOLOCK) 
                 ON o.object_id = syscolumns.id 
            WHERE (o.is_schema_published = 1
              or o.is_published = 1)
              and o.object_id = RADHE.object_id
           ORDER BY syscolumns.colorder 
           FOR XML PATH('') 
       ), 1, 2, '' 
   ) AS "Columns" FROM syspublications WITH (NOLOCK) 
   INNER JOIN (
   
                   SELECT pubid =1
                      ,o.object_id
                      ,schema_name(o.schema_id) + '.' + name AS name
                      , o.type_desc
                FROM sys.objects o 
                WHERE o.is_schema_published = 1
                  or o.is_published = 1

  ) RADHE  ON syspublications.pubid = RADHE.pubid 
  --where radhe.object_id = object_id ('dbo.fnCalcBudgetTotals')
ORDER BY syspublications.name, RADHE.name

    在此处输入图片描述

    • 0

相关问题