public IHttpActionResult GetTables(string database) { string query = @"SELECT o2.DataBaseName,TableName FROM(SELECT OwnerName,DatabaseName,DBKind AS DB1,DBKind FROM dbc.DatabasesV WHERE DataBaseName<> 'DBC' AND OwnerName = 'systemdatabaseowners' AND DataBaseName = '" + database + @"') AS o1 INNER JOIN (SELECT* FROM dbc.tablesV) AS o2 on o1.DataBaseName = o2.DataBaseName ORDER BY 1"; using (DbConnection conn = DbUtility.Connect()) using (DbCommand sql = DbUtility.GenerateSQL(conn, query)) using (var reader = sql.ExecuteReader()) { List <TDTable> tables = new List <TDTable>(); while (reader.Read()) { TDTable table = new TDTable { Name = reader.GetString(reader.GetOrdinal("TableName")), ColumnCount = 0, Database = database }; tables.Add(table); } ; return(Ok(tables)); } }
public IHttpActionResult GetReferences(string database, string table) { string query = @"WITH P (DbName,TblName) AS ( SELECT '" + database + "','" + table + @"') SELECT DatabaseName,TVMName,TableKind AS " + ((char)34) + @"Type" + ((char)34) + @" FROM dbc.TVM T, dbc.dbase D, P WHERE D.DatabaseId = T.DatabaseId AND CreateText LIKE '%" + ((char)34) + @"' || P.DbName || '" + ((char)34) + @"." + ((char)34) + @"' || P.TblName || '" + ((char)34) + @"%'(NOT CS) UNION SELECT DatabaseName,TVMName,TableKind AS " + ((char)34) + @"Type" + ((char)34) + @" FROM dbc.TextTbl X, dbc.dbase D, dbc.TVM T, P WHERE X.TextType = 'C' AND X.TextString LIKE '%" + ((char)34) + @"' || P.DbName || '" + ((char)34) + @"." + ((char)34) + @"' || P.TblName || '" + ((char)34) + @"%'(NOT CS) AND X.DatabaseId = D.DatabaseId AND X.TextId = T.TVMId UNION SELECT ChildDB,ChildTable,'T' FROM dbc.RI_Distinct_Children,P WHERE ParentDB = P.DbName AND ParentTable = P.TblName MINUS SELECT DatabaseName,TVMName,TableKind FROM dbc.TVM T, dbc.dbase D, P WHERE D.DatabaseId = T.DatabaseId AND DatabaseName = P.DbName AND TVMName = P.TblName ORDER BY 1,2 "; using (DbConnection conn = DbUtility.Connect()) using (DbCommand sql = DbUtility.GenerateSQL(conn, query)) using (var reader = sql.ExecuteReader()) { List <TDReference> tableReferences = new List <TDReference>(); while (reader.Read()) { TDReference reference = new TDReference { DataBaseName = reader.GetString(reader.GetOrdinal("DataBaseName")), TVMName = reader.GetString(reader.GetOrdinal("TVMName")), Type = reader.GetString(reader.GetOrdinal("Type")) }; Console.WriteLine(reference); tableReferences.Add(reference); } ; return(Ok(tableReferences)); } }
public IHttpActionResult GetTableSpace(string database, string table) { string query = @"Lock Dbc.TableSizeV for Access SELECT '' (Title ''),S.TableName AS Name, CASE TableKind WHEN 'O' THEN 'T' WHEN 'E' THEN 'P' WHEN 'A' THEN 'F' WHEN 'S' THEN 'F' WHEN 'R' THEN 'F' WHEN 'B' THEN 'F' ELSE TableKind END AS " + ((char)34) + @"Type" + ((char)34) + @",SUM(CurrentPerm) AS CurrentPerm,SUM(PeakPerm) AS PeakPerm, (100 - (AVG(CurrentPerm) / NULLIFZERO(MAX(CurrentPerm)) * 100)) AS SkewFactor, ''(Title ''),CreatorName,CommentString FROM Dbc.TableSizeV S, dbc.TablesV T WHERE S.TableName = T.TableName AND S.DataBaseName = T.DataBaseName AND S.DataBaseName = '" + database + @"' AND S.TableName = '" + table + @"' GROUP BY 2,3,8,9 ORDER BY 3,2; "; using (DbConnection conn = DbUtility.Connect()) using (DbCommand sql = DbUtility.GenerateSQL(conn, query)) using (var reader = sql.ExecuteReader()) { List <String> tableSpace = new List <string>(); while (reader.Read()) { tableSpace.Add(reader.GetString(reader.GetOrdinal("CurrentPerm"))); tableSpace.Add(reader.GetString(reader.GetOrdinal("PeakPerm"))); tableSpace.Add(reader.GetString(reader.GetOrdinal("SkewFactor"))); break; } ; return(Ok(tableSpace)); } }
public IHttpActionResult GetDatabases() { string query = @"SELECT o1.DataBaseName, Tables FROM( SELECT DataBaseName, COUNT(*) AS Tables FROM DBC.TablesV WHERE TableKind = 'T' AND DataBaseName <> 'DBC' GROUP BY DataBaseName) as o1 INNER JOIN ( SELECT OwnerName, DatabaseName, DBKind AS DB1, DBKind FROM dbc.DatabasesV WHERE DataBaseName<> 'DBC' AND OwnerName = 'systemdatabaseowner') AS o2 on o1.DataBaseName = o2.DataBaseName"; using (DbConnection conn = DbUtility.Connect()) using (DbCommand sql = DbUtility.GenerateSQL(conn, query)) using (var reader = sql.ExecuteReader()) { List <TDDatabase> databases = new List <TDDatabase>(); while (reader.Read()) { TDDatabase db = new TDDatabase { Name = reader.GetString(reader.GetOrdinal("DataBaseName")), IsOpen = false, Tables = new List <TDTable>() }; databases.Add(db); } ; return(Ok(databases)); } }
public IHttpActionResult GetRights(string database, string table) { string query = @"WITH Parms (DbName,TblName) AS ( SELECT '" + database + "', '" + table + @"') SELECT UserName,ColumnName,AccessRight,GrantAuthority,GrantorName, AllnessFlag FROM dbc.AllRightsV,Parms WHERE DatabaseName=Parms.DbName AND TableName=Parms.TblName UNION SELECT RoleName,ColumnName,AccessRight,'R',GrantorName,'' FROM dbc.AllRoleRightsV,Parms WHERE DatabaseName=Parms.DbName AND TableName=Parms.TblName ORDER BY 1,2,3;"; using (DbConnection conn = DbUtility.Connect()) using (DbCommand sql = DbUtility.GenerateSQL(conn, query)) using (var reader = sql.ExecuteReader()) { List <TDRights> tableRights = new List <TDRights>(); while (reader.Read()) { TDRights right = new TDRights { Username = reader.GetString(reader.GetOrdinal("UserName")), RightType = reader.GetString(reader.GetOrdinal("AccessRight")), GrantorName = reader.GetString(reader.GetOrdinal("GrantorName")) }; tableRights.Add(right); } ; return(Ok(tableRights)); } }