Well, I get the question on how do I get the database names for my members frequently from users. I know this is harder than it should be but hiding this information helps future proof certain operations that we are working on for future for federations so we need to continue to keep them hidden.

I realize we don't leave you many options these days for getting the database names for all your members; Today sys.databases reports just the is_federation_member property to tell you if a database is a member. The federation views in the root database like sys.federation_* does not tell you the member ids and their ranges. I know it gets complicated to generate the database names for all your members or for a given federation. So here is a quick script that will generate this the right batch for you: Run the script in the root database and it will return all the database names on all federations based on your existing distribution points.  

-- GENERATE DB_NAME SCRIPT 
USE FEDERATION ROOT WITH RESET
GO
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'USE FEDERATION fed_name(dist_name=fed_dist_value) WITH RESET, FILTERING=OFF
GO
SET NOCOUNT ON
SELECT db_name()
GO'
SELECT CASE
WHEN SQL_VARIANT_PROPERTY ( fmd.range_low , 'BaseType' ) IN ('int','bigint')
THEN REPLACE(REPLACE(REPLACE(@sql,'fed_name',f.name), 'dist_name', fmd.distribution_name), 'fed_dist_value', cast(fmd.range_low as nvarchar))
WHEN SQL_VARIANT_PROPERTY ( fmd.range_low , 'BaseType' ) IN ('varbinary')
THEN REPLACE(REPLACE(REPLACE(@sql,'fed_name',f.name), 'dist_name', fmd.distribution_name), 'fed_dist_value', convert(nvarchar(max),fmd.range_low,1))
WHEN SQL_VARIANT_PROPERTY ( fmd.range_low , 'BaseType' ) IN ('uniqueidentifier')
THEN REPLACE(REPLACE(REPLACE(@sql,'fed_name',f.name), 'dist_name', fmd.distribution_name), 'fed_dist_value', ''''+convert(nvarchar(max),fmd.range_low,1)+'''')
END
FROM sys.federation_member_distributions fmd join sys.federations f
on fmd.federation_id=f.federation_id order by fmd.range_low asc
GO

 

One important note: If you repartition federation with ALTER FEDERATION .. SPLIT or DROP, rerun the script so you can get the new list of databases.

As we improve development experience, scriptability and overall manageability, these will become less of an issue but for now, the above script generation should help you work with federation member database names.

thanks

-cihan