How to get columns Primary key constraints using SqlConnection.GetSchema()

Question

I have some code of ADO.NET to dynamically detect the database schema, what I need is how to get unique columns constraints and Primary key constraints using the GetSchema method on SqlConnection. This is the code that I have:

conn.Open();     
SqlCommand mSqlCommand = new SqlCommand("sp_pkeys", conn);
mSqlCommand.CommandType = CommandType.StoredProcedure;
mSqlCommand.Parameters.Add(
    "@table_name", SqlDbType.NVarChar).Value = tableName;
SqlDataReader mReader = mSqlCommand.ExecuteReader(
    (CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly));
//ExecuteReader();
DataTable schema = mReader.GetSchemaTable();
mReader.Close();
conn.Close();

Caracaraballo

Answer 1

There is nothing in the call to GetSchemaTable on SqlConnection which will allow you to figure this out.

It might seem that you can, using the IsKey column value, which should return true for anything that contributes to uniquely identifying the record in the table. However, from the documentation for the IsKey column (emphasis mine):

true : The column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with IsKey set to true must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns. This set of columns may be generated from a base table primary key, a unique constraint or a unique index.

Because of this, you can't guarantee that it contributes to a primary key per-se.

Now, if all you need is something to uniquely identify the row, then IsKey is fine, as the primary key is not always the way to uniquely identify a row (e.g. you can have natural identifiers with a unique index). Even if you have a primary key and a unique index with other columns, the values across all those columns in combination will always be unique.

However, if you specifically need to look at the columns that make up the primary key, then GetSchemaTable will not give you the information you need. Rather, you can just make a call to the sp_pkeys system stored procedure to find the names of the columns that contribute to making the primary key.

Illiquid

thanks for reply.from your explanation i don't understand what i need to change on my above C# syntax.Will you plz say what i need to change on my code.thanks – Caracaraballo

@shamim: See the link for the sp_keys stored procedure, you will have to call this stored procedure to get the columns that make up the primary key for a table. – Illiquid

in my above syntax i use sp_keys procedure there i pass the table name ,in my datatable i get schema but from the schema i fail to detect the primary key.So i seek your suggestion and help.Thanks – Caracaraballo

i don't like to use .SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = "sp_pKeys "+tableName; DataTable dtGet = new DataTable(); da.Fill(dtGet); return dtGet;...............I want to retrive as i descrived above. – Caracaraballo

@shamim: Why not use a SqlCommand where the CommandType is CommandType.StoredProcedure, set the CommandText to sp_keys, and then add the parameters to the Parameters property on the SqlCommand. You can then call ExecuteDataTable (or ExecuteDataReader to get the result). – Illiquid

@shamim: When you call sp_keys, you have to read the results from the SqlDataReader/DataTable that is returned, calling GetSchemaTable will never give you the information you are looking for. – Illiquid

Answer 2

In case still anyone needs a solutions i have created a function for this, the Sqlcommand contains the statement you want to get schema information.

Public Shared Function TableFromCommand(ByVal Command As SqlCommand) As DataTable

    Dim Cn As SqlConnection = Nothing
    Dim Dt As DataTable
    Dim Dr As SqlDataReader
    Dim Column As DataColumn
    Dim Answer As New DataTable

    Try
        Answer.TableName = "SearchTable"
        Cn = New SqlConnection("Your connection string")
        Cn.Open()

        Command.Connection = Cn
        For Each Prm As SqlParameter In Command.Parameters
            If Prm.Direction = ParameterDirection.Input _
            OrElse Prm.Direction = ParameterDirection.InputOutput Then
                Prm.Value = DBNull.Value
            End If
        Next

        Dr = Command.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)
        Dt = Dr.GetSchemaTable
        Dim Keys As New List(Of DataColumn)
        Dim ColumnsDic As New SortedDictionary(Of Integer, DataColumn)

        For Each Row As DataRow In Dt.Rows
            Column = New DataColumn
            With Column
                .ColumnName = Row("ColumnName").ToString
                .DataType = Type.GetType(Row("DataType").ToString)
                .AllowDBNull = CBool(Row("AllowDBNull"))
                .Unique = CBool(Row("IsUnique"))
                .ReadOnly = CBool(Row("IsReadOnly"))

                If Type.GetType(Row("DataType").ToString) Is GetType(String) Then
                    .MaxLength = CInt(Row("ColumnSize"))
                End If

                If CBool(Row("IsIdentity")) = True Then
                    .AutoIncrement = True
                    .AutoIncrementSeed = -1
                    .AutoIncrementStep = -1
                End If

                If CBool(Row("IsKey")) = True Then
                    Keys.Add(Column)
                End If
            End With

            ColumnsDic.Add(CInt(Row("ColumnOrdinal")), Column)

            Answer.Columns.Add(Column)
        Next

        If Keys.Count > 0 Then
            Answer.Constraints.Add("PrimaryKey", Keys.ToArray, True)
        End If
    Catch ex As Exception
        MyError.Show(ex)
    Finally
        If Cn IsNot Nothing AndAlso Not Cn.State = ConnectionState.Closed Then
            Cn.Close()
        End If
    End Try

    Return Answer

End Function

Guesthouse

Answer 3

