Find all tables containing column with specified name Many developers asking is it possible to find a column name in all tables which has columns specified name. So here is query to search a column in a SQL Server database. Search in all tables SELECT c.name AS 'Column', t.name AS 'TableName' FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name = 'MycolumnName' --specify search column name ORDER BY TableName,Column Search in all tables and views SELECT COLUMN_NAME AS 'Column', TABLE_NAME AS 'TableName' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MyColumnName' --specify search column name ORDER BY TableName,Column;

Collecting data via Array and copy it to another sheet on next available row

Rathnakar

I'm trying to set up a sheet that collects data from sheet 'input' and copies it over to sheet 'output', using an array. Ideally it would paste it on the next available row, for each 'for loop'. Unfortunately due to lack of know-how, I run into two major issues;

It currently only returns the left most value on a row. E.g. On row 2 I've got both 'car2' and 'car3' with each's respective color. This would probably require a two dimensional Array, as the Logger seems to grab these two in a single iteration. The value's that do get picked up('car1, 'car2' and 'car4') get pasted onto the same row. So the last iteration overwrite the previous for loop results. Obviously the 'add to next row' bit is not set correctly.

Really the two questions are

How do I ensure that all values (based on the for loop) get pasted in the same order as the Array. How does every iteration get set to the next available row, instead of overwriting the previous one.

Current code below:

function collectDataTest()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('input'); //where to grab data
  var sOutput = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('output'); // where to store data 
  var endRW = ss.getLastRow();
  var endRWout = sOutput.getLastRow();
  var nextRW = endRW - 1; 
  var values = sOutput.getRange("A:A").getValues();
  var maxIndex = values.reduce(function(maxIndex, row, index) 
  { return row[0] === "" ? maxIndex : index;}, 0);
  var data = sOutput.getDataRange().getValues();
  for (var ii = 0; ii < data.length; ii++) 
  {
  if (data[ii][2]) 
  {   // index 2 = 3rd column = C
    break;
  }
}
Logger.log("First nonempty row is " + (ii+1))  // i is 0-based but row numbers are 1-based
var cell1 = sOutput.getRange(ii,1).setValue('<start>');
for (var i = 1; i <= endRW; i++)
{                                       
  if (ss.getRange(i, 1).getValue()== 'car1' && ss.getRange(i, 2).getValue() != null )
  {
  sOutput.setActiveRange(sOutput.getRange(maxIndex + 2, 1)).setValue(ss.getRange(i,1).getValue()+'- '+ss.getRange(i,2).getValue());           
  }
  else if (ss.getRange(i, 1).getValue()== 'car2' && ss.getRange(i, 2).getValue() != null )
  {
  sOutput.setActiveRange(sOutput.getRange(maxIndex + 2, 1)).setValue(ss.getRange(i,1).getValue()+'- '+ss.getRange(i,2).getValue());           
  }
  else if (ss.getRange(i, 3).getValue()== 'car3' && ss.getRange(i, 4).getValue() != null )
  {
  sOutput.setActiveRange(sOutput.getRange(maxIndex + 2, 1)).setValue(ss.getRange(i,3).getValue()+'- '+ss.getRange(i,4).getValue());           
  }
  else if (ss.getRange(i, 1).getValue()== 'car4' && ss.getRange(i, 2).getValue() != null )
  {
  sOutput.setActiveRange(sOutput.getRange(maxIndex + 2, 1)).setValue(ss.getRange(i,1).getValue()+'- '+ss.getRange(i,2).getValue());           
  }            
  else{} 
}
  sOutput.getRange(ii,1).setValue('</end>')
}