CODEWITHSUNDEEP
c#sqlview
Jade M
Jade M

Reputation:

C# Reading SQL tables and views

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();

Upvotes: 1

Views: 4237

Answers (2)

jhale
jhale

Reputation: 1830

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.

Upvotes: 1

John Feminella
John Feminella

Reputation: 311476

Try:

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

Upvotes: 4

Related Questions