title: Create Schema Clones with RedGate sqlcompare & table of server names
body: <p>Ok, so I have taken an old batch file that previous DBA's have used to "clone" the schema from Production databases on multiple servers and drop them in one network directory.  This batch file was updated manually so I am trying to automate it.  We have a support dbase with a table that has all of the server names in it so I was figuring easiest would be a cursor (I know, but for this not an issue) to while through the list of server names executing the "sqlcompare" command line stuff for each.  Apparently though it makes a new command line line for every individual database on each server so my variable is returning more than one result.  There HAS to be a better way to do this, I feel like I must be going about this all wrong, any help is appreciated:</p>
  
  <pre><code>  DECLARE @RowCount INT = 1
   ,@index INT = 1
   ,@outputfolder varchar(100)
   ,@servername varchar(100)
   ,@environment varchar(50)
   ,@OutputPath  varchar(100)
   ,@sqlcmd nvarchar (100)
  
  SET @outputfolder = GETDATE()
  SET @OutputPath = '\\&lt;network location to store output files&gt;\'
  
  -- set @outputfolder = '2014.03.10_0900'
  SET @servername = '&lt;servername stored in table&gt;'
  SET @environment = '&lt;environment variable in table&gt;'
  SET @sqlcmd = ':connect '+@servername
  
  DECLARE @redgatecmd varchar(255)
  SET @redgatecmd = ''
  
  --SELECT '::'+@environment
  SET @redgatecmd = (SELECT DISTINCT 'sqlcompare /s1:'+@servername+' /db1:'+'name'+' /mkscr:"'+@OutputPath+@outputfolder+'\'+@environment+'\'+'name'+'" /options:iw,iu,isn,ie,ic,iup,iweo,infr,idc,idsn,isoa,isb"' from sysdatabases where name not in ( 'tempdb'))
  PRINT @redgatecmd
  
  --EXEC master..xp_cmdshell @redgatecmd
  </code></pre>