Programmatically Retrieving MS SQL View's columns including Alias in .net/C#?

Question

How to retrieve the columns (including alias) name, tables involved in a 'View' in C#?

Answer 1

Hi,

You can just query it as you would do a normal table. The alias names should come out like regular column names.

SqlConnection con = new SqlConnection("MyConnectionString");

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter("Select * FROM vw_MyView",con);

da.fill(ds);

Answer 2

You can invoke the following sql from C# in a number of ways. I'll let you figure out which one to use ;)

Select c.* From sys.all_views v join sys.all_columns c on c.object_id = v.object_id Where v.name = 'MyView'

Answer 3

How about GetSchema on the connection, i.e:

Dim conn As New SqlConnection(
"Data Source=YOURMACHINE;initial catalog=YOURDB;Integrated security=true")

conn.Open()

Dim dt As New DataTable

Dim viewName(3) As String
viewName(0) = Nothing
viewName(1) = Nothing
viewName(2) = "YOURVIEWNAME"


dt = conn.GetSchema(SqlClientMetaDataCollectionNames.Views, viewName)

i.e http://msdn.microsoft.com/en-us/library/ms136367(VS.80).aspx