-1

With Oracle I do this with DbConnection.GetSchema, but MSDN tells quite clearly that it is not possible with SQLSRV:

https://msdn.microsoft.com/en-us/library/ms254969(v=vs.110).aspx#Indexes

So how this can be done when table, index name and index columns are known? I already have code that reads everything else (except index uniqueness in case of SQLSRV) I need from Db with all providers I need.

char m
  • 7,840
  • 14
  • 68
  • 117
  • thanks for commenting the reason of ur down vote. let's make SO great again! – char m Mar 21 '17 at 15:02
  • 1
    What is the question actually? I didn't downvote, but *what* are you trying to do and why are you using GetSchema to do it? If, as you say, the documentation says that you can't use GetSchema for this, why are you using it instead of querying the `sys` views? – Panagiotis Kanavos Mar 21 '17 at 15:27
  • actually i deserved it, but i would really appreciate explanation when downvoted. the documentation is many years old so I thought it could be now possible with some equivalent. i rewrite my question. i would like to hear mode @PanagiotisKanavos about sys views. – char m Mar 21 '17 at 20:06
  • thanks! I think I go with sys.indexes. if you want to write short answer i accept it. – char m Mar 21 '17 at 20:24

1 Answers1

0

This is just for infromation if somebody has same problem. Thanks to @Panagiotis Kanavos for gettting me to this path.

SQL Server is supposed to be case insensitive, but as I am not sure if that is case in all installations at all times, I have used UPPER for table names. NOTE that this can make it considerably slower so if sure that it's case insensitive, don't do it like this:

    private static bool GetSqlServerIndexUniqueness(DbConnection cnctn, string tableName, string indexName)
    {
        bool isUnique = false;
        using (SqlCommand cmd = ((SqlConnection) cnctn).CreateCommand())
        {
             cmd.CommandText =
                    "SELECT is_unique FROM sys.indexes WHERE name = @IndexName AND object_id = " +
                    "(SELECT object_id FROM sys.tables WHERE upper(name) = @TableNameUpper)";

            cmd.Parameters.AddWithValue("@IndexName", indexName);
            cmd.Parameters.AddWithValue("@TableNameUpper", tableName.ToUpper());
            var readValue = cmd.ExecuteScalar();
            if (readValue != null)
                isUnique = Convert.ToInt32(readValue) != 0;
        }
        return isUnique;
    }

If indexName is uniquethe table / object_id subquery is not needed. This will make it a lot faster.

char m
  • 7,840
  • 14
  • 68
  • 117