Efficient way to check if a row, column etc exists in database?

Question

I have been using the following code to check if a particular row, column, table etc is present in the SQL Server database. I would like to know if there is a more efficient way to do this.
Note1: I am particularly trying to eliminate using the dataTable dt_local.
Note2: I am using C#, WinForms, VS-2015 and SQL Server 2008 R2 Express.

internal static bool MaujoodHaiKya(string queryLe)
    {
        //This function has been created to check
        //if a particular row, column, table etc exists
        //and to return true or false accordingly
        try
        {
            DataTable dt_local = new DataTable();
            string[] connString = System.IO.File.ReadAllLines(Application.StartupPath + "\\MM.ini");

            using (SqlConnection conn = new SqlConnection(connString[1]))
            {
                SqlCommand cmd = new SqlCommand(queryLe, conn);
                SqlDataAdapter adapter = new SqlDataAdapter();

                conn.Open();
                adapter.SelectCommand = cmd;
                adapter.Fill(dt_local);
            }
            return (true); //Column exists
        }
        catch
        {
            return (false); //Column does not exist
        }
    }

You can probably query the catalog directly (tables metadata) without loading any data — vc 74, Jun 15 '18 at 08:15
Or use executereader, and if returned something true, else false — BugFinder, Jun 15 '18 at 08:16
without knowing what `queryLe` is, it is hard to say how good or bad this is - it could be **horribly** inefficient; but, there's certainly no need to fill a data table with it - heck, just `ExecuteNonQuery` should be sufficient — Marc Gravell, Jun 15 '18 at 08:23
TIL 2008 R2 is still in extended support... just about; final cut-off is July 2019, but frankly you should **really, really** be moving as a priority now — Marc Gravell, Jun 15 '18 at 08:26
@MarcGravell - I am so sorry. A sample queryLe is `select ACTIV from PRH` — Prasad Kamath, Jun 15 '18 at 08:53
@MarcGravell / @BugFinder - could you pls elaborate with a small sample piece of code? I am unable to visualise how `ExecuteNonQuery` would be coded for this scenario. — Prasad Kamath, Jun 15 '18 at 08:57
@PrasadKamath that returns an entire table (all rows), but you just want to check for a single column; frankly, I'd be looking at `sys.columns` for this (or the information-schema stuff), rather than issuing actual data queries. — Marc Gravell, Jun 15 '18 at 09:17

score 2 · Answer 1 · answered Jun 15 '18 at 09:21

Your best bet here is probably to use database metadata rather than data queries. Consider:

select count(1) as [Count] from sys.columns
where [object_id] = object_id('PRH') and [name] = 'ACTIV'

This will return 0 if the column doesn't exist. With that you can use:

cmd.CommandText = @"
    select count(1) as [Count] from sys.columns
    where [object_id] = object_id('PRH') and [name] = 'ACTIV'";

int count = (int)cmd.ExecuteScalar();
return count == 1;

Note that this query can be parameterized if needed:

cmd.CommandText = @"
    select count(1) as [Count] from sys.columns
    where [object_id] = object_id(@table) and [name] = @column";
cmd.Parameters.AddWithValue("@table", tableName);
cmd.Parameters.AddWithValue("@column", columnName);

To do the same with information-schema:

select COUNT(1) as [Count] from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'PRH' and COLUMN_NAME = 'ACTIV'

(which can also be parameterized)

HaukurHaf · Answer 2 · 2018-06-15T08:28:10.980

1

You have some alternative options - which may or may not be more efficient. The only way to really know for sure is to test it. Here are two from the top of my head:

Use the COL_LENGTH T-SQL function to query the database metadata - see if the length of the given column in the given table is NULL or not

IF COL_LENGTH('table_name','column_name') IS NULL BEGIN /Column does not exist or caller does not have permission to view the object/ END

See here for more info: How to check if a column exists in a SQL Server table?
Do a simple "select top 1 * from <tablename>" query, load it into a datatable and enumerate the fields, check if the one you are looking for was returned.

edited Jun 15 '18 at 08:28

answered Jun 15 '18 at 08:22

HaukurHaf

13,522
5
44
59

Thank you for your response. As I said, I would like to use this as a general function to check for many types of things, such as whether a row exists, whether a table exists etc..the comments mentioned in the code were for simplicity, not to be taken literally. – Prasad Kamath Jun 15 '18 at 08:59

Efficient way to check if a row, column etc exists in database?

2 Answers2