Build SQL Server Stored Procedures From Information_Schema Tables

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BuildCode {
	/// <summary>
	/// Summary description for ColumnDAO.
	/// </summary>
	public class ColumnDAO {

		// constructor
		public ColumnDAO() {
		}

		// accessors
		public static ArrayList GetColumns(string connStr, string tableName, string tableSchema) {
			ArrayList al = new ArrayList();
			DataTable dt = new DataTable();
			StringBuilder sb = new StringBuilder();
			sb.Append("SELECT DISTINCT RTRIM(tbl.table_name) AS TableName");
			sb.Append(", RTRIM(col.table_schema) AS TableSchema");
			sb.Append(", RTRIM(col.column_name) AS ColumnName");
			sb.Append(", col.ordinal_position AS OrdinalPosition");
			sb.Append(", RTRIM(col.DATA_TYPE) AS DataType");
			sb.Append(", ISNULL(RTRIM(CHARACTER_MAXIMUM_LENGTH), '') AS MaxLen");
			sb.Append(", ISNULL(RTRIM(NUMERIC_PRECISION), '') AS [Precision]");
			sb.Append(", ISNULL(RTRIM(NUMERIC_SCALE), '') AS Scale");
			sb.Append(" FROM INFORMATION_SCHEMA.TABLES tbl");
			sb.Append(" INNER JOIN INFORMATION_SCHEMA.Columns col ON col.TABLE_NAME = tbl.TABLE_NAME");
			sb.Append(" WHERE tbl.TABLE_SCHEMA = '" + tableSchema + "' AND tbl.TABLE_TYPE = 'BASE TABLE'");
			sb.Append(" AND tbl.table_name = '" + tableName + "'");
			sb.Append(" ORDER BY col.ordinal_position");
			try {
				dt = DataProvider.FillDataSet(connStr, sb.ToString()).Tables[0];
				IEnumerator rows = dt.Rows.GetEnumerator();
				while (rows.MoveNext()) {
					Column dataType = new Column();
					DataRow row = (DataRow) rows.Current;
					dataType.TableSchema = row["TableSchema"].ToString();
					dataType.TableName = row["TableName"].ToString();
					dataType.ColumnName = row["ColumnName"].ToString();
					dataType.OrdinalPosition = row["OrdinalPosition"].ToString();
					dataType.DataType = row["DataType"].ToString();
					dataType.MaxLen = row["MaxLen"].ToString();
					dataType.Precision = row["Precision"].ToString();
					dataType.Scale = row["Scale"].ToString();
					al.Add(dataType);
				}
				dt.Dispose();
			}
			catch (Exception ex) {
				throw ex;
			}
			return al;
		}

	}
}