コード例 #1
0
        private string RetreiveSelectAll(int procId)
        {
            string strSelectAll = string.Format(@"SELECT distinct
      T.name as Ref
FROM
      sys.sql_dependencies D        
LEFT JOIN sys.objects T 
      ON D.referenced_major_id = T.object_id        
LEFT JOIN sys.objects P 
      ON D.object_id = P.object_id
LEFT JOIN sys.columns C
      ON D.referenced_major_id = C.object_id 
      AND D.referenced_minor_id = C.column_id    
WHERE
      1=1        
      and D.is_select_all = 1
      AND    P.type = 'P'
      and P.object_id = '{0}'
", procId);

            string result = string.Empty;

            using (SQLQueryHelper sql = new SQLQueryHelper(Connection))
            {
                IDataReader reader = sql.ExecuteReader(strSelectAll);
                while (reader.Read())
                {
                    result += (string)reader.GetValue(0) + ";";
                }
                reader.Close();
            }
            return(result);
        }
コード例 #2
0
        /// <summary>
        /// Return all primary keys for a given table
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
        {
            string commandText = string.Format(_Queries.GetQuery("TablePrimaryKey"), table.Name, table.Database.Name, table.Owner);

            DataTable table2 = new SQLQueryHelper(connectionString).ExecuteData(commandText);

            if (table2.Rows.Count == 1)
            {
                string primary = (string)table2.Rows[0]["CONSTRAINT_NAME"];

                string commandText1 = string.Format(_Queries.GetQuery("TablePrimaryKeyColumns"), table.Database.Name, table.Name, primary);

                DataTable table3 = new SQLQueryHelper(connectionString).ExecuteData(commandText1);

                List <string> memberColumns = new List <string>(table3.Rows.Count);

                for (int i = 0; i < table3.Rows.Count; i++)
                {
                    memberColumns.Add((string)table3.Rows[i]["COLUMN_NAME"]);
                }

                return(new PrimaryKeySchema(table, primary, memberColumns));
            }
            else
            {
                return(null);
            }
        }
コード例 #3
0
        /// <summary>
        /// Return all keys for a given table
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public List <TableKeySchema> GetTableKeys(string connectionString, TableSchema table)
        {
            string commandText = string.Format(_Queries.GetQuery("TableKeys"), table.Owner, table.Name);

            DataTable table2 = new SQLQueryHelper(connectionString).ExecuteData(commandText);

            List <TableKeySchema> schemaArray = new List <TableKeySchema>();

            for (int i = 0; i < table2.Rows.Count; i++)
            {
                string keyName = (string)table2.Rows[i]["ForeignKeyName"];

                string foreignKeyTable = (string)table2.Rows[i]["TableName"];

                List <string> foreignKeyColumnNameList = new List <string>();
                foreignKeyColumnNameList.Add((string)table2.Rows[i]["ColumnName"]);

                string        primaryKeyTable = (string)table2.Rows[i]["ReferenceTableName"];
                List <string> primaryKeyTableColumnNameList = new List <string>();
                primaryKeyTableColumnNameList.Add((string)table2.Rows[i]["ReferenceColumnName"]);


                schemaArray.Add(new TableKeySchema(table.Database, keyName,
                                                   foreignKeyColumnNameList.ToArray(), foreignKeyTable, primaryKeyTableColumnNameList.ToArray(), primaryKeyTable));
            }
            return(schemaArray);
        }
コード例 #4
0
        /// <summary>
        /// Return all indexes for given table
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public List <IndexSchema> GetTableIndexes(string connectionString, TableSchema table)
        {
            DataTable          tableIndex = new SchemaHelper().GetIndexes(connectionString, table.Database.Name, table.Owner, table.Name, null);
            List <IndexSchema> list       = new List <IndexSchema>(tableIndex.Rows.Count);

            for (int i = 0; i < tableIndex.Rows.Count; i++)
            {
                string name = (string)tableIndex.Rows[i]["index_name"];

                DataTable     tableCol       = new SchemaHelper().GetIndexColumns(connectionString, table.Database.Name, table.Owner, table.Name, name, null);
                List <string> listRelatedCol = new List <string>();

                for (int j = 0; j < tableCol.Rows.Count; j++)
                {
                    listRelatedCol.Add((string)tableCol.Rows[j]["column_name"]);
                }

                string    commandText = string.Format(_Queries.GetQuery("IndexExtended"), name);
                DataTable tableSys    = new SQLQueryHelper(connectionString).ExecuteData(commandText);

                bool isPrimaryKey = (bool)tableSys.Rows[0]["is_primary_key"];
                bool isUnique     = (bool)tableSys.Rows[0]["is_unique"];
                bool isClustered  = (bool)((string)tableSys.Rows[0]["type_desc"] == "CLUSTERED" ? true : false);

                list.Add(new IndexSchema(table, name, isPrimaryKey, isUnique, isClustered, listRelatedCol));
            }

            return(list);
        }
コード例 #5
0
        /// <summary>
        /// Return all tables for the given DatabaseSchema object
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="database">DatabaseSchema</param>
        /// <returns></returns>
        public List <TableSchema> GetTables(string connectionString, DatabaseSchema database)
        {
            string str = string.Format(_Queries.GetQuery("TableSchema"), database.Name);

            DataTable          table       = new SQLQueryHelper(connectionString).ExecuteData(str);
            List <TableSchema> schemaArray = new List <TableSchema>(table.Rows.Count);

            for (int i = 0; i < table.Rows.Count; i++)
            {
                List <ExtendedProperty> list = null;

                string strext = string.Format(_Queries.GetQuery("ExtendedProperty"), (string)table.Rows[i][0]);

                DataTable dtEx = new SQLQueryHelper(connectionString).ExecuteData(strext);
                if (dtEx.Rows.Count == 1)
                {
                    list = new List <ExtendedProperty>();
                    list.Add(new ExtendedProperty("Description", (string)dtEx.Rows[0]["value"], DbType.String));
                }
                else
                {
                    list = null;
                }

                schemaArray.Add(new TableSchema(database, (string)table.Rows[i][0], (string)table.Rows[i][1], (DateTime)table.Rows[i][2], list));
            }
            return(schemaArray);
        }
コード例 #6
0
        private void RetreiveAllObjects(List <DataTable> result)
        {
            string str = @"SELECT
      o.object_id as ID,
      convert(sysname,o.name) as NAME,
      P.[rows] as ROW_COUNT
FROM
      sys.all_objects o, sys.tables T,sys.partitions P
      
WHERE
		T.object_id = P.object_id
and		o.object_id = T.object_id
and     o.type ='U'
ORDER BY 2
";

            DataTable table = new DataTable("TABLES");

            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("NAME", typeof(string));
            table.Columns.Add("ROW_COUNT", typeof(long));
            table.Columns.Add("NAMING", typeof(bool));

            table.Columns.Add("IX", typeof(int));             //counting
            table.Columns.Add("PK", typeof(int));
            table.Columns.Add("FK", typeof(int));

            table.Columns.Add("IX_LIST", typeof(string));             //list of object in error
            table.Columns.Add("PK_LIST", typeof(string));
            table.Columns.Add("FK_LIST", typeof(string));

            int    id;
            string name;

            using (SQLQueryHelper sql = new SQLQueryHelper(Connection))
            {
                IDataReader reader = sql.ExecuteReader(str);
                while (reader.Read())
                {
                    DataRow dr = table.NewRow();

                    id       = (int)reader.GetValue(0);
                    dr["ID"] = id;

                    name       = (string)reader.GetValue(1);
                    dr["NAME"] = name;

                    dr["ROW_COUNT"] = (long)reader.GetValue(2);

                    Match m2 = _TableNameMatch.Match(name);
                    dr["NAMING"] = m2.Success;

                    table.Rows.Add(dr);
                }
                reader.Close();
            }

            result.Add(table);
        }
コード例 #7
0
        /// <summary>
        /// Return all views in a given databse
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        public List <ViewSchema> GetViews(string connectionString, DatabaseSchema database)
        {
            string str = string.Format(_Queries.GetQuery("ViewSchema"), database.Name);

            DataTable table = new SQLQueryHelper(connectionString).ExecuteData(str);

            List <ViewSchema> schemaArray = new List <ViewSchema>(table.Rows.Count);

            for (int i = 0; i < table.Rows.Count; i++)
            {
                schemaArray.Add(new ViewSchema(database, (string)table.Rows[i][0], (string)table.Rows[i][1], (DateTime)table.Rows[i][2]));
            }
            return(schemaArray);
        }
コード例 #8
0
        /// <summary>
        /// Retreive the sql code of a given command
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public string GetCommandText(string connectionString, CommandSchema command)
        {
            StringBuilder builder = new StringBuilder();
            string        str     = string.Format(_Queries.GetQuery("CommandText"), command.Database.Name, command.Name);

            using (SQLQueryHelper query = new SQLQueryHelper(connectionString))
            {
                SqlDataReader reader = query.ExecuteReader(str);
                while (reader.Read())
                {
                    builder.Append(reader.GetString(0));
                }
                reader.Close();
            }
            return(builder.ToString());
        }
コード例 #9
0
        /// <summary>
        /// Return the SQL script that compose the given view
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public string GetViewText(string connectionString, ViewSchema view)
        {
            StringBuilder builder = new StringBuilder();
            string        str     = string.Format(_Queries.GetQuery("ViewText"), view.Name, view.Database.Name, view.Owner);

            using (SQLQueryHelper query = new SQLQueryHelper(connectionString))
            {
                SqlDataReader reader = query.ExecuteReader(str);
                while (reader.Read())
                {
                    builder.Append(reader.GetString(0));
                }
                reader.Close();
            }
            return(builder.ToString());
        }
コード例 #10
0
        private DataTable RetreiveProcedures()
        {
            string str = @"SELECT
      P.object_id,
     convert(sysname,P.name) as NAME,
      c.Text
FROM
      sys.procedures P, sys.syscomments C 
where P.object_id = C.id
";

            DataTable table = new DataTable("Procedures");

            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("NAME", typeof(string));
            table.Columns.Add("LENGTH", typeof(string));
            table.Columns.Add("NAMING", typeof(bool));
            table.Columns.Add("CURSOR", typeof(int));
            table.Columns.Add("NOT_IN", typeof(int));
            table.Columns.Add("NOT_EXIST", typeof(int));
            table.Columns.Add("CASE_WHEN", typeof(int));
            table.Columns.Add("DISTINCT", typeof(int));
            table.Columns.Add("GOTO", typeof(int));
            table.Columns.Add("COUNT", typeof(int));
            table.Columns.Add("SUP_EGAL", typeof(int));
            table.Columns.Add("INF_EGAL", typeof(int));
            table.Columns.Add("TEMP_TABLE", typeof(int));
            table.Columns.Add("TOP", typeof(int));
            table.Columns.Add("IN_SELECT", typeof(int));
            table.Columns.Add("SELECT_ALL", typeof(string));

            int    id;
            string name;
            string content;

            using (SQLQueryHelper sql = new SQLQueryHelper(Connection))
            {
                IDataReader reader = sql.ExecuteReader(str);
                while (reader.Read())
                {
                    DataRow dr = table.NewRow();
                    id       = (int)reader.GetValue(0);
                    dr["ID"] = id;

                    name       = (string)reader.GetValue(1);
                    dr["NAME"] = name;

                    content      = (string)reader.GetValue(2);
                    dr["LENGTH"] = content.Length;

                    dr["COUNT"]      = _PerfPatern[0].Match(content).Groups.Count;
                    dr["CURSOR"]     = _PerfPatern[1].Matches(content).Count;
                    dr["NOT_EXIST"]  = _PerfPatern[2].Matches(content).Count;
                    dr["DISTINCT"]   = _PerfPatern[3].Matches(content).Count;
                    dr["NOT_IN"]     = _PerfPatern[4].Matches(content).Count;
                    dr["CASE_WHEN"]  = _PerfPatern[5].Matches(content).Count;
                    dr["GOTO"]       = _PerfPatern[6].Matches(content).Count;
                    dr["SUP_EGAL"]   = _PerfPatern[7].Matches(content).Count;
                    dr["INF_EGAL"]   = _PerfPatern[8].Matches(content).Count;
                    dr["TEMP_TABLE"] = _PerfPatern[10].Matches(content).Count;
                    dr["TOP"]        = _PerfPatern[9].Match(content).Groups.Count;
                    dr["IN_SELECT"]  = _PerfPatern[11].Matches(content).Count;

                    Match m2 = _ProcNameMatch.Match(name);
                    dr["NAMING"] = m2.Success;

                    dr["SELECT_ALL"] = RetreiveSelectAll(id);
                    table.Rows.Add(dr);
                }
                reader.Close();
            }
            return(table);
        }