How to let SQL Server not return null query?

Question

I have a sql query like this:

if table1 exist
    if table1 has data
       select * from table1

if table2 exist
    if table2 has data
       select * from table2
...
tableN

then I iterate dataset in c# but even a table does not exist in database, there is an empty result table in dataset, How to write the sql to only return not empty queries? say I have 10 tables, 3 not exist in database, So, only 7 result tables in returned dataset?

Answer 1

You can easily extract details about each table available in your Database:

use MyDBName
go

select * from sys.tables
go

Using a cursor (though I personally avoid them if at all possible), you could loop through and build your queries dynamically to use only the tables available, meaning you won't return any NULLs.

Answer 2

it's pretty hard to guess without knowing what kind of database - especially the involved entities and relations - you are talking about... And as said before, you would'nt normally check if a table exists beforehand - it sounds to me like a question of persistence...

Answer 3

I meant "there is an empty table with no cols and rows in returned dataset"

ahh... so you want the missing table to have a stub in your DataSet? Seems pretty... oddd - but I guess you could do something like:

if object_id('table2') is not null -- yeuch yeuch yeuch (see below)
begin
    select * from table2
end
else
begin
    select 1 where 1 = 0 
end

Again, I stress that I find this desire... unusual; but:

• if we select even if there isn't data, we just get an empty table, which is fine
• if the table doesn't exist, we just get a 1-column, 0-row stub

There are better ways of checking for existance of an object - for example, checking the info-schema tables; but I'm still not convinced that it is (in the general case) sensible to be overly concerned about existence / non-existence of tables, unless you're:

• writing tooling, such as a query analyzer or ORM tool
• writing a data impoty utility that can't trust ad-hoc data