I don't know what language you are using. I can provide you with the algorithm required to find the most frequently occuring column name. If you want the code snippet for either Visual Basic or Java then, check out the links below. The code on those pages contain the specific meta-data information that is required for your task.
You are going to have to access the database meta-data in order to find out the number of tables, the table names, the number of columns in each particular table, and their respective names.
All For Loop counters will begin at zero.
You will use a outer For Loop to iterate through each table in the database—to get a list of the column names.
Create a integer variable to store the the total number of unique column names; intialize it to zero. Let's call this variable intUnique .
Create a data structure to store the column names, i.e., a string array. I assumed all arrays are zero-based.
Create a data structure to store the number of occurences of a particular column name, i.e., an integer array. This particular data structure will be associated with the above string array.
Create integer variable to store a location value (to store an array index): intFound
To obtain each table's column names: Use an inner For Loop to iterate through columns in the current table—of the outer For Loop.
As you obtain a column name, find out if already exists in the string array: strArray. To accomplish this task, use an additional For Loop to iterate through the strArray. Within this For Loop, you will perform a search, i.e., using the inStr() function.
If there is no match then, add the column name to the string array at index intUnique: strArray(intUnique). Next, increment intUnique. Also, increment the value of intArray element whose index is equal to intUnique: intArray(intUnique).
However, if the current column name matches an element in the string array then, note the current value of the inner For Loop's counter. Assign this value to intFound. Now, in the integer array, increment the element that corresponds to that matching strArray element: intArray(intFound).
The inner For Loop continues until each column in the table is processed.
Then, the outer For Loop counter increments, and the next table is processed.
Once you have processed all of the tables, it is a simple matter of checking each element of the integer array—intArray—to find the maximum value. Whichever integer array element is the maximum, use that index value to access the value in the corresponding string array. This element's value holds the name of the most frequently used column name.
_________________________
Java Hints:
Statement st = conn.createStatement();
//Let's say you have a table named survery
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
// Use i.e., rsMetaData.getColumnName(i)
_________________________
Visual Basic Hints:
Dim SQL As String
Dim ds As DataSet
SQL = "SELECT Name FROM sysobjects "
SQL &= "WHERE (type = 'U') AND (status > 0) "
SQL &= "order by Name"
This above statement pulls the table names from the sysobjects table in the selected database. Executing this SQL statement yields a list of all the tables which are returned, below, in a DataSet:
ds = RunSQLWithDataSet(SQL)
This section uses the SQL query shown here to retrieve the details of a particular table, including field names, datatype, and maximum length:
SQL = "select '" & TableName & "' as TableName, " _
SQL &= "COLUMN_NAME, DATA_TYPE, " _
SQL &= "CHARACTER_MAXIMUM_LENGTH from " _
SQL &= "INFORMATION_SCHEMA.COLUMNS where " _
SQL &= "table_name = '" & TableName & "' "