I have a several linked servers, with mass of DB's and tons of views and tables. The database names are like random set of letters and numbers and does not make any sense for me. Need to find on which server is certain view.
DECLARE @servers AS TABLE (srv_name NVARCHAR(300), srv_providername NVARCHAR(300), srv_product NVARCHAR(300), srv_datasource NVARCHAR(300), srv_pstring NVARCHAR(300), srv_location NVARCHAR(300), srv_cat NVARCHAR(300) );
INSERT INTO @servers EXEC sp_linkedservers
DECLARE @sql nvarchar(MAX) = N''
SELECT @sql +=
N'SELECT
''' + QUOTENAME(srv_name) + ''' AS [Server],
QUOTENAME(name) AS [DB]
FROM ' + QUOTENAME(srv_name) + '.[master].[sys].[databases]
WHERE HAS_PERMS_BY_NAME(name, ''DATABASE'', ''SELECT'') = 1
UNION ALL
'
FROM @servers
SET @sql = LEFT(@sql, LEN(@sql) - 11);
EXEC @sql
And it fails on EXEC. Says that query is not valid, but if i copy-paste the query an run it - works well.
Need to perform this exec, to generate temp table w/ servers and available databases, to query sys.all_object of each DB on each server
The output on my localhost w/ 1 server
> (1 row(s) affected) Msg 203, Level 16, State 2, Line 17 The name
> 'SELECT
> '[localhost]' AS [Server],
> QUOTENAME(name) AS [DB] FROM [localhost].[master].[sys].[databases] WHERE HAS_PERMS_BY_NAME(name,
> 'DATABASE', 'SELECT') = 1 ' is not a valid identifier.
Same error I also made. So just execute like this:
exec sp_executesql @sql