public virtual ConstraintColumn Clone()
{
ConstraintColumn column = new ConstraintColumn();
column.Name = this.Name;
column.Order = this.Order;
column.Descending = this.Descending;
column.IsIncludedColumn = this.IsIncludedColumn;
column.IsIdentity = this.IsIdentity;
return(column);
}
public List <Constraint> GetIndexesForDatabase()
{
// https://dba.stackexchange.com/questions/63185/listing-indexes-and-constraints
// http://sqldbpros.com/2013/02/sql-query-list-all-indexes-and-their-columns/
List <Constraint> response = new List <Constraint>();
// This union selects indexes for tables and then unions on a select for views
string query = @"
SELECT OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName
, OBJECT_NAME(ind.object_id) AS TableName
, ind.name AS IndexName
, ind.is_primary_key AS IsPrimaryKey
, ind.is_unique AS IsUnique
, col.name AS ColumnName
, ic.is_included_column AS IsIncludedColumn
, ic.key_ordinal AS ColumnOrder
, ind.type_desc as IndexType
, ic.is_descending_key IsDescending
, col.is_identity IsIdentity
FROM sys.indexes ind
INNER JOIN sys.index_columns ic
ON ind.object_id = ic.object_id
AND ind.index_id = ic.index_id
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
INNER JOIN sys.tables t
ON ind.object_id = t.object_id
WHERE t.is_ms_shipped = 0
UNION
SELECT OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName
, OBJECT_NAME(ind.object_id) AS TableName
, ind.name AS IndexName
, ind.is_primary_key AS IsPrimaryKey
, ind.is_unique AS IsUnique
, col.name AS ColumnName
, ic.is_included_column AS IsIncludedColumn
, ic.key_ordinal AS ColumnOrder
, ind.type_desc as IndexType
, ic.is_descending_key IsDescending
, col.is_identity IsIdentity
FROM sys.indexes ind
INNER JOIN sys.index_columns ic
ON ind.object_id = ic.object_id
AND ind.index_id = ic.index_id
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
INNER JOIN sys.views t -- THIS IS WHAT IS DIFFERENT IN THIS BLOCK
ON ind.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName
, OBJECT_NAME(ind.object_id) --ObjectName
, ind.is_primary_key DESC
, ind.is_unique DESC
, ind.name --IndexName
, ic.key_ordinal
";
using (SqlConnection conn = new SqlConnection(this._connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
command.CommandTimeout = 300;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var index = new Constraint
{
Name = reader["IndexName"].ToString(),
ConstraintType = reader["IndexType"].ToString(),
IsPrimaryKey = Convert.ToBoolean(reader["IsPrimaryKey"].ToString()),
SchemaName = reader["SchemaName"].ToString(),
TableName = reader["TableName"].ToString(),
IsUnique = Convert.ToBoolean(reader["IsUnique"].ToString())
};
//This is to handle the unique index on views
if (index.IsUnique && index.ConstraintType.ToUpper() == "CLUSTERED")
{
index.IsPrimaryKey = true;
}
var column = new ConstraintColumn
{
Name = reader["ColumnName"].ToString(),
Order = Convert.ToInt32(reader["ColumnOrder"].ToString()),
Descending = Convert.ToBoolean(reader["IsDescending"].ToString()),
IsIncludedColumn = Convert.ToBoolean(reader["IsIncludedColumn"].ToString()),
IsIdentity = Convert.ToBoolean(reader["IsIdentity"].ToString())
};
if (response.Any(x => x.Name == index.Name && x.TableName == index.TableName && x.SchemaName == index.SchemaName))
{
response.FirstOrDefault(x => x.Name == index.Name && x.TableName == index.TableName && x.SchemaName == index.SchemaName).Columns.Add(column);
}
else
{
index.Columns.Add(column);
response.Add(index);
}
}
}
return(response);
}
public List <Constraint> GetIndexesForDatabase()
{
List <Constraint> response = new List <Constraint>();
// this query is incomplete. amcanorder was removed from pg_catalog.pg_am in the latest versions of PostGres
// And I don't know where to get the index sort direction
string query = @"SELECT tnsp.nspname AS SchemaName,
ct.relname AS TableName,
replace(pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false),'""','') AS ColumnName,
i.indisunique As IsUnique,
i.indisclustered As IsClustered,
i.indisprimary As IsPrimaryKey,
ci.relname AS IndexName,
(i.keys).n AS ColumnOrder,
am.amname as IndexType,
-- CASE am.amcanorder
-- WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1
-- WHEN 1 THEN 'DESC'
-- ELSE 'ASC'
-- END
-- ELSE NULL
-- END AS ASC_OR_DESC,
pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION
FROM pg_catalog.pg_class ct
JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)
JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,
i.indisunique, i.indisclustered, i.indpred,
i.indisprimary,
i.indexprs,
information_schema._pg_expandarray(i.indkey) AS keys
FROM pg_catalog.pg_index i) i
ON (ct.oid = i.indrelid)
JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)
JOIN pg_catalog.pg_am am ON (ci.relam = am.oid)
JOIN pg_class AS trel ON trel.oid = i.indrelid
JOIN pg_namespace AS tnsp ON trel.relnamespace = tnsp.oid
ORDER BY ct.relname, (i.keys).n";
using (NpgsqlConnection conn = new NpgsqlConnection(this._connectionString))
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand(query, conn);
command.CommandTimeout = 300;
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var index = new Constraint
{
Name = reader["IndexName"].ToString(),
ConstraintType = reader["IsClustered"].ToString().ToBoolean() ? "CLUSTERED" : "NONCLUSTERED",
IsPrimaryKey = Convert.ToBoolean(reader["IsPrimaryKey"].ToString()),
SchemaName = reader["SchemaName"].ToString(),
TableName = reader["TableName"].ToString(),
IsUnique = Convert.ToBoolean(reader["IsUnique"].ToString())
};
var column = new ConstraintColumn
{
Name = reader["ColumnName"].ToString(),
Order = Convert.ToInt32(reader["ColumnOrder"].ToString()),
Descending = false, // Convert.ToBoolean(reader["IsDescending"].ToString()),
IsIncludedColumn = false, // Convert.ToBoolean(reader["IsIncludedColumn"].ToString())
IsIdentity = Convert.ToBoolean(reader["IsPrimaryKey"].ToString())
};
if (response.Any(x => x.Name == index.Name && x.TableName == index.TableName && x.SchemaName == index.SchemaName))
{
response.FirstOrDefault(x => x.Name == index.Name && x.TableName == index.TableName && x.SchemaName == index.SchemaName).Columns.Add(column);
}
else
{
index.Columns.Add(column);
response.Add(index);
}
}
}
return(response);
}