C# Reading SQL tables and views

Question

Hi all,

I'm currently reading in a list of tables in a SQL database and populating a comboBox with the table names. I want to include SQL Views in the same list. The sysobjects type for tables is 'U' and for views it's 'V'. How would I alter the OdbcCommand line to retrieve both U and V? Thanks.

OdbcConnection cn=getConnection(); 
OdbcCommand cmdList; 
cmdList = new OdbcCommand("select name, user_name(uid) from sysobjects where type='U'",cn);
cn.Open();

        OdbcDataReader reader = cmdList.ExecuteReader();
        while (reader.Read())
        {
            for (int i=0;i<reader.FieldCount;i++)
            {
             if (!reader.IsDBNull(i))
             {
              if (reader.GetName(i).ToUpper()=="NAME")
              {
                            comboBoxTables.Items.Add(reader.GetString(i));
              }
             }
            }
        }
cn.Close();

Answer 1

Try:

"... where type='U' or type='V'"

Answer 2

What version of SQL Server are you using? If it is anything past Sql 2000, do not use sysobjects because it has been deprecated. Use sys.objects instead.

Also you should probably be using SMO (SQL Server Management Objects) instead of writing your own query.