CODEWITHSUNDEEP
c#sql-server
Julien N
Julien N

Reputation: 3920

Get database schema when getting the table metadata

Please note that when I using the word "schema" I mean Schema as the security feature provided by Sql Server ("dbo"...) that you must provide with the table name if you want your query to succeed, NOT its metadata (columns...).

I'm using this bit of code to get the table metadata (columns, types, etc) :

// Parameter table includes the schema name.
public DataTable GetTableSchema(string table)
{
    var tbl = new DataTable();
    using (var conn = new SqlConnection(ConnectionString))
    using (var adapter = new SqlDataAdapter(String.Format("SELECT * FROM {0} WHERE 1=0", table), conn))
    {
        tbl = adapter.FillSchema(tbl, SchemaType.Source);
    }    
    return tbl;
}

My issue is that the property DataTable.TableName doesn't contain the table schema ("dbo", or any custom schema) and I can't find any property in the object that allows me to get that information, so it is lost during the process (or I have to pass several variables to methods, while I'd like to keep everything in the DataTable object, which should be logical).
Where / how can I get it along with the database structure ?

Only solution I found : adding tbl.TableName = table; before returning the table but it feels... wrong.

Upvotes: 0

Views: 2529

Answers (1)

Steve
Steve

Reputation: 216293

You could query the INFORMATION_SCHEMA.TABLES view and get your info from the TABLE_SCHEMA field

 "SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES where table_name = '" + table "'";

It is not clear why you need this info, so I can only suggest to run this code

SqlCommand cmd = new SqlCommand(@"SELECT TABLE_SCHEMA 
                                 FROM INFORMATION_SCHEMA.TABLES 
                                 WHERE TABLE_NAME = @table", con);
cmd.Parameters.Add("@table", SqlDbType.NVarChar).Value = table;
string schema = (string)cmd.ExecuteScalar();

There is also an alternative, that could return some of the info on your table and the table_schema in a single call

DataTable dt = new DataTable();
dt = cnn.GetSchema("COLUMNS", new string[] { null, null, table, null });
Console.WriteLine(dt.Rows[0]["TABLE_SCHEMA"].ToString());

This code returns a lot of info about your table and also the TABLE_SCHEMA colum.
Not sure if this approach is suitable for your purpose because here the datatable returned contains a row for each column and each column of this DataTable contains details about the columns (Like OrdinalPosition, IsNullable, DataType, Character_Maximum_Length)

Upvotes: 3

Related Questions