Table doesn't exists by table name

Question

Is it possible in SQL Server to check whether table exists in this way? If it doesn't exists, it will run catch

             declare @SQL  varchar(4444)

             select @SQL = '
             begin try
                select * from ServerName.DBName.dbo.TableNAme
             end try
             begin catch
                select 1
             end catch'

             exec (@SQL)

I don't want to use solution described here , because I want to use exact same structure of table as above.

Reason: I will run several dynamic queries in loop, and above ServerName, DbName ,TableName gonna be passed as a parameter.

Of course I did. Above script doesn't run catch, it throws an error if that object doesn't exist — rakamakafo, Sep 26 '18 at 14:38
you do now that `try ... catch` is a costly operation ? It will slow your loop down — GuidoG, Sep 26 '18 at 14:38
How exactly do you propose returning a dummy row with the exact same structure as a table if the table no longer exists? Where do you get the structure? — Sean Lange, Sep 26 '18 at 14:41
@SeanLange , see update, i want that dynamic query to run successfully, why catch doesn't run? — rakamakafo, Sep 26 '18 at 14:43
The catch doesn't happen because even in dynamic sql it won't parse. That is not a run time error which is what try/catch handles. — Sean Lange, Sep 26 '18 at 14:45
Why not use if exists and look at sys.tables? But still how would you know what the structure would be when the table doesn't exist? — Sean Lange, Sep 26 '18 at 14:46
@SeanLange, sys tables you can't look for db name and servername — rakamakafo, Sep 26 '18 at 14:49
Didn't realize it was a remote server. This sounds a LOT like an [xy problem](http://xyproblem.info/) to me. — Sean Lange, Sep 26 '18 at 14:50
@SeanLange it actually isn't, and pretty straightforward. I for some reason forgot to get out of try catch query itself — rakamakafo, Sep 26 '18 at 14:54
Well if it is an xy problem you wouldn't think it is....that is kind of the definition. But whatever, seems you found a solution. — Sean Lange, Sep 26 '18 at 14:55

Tab Alleman · Accepted Answer · 2018-09-26T14:44:59.093

5

It should work if you do it dynamically. When you don't do it dynamically, the missing table will be caught at parse time, and the CATCH will not be triggered.

EDIT: I mean like this:

         declare @SQL  varchar(4444)

         select @SQL = 'select * from ServerName.DBName.dbo.TableNAme'
         begin try
            exec (@SQL)
         end try
         begin catch
            select 1
         end catch

edited Sep 26 '18 at 14:44

answered Sep 26 '18 at 14:39

Tab Alleman

31,483
7
36
52

I updated script. If table doesn't exist, catch doesn't run, it throws an error – rakamakafo Sep 26 '18 at 14:41
Of course not, you put the CATCH in the dynamic context. – Tab Alleman Sep 26 '18 at 14:43
Thanks. Sorry. I don't know why I missed that – rakamakafo Sep 26 '18 at 14:48

Table doesn't exists by table name

1 Answers1