SELECT Field if it exists on all DB tables

Question

I have an SQL Server DB with 100+ tables. Many, but not all of them, have a column called ins_date which is used to keep the date each record was inserted in the DB.

I would like to obtain the last (max) date of insertion for every table in my DB. I have done some tinkering with sp_MSforeachtable which seems good enough to iterate through all the tables.

The problem I'm facing: my queries will return error when they bump into a table that doesn't have the ins_date column. What can I use, together with sp_MSforeachtable, to obtain the info only from the tables that have ins_date, and just ignore or return as NULL the other tables?

Answer 1

U can try this code for check null situation for a table

 string kyt = "SELECT * from sp_MSforeachtable where yourcondition=@yourcondition";
                                SqlCommand comm = new SqlCommand(kyt, con);
                                con.Open();
                                comm.Parameters.AddWithValue(@yourcondition,"valueofyourcondition");
                                SqlDataAdapter da = new SqlDataAdapter(comm);
                                SqlDataReader dr = comm.ExecuteReader();
                                if (dr.Read())
                                {
                                    dr["ins_date"].ToString();


                                    if (dr["ins_date"] != DBNull.Value)
                                    {

Answer 2

You might want to wrap the query in @command1 with an if statement. Something like this:

if exists(select 1 from INFORMATION_SCHEMA.columns where COLUMN_NAME = 'ins_date' and TABLE_NAME='?')