CODEWITHSUNDEEP
c#sql
Awadhendra
Awadhendra

Reputation: 513

Retrieving table schema information using C#

I want to retrieve the table schema information using C#.

I used the sp_help <table_name> query to retrieve it, which works well when I execute it in query window but fail to retrieve it from C#. I want to retrieve all table information which is necessary for mapping between two tables such as name, datatype, size, isprimary, key, etc.

I had write down following code 

SqlCommand cmd = sourceCon.CreateCommand();
cmd.CommandText = string.Format("sp_help '{0}'", cmbSourceTable.SelectedItem.ToString()); //cmd.CommandType = CommandType.StoredProcedure; 
sourceDataTable = new DataTable(); 
SqlDataReader dr = cmd.ExecuteReader(); 
sourceDataTable.Load(dr);
dr.Close();

It will returns only info about table that when it is created or not

Upvotes: 1

Views: 13133

Answers (8)

Awadhendra
Awadhendra

Reputation: 513

I got a simple way to get schema of a table. Just create a connection with any provider like SqlConnection object. Create a command object for select query (select only top 1 record as you want only schema of table) Execute that query which return a DataReader object. Every DataReader object have a method called GetTableSchema() which returns DataTable object which have schema of a perticulat table.

This way you can easily get schema of any table with less effort.

Here I will provide a simple code for this

SqlConnection con=new SqlConnection("connString");
con.Open();
SqlCommand cmd= new SqlCommand("select top 0 * from Person.Address",con);
DataTable table = cmd.ExecuteReader().GetTableSchema();

Now this table object have schema information of Person.Address table.

Upvotes: 2

Goran
Goran

Reputation: 6846

Since .net 2.0 the SqlConnection class offers GetSchemaMethod you can use to retrieve the requested info.

http://msdn.microsoft.com/en-us/library/ms136364(v=vs.80).aspx

Upvotes: 0

NJV
NJV

Reputation: 187

Also, you can use the GetSchema method from SqlConnection. http://msdn.microsoft.com/en-us/library/ms136367.aspx

like so:

var tableSchema = con.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, "[tableName]" });

The string array is a 'filter', here you can find the columns you can filter on: http://msdn.microsoft.com/en-us/library/ms254969.aspx

For other database servers and more info: http://msdn.microsoft.com/en-us/library/kcax58fh.aspx

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

Instead of using sp_help, you could try the following options:

1) use INFORMATION_SCHEMA.COLUMNS view:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable'

2) query sys.columns

SELECT c.*, t.name
FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('YourTable')

These are just 2 options that will return a single resultset. INFORMATION_SCHEMA.COLUMNS doesn't tell you (e.g.) if a column is an IDENTITY column, wherease the sys.columns route does. There's more information you can get from the catalog views, just depends what else you need. Here's the MSDN reference.

Upvotes: 4

Murph
Murph

Reputation: 10190

You might find it easier to play with the INFORMATION_SCHEMA views - you can build queries that will extract most of the data relating to the structure of and relationship between tables within a SQL Server database schema.

See here: http://msdn.microsoft.com/en-us/library/ms186778.aspx

Upvotes: 2

ipr101
ipr101

Reputation: 24236

I think sp_help returns multiple result sets so you'll need to use NextResult, there are more details here - http://support.microsoft.com/kb/311274 (the example is not sp_help specific but it should give you the general idea)

Upvotes: 1

Rahul
Rahul

Reputation: 77866

See this one ... it explains how

How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual C# .NET

Upvotes: -1

Dave Rael
Dave Rael

Reputation: 1759

sp_help doesn't work in your code? maybe you need to include an execute statement: exec sp_help. or maybe the application is running under an account that doesn't have permission for sp_help.

Upvotes: 0

Related Questions