At Stedman Solutions, we do a lot of work with SQL Server replication, mostly transactional and merge replication.
The other day I needed a query to show all the merge replication publication on a SQL Server, not just a single database, but to see it for all databases on the SQL Server.
Here is the query that I came up with.
CREATE TABLE ##publications
(
DatabaseName varchar(1000),
TableName varchar(1000)
);
declare @cmd varchar(4000)
set @cmd='use [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_NAME = ''sysmergepublications''))
BEGIN
INSERT INTO ##publications
SELECT db_name(), a.name
FROM dbo.sysmergepublications p
JOIN dbo.[sysmergeextendedarticlesview] a on p.pubid=a.pubid;
END
'
exec sp_MSforeachdb @cmd;
select * FROM ##publications
drop table ##publications;
I use the sp_MSforeachdb which is an undocumented system stored procedure, and sometimes it misses a database or two when it is run. So after that I decided to modify my code to not use sp_MSForEachDB, and this is what I came up with. I did borrow some code from this blog post from a while back: https://stevestedman.com/2015/09/dbcc-checkdb-all-databases/
CREATE TABLE ##publications
(
DatabaseName varchar(1000),
TableName varchar(1000)
);
DECLARE @databaseList as CURSOR;
DECLARE @databaseName as NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(500);
SET @databaseList = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT QUOTENAME([name])
FROM sys.databases
WHERE [state] = 0
AND [is_read_only] = 0;
OPEN @databaseList;
FETCH NEXT FROM @databaseList into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = '
IF (EXISTS (SELECT *
FROM ' + @databaseName + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_NAME = ''sysmergepublications''))
BEGIN
INSERT INTO ##publications
SELECT ''' + @databaseName + ''', a.name
FROM ' + @databaseName + '.dbo.sysmergepublications p
JOIN ' + @databaseName + '.dbo.[sysmergeextendedarticlesview] a on p.pubid=a.pubid;
END
' ;
--print @tsql;
EXECUTE (@tsql);
FETCH NEXT FROM @databaseList into @databaseName;
END
CLOSE @databaseList;
DEALLOCATE @databaseList;
select * FROM ##publications
drop table ##publications;
If you are working with Merge Replication I hope this helps a bit.
Enjoy
– Steve Stedman
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!