Get the list of all Tables, Views, Stored procedures that are not added in a publication for replication

Posted on

Question :

How do I get the list of all Tables, Views, Stored procedures that are NOT added in a publication for replication?

Answer :

For Snapshot and Transactional:

SELECT
    name 
FROM sysobjects WITH (NOLOCK) 
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND 
    category <> 2 AND 
    name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysarticles)

For Merge:

SELECT
    name 
FROM sysobjects WITH (NOLOCK) 
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND 
    category <> 2 AND 
    name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysmergearticles)

With added schema to Brandon’s answer.

SELECT DB_NAME() AS db, SCHEMA_NAME(o.schema_id) AS [Schema], so.name, so.type
FROM sys.objects o WITH (NOLOCK) 
INNER JOIN sysobjects so WITH(NOLOCK)
ON so.id=o.object_id
WHERE ((so.xtype = 'U') OR (so.xtype = 'V') OR (so.xtype = 'P')) 
AND so.category <> 2 
AND so.name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysarticles)
ORDER BY  so.name, so.type

To know the entire information about Tables and stored Procedures and views

Check this link:

http://codedada.com/how-to-get-entire-information-about-sqlserver-database/

Leave a Reply

Your email address will not be published. Required fields are marked *