So, I'm trying to build a code generator that will extract indexes from a database and make a class that will filter based on an index. Below code works in SQL-server and yields 2 records. But my SqlDataReader yields zero records. Provided example for 1 table with index. Hoping someone can help me out here.
Code in SQL server:
create table Agent(
ID bigint constraint PK_Agent primary key identity(1,1),
LastName nvarchar(50) not null,
FirstName nvarchar(50) not null,
index IN_Agent_Name nonclustered (LastName, FirstName)
)
select t.object_id,
s.name as schemaname,
t.name as tablename,
i.index_id,
i.name as indexname,
index_column_id,
c.name as columnname
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = t.object_id and ic.column_id = c.column_id
where i.index_id > 0
and i.type in (1, 2)
and i.is_primary_key = 0
and i.is_unique_constraint = 0
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
and t.name like 'Agent'
and i.name like 'IN_Agent_Name'
Code in VS:
public static TableIndex GetIndex(string indexName, string tableName)
{
TableIndex index = null;
using (var conn = new SqlConnection("Server=localhost;Database=VenturaERD;User Id=VenturaDBUser;Password = Ventura;"))
{
conn.Open();
var cmd = new SqlCommand("select t.object_id, s.name as schemaname, t.name as tablename, i.index_id, i.name as indexname, index_column_id, c.name as columnname from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id = i.index_id inner join sys.columns c on c.object_id = t.object_id and ic.column_id = c.column_id where i.index_id > 0 and i.type in (1, 2) and i.is_primary_key = 0 and i.is_unique_constraint = 0 and i.is_disabled = 0 and i.is_hypothetical = 0 and ic.key_ordinal > 0 and t.name like '" + tableName + "' and i.name like '" + indexName + "'")
{
Connection = conn
};
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
index = new TableIndex()
{
TableId = reader.GetInt32(reader.GetOrdinal("object_id")),
TableName = reader.GetString(reader.GetOrdinal("tablename")),
IndexId = reader.GetInt32(reader.GetOrdinal("index_id")),
IndexName = reader.GetString(reader.GetOrdinal("indexname")),
Columns = new List()
{
new IndexColumn()
{
ColumnName = reader.GetString(reader.GetOrdinal("columnname")),
Order=reader.GetInt32(reader.GetOrdinal("index_column_id"))
}
}
};
while (reader.Read())
{
index.Columns.Add(new IndexColumn()
{
ColumnName = reader.GetString(reader.GetOrdinal("columnname")),
Order = reader.GetInt32(reader.GetOrdinal("index_column_id"))
});
}
}
reader.Close();
}
}
return index;
}
