List <TableIndex> LoadIndices(Table tbl)
{
const string sql = @"SELECT DISTINCT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],
I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key],
I.[is_unique_constraint], I.[fill_factor], I.[is_padded], I.[is_disabled], I.[is_hypothetical],
I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column]
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
AND I.is_primary_key = 0 AND T.[name] = @tableName and OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) = @schemaName";
using (var cmd = this._factory.CreateCommand())
{
cmd.Connection = this._connection;
cmd.CommandText = sql;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value = tbl.Name;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "@schemaName";
p.Value = tbl.Schema;
cmd.Parameters.Add(p);
var result = new List <TableIndex>();
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string thisTable = rdr["table_name"].ToString();
if (tbl.Name.ToLower() == thisTable.ToLower())
{
var indexName = rdr["index_name"].ToString();
if (!result.Exists(i => i.Name == indexName))
{
TableIndex index = new TableIndex();
index.Name = indexName;
index.IsUnique = rdr.GetBoolean(rdr.GetOrdinal("is_unique"));
index.IndexColumns = new List <IndexColumn>();
index.IndexColumns.Add(new IndexColumn {
Name = rdr["column_name"].ToString(), IsAsc = !rdr.GetBoolean(rdr.GetOrdinal("is_descending_key"))
});
result.Add(index);
}
else
{
result.Single(i => i.Name == indexName).IndexColumns.Add(new IndexColumn {
Name = rdr["column_name"].ToString(), IsAsc = !rdr.GetBoolean(rdr.GetOrdinal("is_descending_key"))
});
}
}
}
}
return(result);
}
}
List<TableIndex> LoadIndices(string tableName)
{
var result=new List<TableIndex>();
using (var cmd1=this._factory.CreateCommand())
{
cmd1.Connection=this._connection;
cmd1.CommandText=String.Format(INDEX_SQL,tableName);
using (IDataReader rdr1=cmd1.ExecuteReader())
{
while(rdr1.Read())
{
TableIndex indx=new TableIndex();
indx.Name=rdr1["name"].ToString();
indx.SQL=rdr1["sql"].ToString();
indx.IndexColumns = new List<IndexColumn>();
indx.IsUnique = indx.SQL.ToUpper().Contains("UNIQUE");
using (var cmd2=this._factory.CreateCommand())
{
cmd2.Connection=this._connection;
cmd2.CommandText=String.Format(INDEX_INFO_SQL,indx.Name);
using (IDataReader rdr2=cmd2.ExecuteReader())
{
while(rdr2.Read())
{ IndexColumn col = new IndexColumn();
col.Name = rdr2["name"].ToString();
indx.IndexColumns.Add(col);
}
}
}
result.Add(indx);
}
}
}
return result;
}
List<TableIndex> LoadIndices(string schemaName, string tableName)
{
//var result=new List<TableIndex>();
var indquery = string.Format(@"
select indlst.relname as indname,indlst.oid as indoid,indlst.indkey as indkey,tbllst.oid as tbloid,tbllst.relname as tblname from (select c.relname,c.oid,i.indkey from pg_index as i
inner join pg_class as c on i.indexrelid=c.oid
inner join pg_namespace as ns on c.relnamespace=ns.oid
where ns.nspname='{0}' and i.indisprimary = false) as indlst
inner join (select i.indexrelid,c.oid,c.relname from pg_index as i
inner join pg_class as c on i.indrelid=c.oid
where c.relname='{1}'
) as tbllst on indlst.oid = tbllst.indexrelid
", schemaName, tableName);
var colquery = @"
select att.attrelid,att.attname,att.attnum from pg_attribute as att
where att.attnum>0
";
using(var indTable = new DataTable())
using(var colTable = new DataTable())
{
using (var indcmd = this._factory.CreateCommand())
using (var colcmd = this._factory.CreateCommand())
{
indcmd.Connection = this._connection;
colcmd.Connection = this._connection;
indcmd.CommandText = indquery;
colcmd.CommandText = colquery;
using (var indadp = this._factory.CreateDataAdapter())
using (var coladp = this._factory.CreateDataAdapter())
{
indadp.SelectCommand = indcmd;
coladp.SelectCommand = colcmd;
indadp.Fill(indTable);
coladp.Fill(colTable);
}
}
var dic = new List<TableIndex>();
foreach(DataRow indrow in indTable.Rows)
{
var indname = indrow["indname"].ToString();
var indcols = indrow["indkey"].ToString().Split(' ');
var idx = new TableIndex();
idx.IndexColumns = new List<IndexColumn>();
idx.Name = indname;
idx.SQL = "";
idx.IndexColumns.AddRange(
colTable.Rows.Cast<DataRow>()
.Where(col => indcols.Any(indcolnum => col["attnum"].ToString() == indcolnum && indrow["tbloid"].ToString() == col["attrelid"].ToString()))
.Select(col => new IndexColumn() { IsAsc = true, Name = col["attname"].ToString() }))
;
dic.Add(idx);
}
return dic;
}
}
