In sql what query would you use to determine the most popular (the column name that appears the most frequentl

In sql what query would you use to determine the most popular (the column name that appears the most frequently) column name in the tables of your database?

In sql what query would you use to determine the most popular (the column name that appears the most frequentl
you can run queries against the master and model databases which store all the information for all user database tables on the server.


this answer refers to MS SQL Server, not sure how Oracle does it, but i would imagine not much differently.
Reply: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鈥攖o 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鈥攐f 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鈥攊ntArray鈥攖o 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 %26amp;= "WHERE (type = 'U') AND (status %26gt; 0) "


SQL %26amp;= "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 '" %26amp; TableName %26amp; "' as TableName, " _


SQL %26amp;= "COLUMN_NAME, DATA_TYPE, " _


SQL %26amp;= "CHARACTER_MAXIMUM_LENGTH from " _


SQL %26amp;= "INFORMATION_SCHEMA.COLUMNS where " _


SQL %26amp;= "table_name = '" %26amp; TableName %26amp; "' "
Reply:I'm not sure that's possible to do with just SQL. :/