SQL Stored-Proc using parameter for server name? -


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

 Choose the name from  MySQLServer.master.sys.databases where the name is not ('master') and the name does not like ('tempdb') and the name does not like ('msdb') and the name ('model') Do not like and does not like the name ('Admin')

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

 Select the name from the  @servername = 'Some Passed Syrename' name @ servername.master.sys.databases where the name ('master') and name ('tempdb') do not like and the name ('MSDB') is preferred. And not like the name ('model') and not the name ('admin')

Can it be done? How can I do this?

Can I create a variable: 

  announcement @ serverpoker interaction (maximum) SET @serverPointer = @sernvername + '.master.sys Any help appreciated Will go.   
 
   
 This will work if you have linked all SQL Servers to the server That's what you want to query. According to the query to use, system databases first assume 4 database_id values. You can run the following to get the user database:  
  SELECT '[' + name + ']' from sys.databases where database_ id & gt; 4  
 
 By putting together, you basically do the following for your process: 
 
  create PROC GetUserDBs @ server sysname AS BEGIN DECLARE @ SQL Nvarchar (2000); SET @SQL = 'SELECT' '[' + + + Name + ']]' 'FROM [' + @ Server + '] .master.sys.databases Where database_id & gt; 4 '; Execute SP_executesql @ SQL; Go END

Comments

Post a Comment