MS- SQL 테입블 목록 조회, 테이블별 컬럼 조회하기

MS- SQL 테입블 목록 조회, 테이블별 컬럼 조회하기

 

오라클만 사용하다가 아주 5년만에 MS-SQL 사용 하려니 힘드네

오라클이랑 많이 달라서..

 

        #region SelectTableList - 테이블 리스트 조회
        /// <summary>
        ///
        /// </summary>
        /// <param name="vo"></param>
        /// <returns></returns>
        public ReplySet SelectTableList(string paramTable)
        {
            StringBuilder sb = new StringBuilder();
            string s_Query = string.Empty;
            ReplySet rs = new ReplySet();
            try
            {
                DBConnect();

                sb.AppendLine(" Select o.name AS TABLE_NAME,");
                sb.AppendLine("     nullif(cast(p.value as varchar(4000)),'-') as COMMENTS");
                sb.AppendLine("     FROM sys.objects o ");
                sb.AppendLine("      left outer join sys.schemas s on s.schema_id = o.schema_id ");
                sb.AppendLine("      left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' ");
                sb.AppendLine("     where type in ('U', 'V') ");
                sb.AppendLine("     AND o.[name] LIKE 'TB_RC%'");
                sb.AppendLine("     order by type, s.name, o.name");

                rs.DataTable = dbCtrl.ExecuteDataTable(sb.ToString(), "all_col_comments");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbCtrl.ReaderClose();
                dbCtrl.DbClose();
            }
            return rs;
        }
        #endregion

        #region SelectColumnList - 컬럼 조회
        /// <summary>
        ///
        /// </summary>
        /// <param name="vo"></param>
        /// <returns></returns>
        public ReplySet SelectColumnList(string paramTable)
        {
            StringBuilder sb = new StringBuilder();
            string s_Query = string.Empty;
            ReplySet rs = new ReplySet();
            try
            {
                DBConnect();

                sb.AppendLine(" SELECT ");
                sb.AppendLine("    A.COLUMN_NAME,");
                sb.AppendLine("    B.[value] AS COL_COMMENTS, ");
                sb.AppendLine("    DATA_TYPE + '(' + CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' AS COL_TYPE ");
                sb.AppendLine(" FROM INFORMATION_SCHEMA.COLUMNS A");
                sb.AppendLine("   LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B");
                sb.AppendLine("    ON B.major_id = object_id(A.TABLE_NAME) ");
                sb.AppendLine("     AND A.ORDINAL_POSITION = B.minor_id");
                sb.AppendLine(" WHERE TABLE_SCHEMA = 'dmsoft'");
                sb.AppendLine(" AND A.TABLE_NAME LIKE 'TB_RC%'");
                if (paramTable != null && paramTable.Length > 0)
                {
                    sb.AppendLine(" AND A.TABLE_NAME = '" + paramTable + "'");
                }
                sb.AppendLine(" ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION");

                rs.DataTable = dbCtrl.ExecuteDataTable(sb.ToString(), "all_col_comments");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbCtrl.ReaderClose();
                dbCtrl.DbClose();
            }
            return rs;
        }
        #endregion