2

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

3 Answers 3

2

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)
0
2

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
0

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.