You can get the primaryKeys, UniqueKeys and ForeignKeys and any other schema listed in the dataTable returned by this command: "connection.GetSchema (" MetaDataCollections ")"

Below a code that returns you the primaryKeys and UniqueKeys (Key name and column name).

Look all documentation Here

    public void Dotransfer()
    {
        var sourceSchema = new TableSchema(SourceConnectionString);

    }



  public class TableSchema
    {
        public TableSchema(string connectionString)
        {
            this.TableList = new List<string>();
            this.ColumnList = new List<Columns>();
            this.PrimaryKeyList = new List<PrimaryKey>();
            this.ForeignKeyList = new List<ForeignKey>();
            this.UniqueKeyList = new List<UniqueKey>();

            GetDataBaseSchema(connectionString);

        }

        public List<string> TableList { get; set; }
        public List<Columns> ColumnList { get; set; }
        public List<PrimaryKey> PrimaryKeyList { get; set; }
        public List<UniqueKey> UniqueKeyList { get; set; }
        public List<ForeignKey> ForeignKeyList { get; set; }


        protected void GetDataBaseSchema(string ConnectionString)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {

                System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
                builder.ConnectionString = ConnectionString;
                string server = builder.DataSource;
                string database = builder.InitialCatalog;

                connection.Open();


                DataTable schemaTables = connection.GetSchema("Tables");

                foreach (System.Data.DataRow rowTable in schemaTables.Rows)
                {
                    String tableName = rowTable.ItemArray[2].ToString();
                    this.TableList.Add(tableName);

                    string[] restrictionsColumns = new string[4];
                    restrictionsColumns[2] = tableName;
                    DataTable schemaColumns = connection.GetSchema("Columns", restrictionsColumns);

                    foreach (System.Data.DataRow rowColumn in schemaColumns.Rows)
                    {
                        string ColumnName = rowColumn[3].ToString();
                        this.ColumnList.Add(new Columns(){TableName= tableName, FieldName = ColumnName});
                    }

                    string[] restrictionsPrimaryKey = new string[4];
                    restrictionsPrimaryKey[2] = tableName;
                    DataTable schemaPrimaryKey = connection.GetSchema("IndexColumns", restrictionsColumns);


                    foreach (System.Data.DataRow rowPrimaryKey in schemaPrimaryKey.Rows)
                    {
                        string indexName = rowPrimaryKey[2].ToString();

                        if (indexName.IndexOf("PK_") != -1)
                        {
                            this.PrimaryKeyList.Add(new PrimaryKey()
                            {
                                TableName = tableName,
                                FieldName = rowPrimaryKey[6].ToString(),
                                PrimaryKeyName = indexName
                            });
                        }

                        if (indexName.IndexOf("UQ_") != -1)
                        {
                            this.UniqueKeyList.Add(new UniqueKey()
                            {
                                TableName = tableName,
                                FieldName = rowPrimaryKey[6].ToString(),
                                UniqueKeyName = indexName
                            });
                        }

                    }


                    string[] restrictionsForeignKeys = new string[4];
                    restrictionsForeignKeys[2] = tableName;
                    DataTable schemaForeignKeys = connection.GetSchema("ForeignKeys", restrictionsColumns);


                    foreach (System.Data.DataRow rowFK in schemaForeignKeys.Rows)
                    {

                        this.ForeignKeyList.Add(new ForeignKey()
                        {
                            ForeignName = rowFK[2].ToString(),
                            TableName = tableName,
                            // FieldName = rowFK[6].ToString() //There is no information
                        });                
                    }


                }


            }

        }

    }    

    public class Columns
    {
        public string TableName { get; set; }
        public string FieldName { get; set; }
    }

    public class PrimaryKey
    {
        public string TableName { get; set; }
        public string PrimaryKeyName { get; set; }
        public string FieldName { get; set; }
    }


    public class UniqueKey
    {
        public string TableName { get; set; }
        public string UniqueKeyName { get; set; }
        public string FieldName { get; set; }
    }

    public class ForeignKey
    {
        public string TableName { get; set; }
        public string ForeignName { get; set; }
       // public string FieldName { get; set; } //There is no information
    }

Heirdom

Answer 4

What about calling GetSchema() on your SqlConnection? Using collectionName="IndexColumns" and a list of schema restrictions you can request the information you want using GetSchema().

See:

• GetSchema and Schema Collections
• Schema Restrictions

Once I established the SqlConnection using the database name, the following worked for me:

var connectionString = 
    string.Format("Server=.\\SQLEXPRESS;Database={0};Trusted_Connection=true", dbName);
using (var sqlConnection = new SqlConnection(connectionString))
{
    sqlConnection.Open();
    DataTable tables = sqlConnection.GetSchema("Tables");
    foreach (DataRow tablesRow in tables.Rows)
    {
        string tableName = tablesRow["table_name"].ToString();
        Console.WriteLine(tableName);
        var indexCols = sqlConnection.GetSchema("IndexColumns",
            new string[] {dbName, null, tableName, "PK_" + tableName, null});
        foreach (DataRow indexColsRow in indexCols.Rows)
            Console.WriteLine("  PK: {0}", indexColsRow["column_name"]);
    }
}

Randall