protected override void PopulateTables(DbObjectCollection <Table> tablesCollection) { using (CreateConnectionScope()) { using (var tables = Connection.GetSchema("Tables", new[] { null, null, null, "Base Table" })) { foreach (DataRow row in tables.Rows) { var table = new SqlTable(this, (string)row["TABLE_SCHEMA"], (string)row["TABLE_NAME"]); tablesCollection.Add(table); } } using (var dataTable = ExecuteTable( "SELECT " + " t.name AS TableName, " + " s.name AS SchemaName, " + " p.rows AS RowCounts, " + " (SUM(a.total_pages) * 8) AS TotalSpaceKB, " + " (SUM(a.used_pages) * 8) AS UsedSpaceKB " + "FROM sys.tables t " + "INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id " + "INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id " + "INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id " + "LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id " + "WHERE " + " t.NAME NOT LIKE 'dt%' " + " AND t.is_ms_shipped = 0 " + " AND i.OBJECT_ID > 255 " + "GROUP BY " + " t.Name, s.Name, p.Rows")) { foreach (DataRow row in dataTable.Rows) { var schema = (string)row["SchemaName"]; var name = (string)row["TableName"]; foreach (SqlTable table in tablesCollection) { if (table.SchemaName == schema && table.TableName == name) { table.TotalRows = Convert.ToInt64(row["RowCounts"]); table.TotalSizeKB = Convert.ToDecimal(row["TotalSpaceKB"]); table.TotalUsedKB = Convert.ToDecimal(row["UsedSpaceKB"]); break; } } } } using (var views = Connection.GetSchema("Tables", new[] { null, null, null, "View" })) { foreach (DataRow row in views.Rows) { var table = new SqlTable(this, (string)row["TABLE_SCHEMA"], (string)row["TABLE_NAME"]); table.SetView(true); tablesCollection.Add(table); } } } }
protected override void PopulateTables(DbObjectCollection <Table> tablesCollection) { using (this.CreateConnectionScope()) { var restrictions = new string[4]; restrictions[3] = "Base Table"; using (var tables = this.Connection.GetSchema("Tables", restrictions)) { foreach (DataRow row in tables.Rows) { var table = new SqlTable(this); table.Schema = (string)row["TABLE_SCHEMA"]; table.Name = (string)row["TABLE_NAME"]; tablesCollection.Add(table); } } var dataTable = this.ExecuteTable(@" SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, (SUM(a.total_pages) * 8) AS TotalSpaceKB, (SUM(a.used_pages) * 8) AS UsedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows"); foreach (DataRow row in dataTable.Rows) { var schema = (string)row["SchemaName"]; var name = (string)row["TableName"]; foreach (SqlTable table in tablesCollection) { if (table.Schema == schema && table.Name == name) { table.TotalRows = Convert.ToInt64(row["RowCounts"]); table.TotalSizeKB = Convert.ToDecimal(row["TotalSpaceKB"]); table.TotalUsedKB = Convert.ToDecimal(row["UsedSpaceKB"]); break; } } } restrictions[3] = "View"; using (var views = this.Connection.GetSchema("Tables", restrictions)) { foreach (DataRow row in views.Rows) { var table = new SqlTable(this); table.Schema = (string)row["TABLE_SCHEMA"]; table.Name = (string)row["TABLE_NAME"]; table.SetView(true); tablesCollection.Add(table); } } } }