Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ADO.Net : Get table definition from SQL server tables

I am using C# to write a method that returns the following information about a table: column names, column types, column sizes, foreign keys.

Can someone point me in the right direction on how to accomplish this ?

like image 733
javito Avatar asked Jan 19 '09 12:01

javito


4 Answers

This really depends on how you communicate with your database. If you are using LinqToSQL or another similar ORM this would be pretty easy but if you want to get these values via a query I'd suggest you use the INFORMATION_SCHEMA views as these are fast and easy to query.

e.g.

select * from information_schema.columns where table_name = 'mytable'
like image 175
Chris Simpson Avatar answered Sep 19 '22 01:09

Chris Simpson


To get the FK and Schema you should be able to use:

DA.FillSchema() 

DS.Table("Name").PrimaryKey 

OR calling sp_fkey using the method demonstrated below

Code Snippet from AND Another Link

    private void LoanSchema()
    {

         private List<String> tablesList = new List<String>();
         private Dictionary<String, String> columnsDictionary = new Dictionary<String, String>();

          string connectionString = "Integrated Security=SSPI;" +
          "Persist Security Info = False;Initial Catalog=Northwind;" +
          "Data Source = localhost";
          SqlConnection connection = new SqlConnection();
          connection.ConnectionString = connectionString;
          connection.Open();

          SqlCommand command = new SqlCommand();
          command.Connection = connection;
          command.CommandText = "exec sp_tables";
          command.CommandType = CommandType.Text;

          SqlDataReader reader = command.ExecuteReader();

           if (reader.HasRows)
           {
               while (reader.Read())
                  tablesList.Add(reader["TABLE_NAME"].ToString());
           }
           reader.Close();

           command.CommandText = "exec sp_columns @table_name = '" +
           tablesList[0] + "'";
           command.CommandType = CommandType.Text;
           reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                          columnsDictionary.Add(reader["COLUMN_NAME"].ToString(), reader["TYPE_NAME"].ToString());
             }
}
like image 35
cgreeno Avatar answered Sep 22 '22 01:09

cgreeno


You can use the SqlDataAdapter.FillSchema() method.

Alternatively you can use the SqlDataAdapter.Fill() method after setting the MissingSchemaAction property of the SqlDataAdapter to AddWithKey. But if you only want the schema you must ensure that your query returns no rows. This can be accomplished by adding a statement like WHERE 1=2 to your query.

like image 21
Rune Grimstad Avatar answered Sep 19 '22 01:09

Rune Grimstad


If you are using MS SQL Server then You should definately have a look at SMO namespace (server management objects).

There are objects which You can use in .net responsible for all kinds of things in a database (including but not limited to tables, columns, constraints etc.)

like image 30
luckyluke Avatar answered Sep 21 '22 01:09

luckyluke