Getting schema_name from schema_id on a remote SQL Server

Question

I have to run various queries over multiple databases and collect the results as a result set, so an sp_MSforeach approach won't work. I'm actually collecting data about the databases, schemas, and objects within those servers to provide some utility functions.

The information schemas work well when they work but for some things you need the sys views, such as sys.synonyms. This, over a linked server:

select *
from <remote_server>.<database>.sys.synonyms

This gets me, for example, schema_id. I need to get the schema name from the schema_id. I can't use SCHEMA_NAME() because that works in the context of this database on this server, not in the context of the remote, linked database on the remote server. What I would need is something like this (invented syntax):

select 
    *, 
    <remote_server>.<database>.SCHEMA_NAME(schema_id)
from
    <remote_server>.<database>.sys.synonyms

which AFAIK, does not exist.

I could use the information_schema.schemata views except

1. they don't mix well with MS's catalogue views (which too often just report internal ID numbers for things) plus
2. BOL continually warns you not to trust the schema info from information_schema results.

So a few questions, firstly how do I get the schema name from the schema_id when that schema_id has been taken from a remote server?

Second, does anyone know of any comprehensive documentation or even better, a good book, that covers these catalogue views that supplant the old information_schema views, because using them is a royal pain.

Answer 1

OK it seems you want to query system views for multiple databases and/or servers in one resultset. This is always difficult, as it requires a big UNION ALL query including each database, and if you only have them in variables then you need dynamic SQL.

Let us assume you have the databases in a table variable

DECLARE @dbs TABLE (server sysname, dbname sysname);

Then generate a big UNION ALL of all of them and execute it.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST('
SELECT
  schema_id,
  name,
  db = ' + QUOTENAME(dbs.dbname, '''') + '
FROM ' + QUOTENAME(dbs.server) + '.' + QUOTENAME(dbs.dbname) + '.sys.schemas
' AS nvarchar(max)), '
UNION ALL')
FROM @dbs dbs;

PRINT @sql;  -- your friend

EXEC sp_executesql @sql;

If you have any parameters, including table valued parameters, you can pass them through via sp_executesql. Do not inject them as text.

For example, to find @schema_id in all databases:

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST('
SELECT
  id,
  name,
  db = ' + QUOTENAME(dbs.dbname, '''') + '
FROM ' + QUOTENAME(dbs.server) + '.' + QUOTENAME(dbs.dbname) + '.sys.schemas
WHERE schema_id = @schema_id
' AS nvarchar(max)), '
UNION ALL')
FROM @dbs dbs;

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
  N'@schema_id int',
  @schema_id = @schema_id;