SQL Stored-Proc using parameter for server name?

Question

I have a stored procedure that gets all the non-system database names from a SQL Server:

select name from MySQLServer.master.sys.databases
where name not like ('master')
and name not like ('tempdb')
and name not like ('msdb')
and name not like ('model')
and name not like ('Admin')

What I would like to do is pass the server name as a parameter, so this sproc is more reusable:

@servername = 'some passed servername'

select name from @servername.master.sys.databases
where name not like ('master')
and name not like ('tempdb')
and name not like ('msdb')
and name not like ('model')
and name not like ('Admin')

Can this be done? How would I do this?

Could I create a variable like:

DECLARE @serverPointer nvarchar(MAX)
SET @serverPointer = @servername + '.master.sys.databases'

select name from @serverPointer

Any help would be appreciated.

Answer 1

To do this you will need to create a dynamic query in the stored procedure and execute it using the exec command, here's a couple of examples:

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2

Answer 2

declare @servername nvarchar(max)
DECLARE @serverPointer nvarchar(MAX)
declare @qry nvarchar(max) 
@serverPointer = @servername + '.master.sys.databases' 
set @qry = 'select name from '+@serverPointer
exec sp_executesql @qry

Answer 3

Declare @query as varchar(1000)

set @query = ''
+ CHAR(10) + ' select name from '+ CAST(@servername AS VARCHAR(15))+'.master.sys.databases'
+ CHAR(10) +  'where name not in (''master'',''tempdb'',''msdb'',''model'',''Admin'')'

--print (@query)
exec (@query)

Answer 4

This will work if you have linked servers to all of the SQL Servers you wish to query. As to the query to use, the system databases take up the first 4 database_id values. So you can run the following to get the user databases:

SELECT '[' + name + ']' 
FROM sys.databases
WHERE database_id > 4

Putting that together, you'd basically do the following for your proc:

CREATE PROC GetUserDBs
  @Server sysname
AS
BEGIN
  DECLARE @SQL nvarchar(2000);
  SET @SQL = 'SELECT ''['' + name + '']'' FROM [' + @Server + '].master.sys.databases WHERE database_id > 4';
  EXECUTE sp_executesql @SQL;
END
GO