getting Schema of one Table in C#

Question

I want to get Schema for a table with name "Petro" in SQL SErver after initializing connectionString, I use this Code

conn.open();
conn.getSchema("Tables");

but it returns schema for all the tables. I only want the Petro schema. What should I do?

Here is an answer: [Ado.net Getting the schema for a table](http://stackoverflow.com/questions/173834/ado-net-getting-the-schema-for-a-table) — Anatolii Gabuza, Jun 23 '12 at 12:08
possible duplicate of [How to read SQL server schema info in .net?](http://stackoverflow.com/questions/8550549/how-to-read-sql-server-schema-info-in-net) — slugster, Jun 23 '12 at 12:19

score 18 · Accepted Answer · edited Jul 12 '15 at 13:38

18

string[] restrictions = new string[4];
restrictions[2] = "Petro";
DataTable table = conn.GetSchema("Tables",restrictions);

Look here for more information: MSDN: Working with the GetSchema Methods

Edit: use GetSchema instead of getSchema

edited Jul 12 '15 at 13:38

Zbun

4,875
4
19
28

answered Jun 23 '12 at 12:09

Jason De Oliveira

1,732
9
16

5

This code will return only 1 row with the name of the table. If it is needed to get a full table schema (all columns with their types) the code should be the following: `conn.GetSchema("Columns", restrictions);` – dyatchenko Oct 29 '16 at 01:05
this returns only table property not table field. – Rakibul Feb 06 '19 at 06:10

score 8 · Answer 2 · edited Dec 15 '16 at 20:24

8

You can retrieve the schema in following way:

            string sql = "select * from Petro WHERE 1 = 0";
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable schema = reader.GetSchemaTable();

edited Dec 15 '16 at 20:24

Denis

11,796
16
88
150

answered Jun 23 '12 at 12:15

Akash KC

16,057
6
39
59

1

Do you really want to Select * on Petro just to get the schema? It looks like the cmd.ExecuteReader adds a lot of un-needed overhead to this. – MBentley Feb 18 '15 at 15:34
As OP has not mentioned any column name in the question except table name, I have answered on the basis of that... – Akash KC Feb 19 '15 at 03:52
Hate to admit it (6 years later) but this was the only code which worked for me... – Mike Gledhill Feb 21 '21 at 20:14

score 5 · Answer 3 · answered Jun 23 '12 at 12:09

5

SQL Server solution:

There is a store procedure called sp_columns. When you run that procedure with the table's name as a parameter, it will return the schema just for that table.

answered Jun 23 '12 at 12:09

Sascha

10,231
4
41
65

getting Schema of one Table in C#

3 Answers3