I have a stored procedure in my Master Database. It is used by external programs which provide all of the parameters. One of the parameters provided is a database name. Within that database there ought to be a table named xyz.
I receive an exception when that table does not exist in the requested database. I would like to write something into the stored procedure to check if that table exists and return something more useful if it doesn't exist.
This is trickier than it seems. I can get by variable database names using dynamic SQL. And I can check if those databases exist using IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME = PARAMETER1).
In order to check if the table exists, I would need to USE the database, so I would need to use dynamic SQL. However, that would leave me with an executable variable and I don't know how to check the results of that variable (for instance, I could not do IF EXISTS (EXEC @SQL)). I was hoping there was some way to say SELECT * FROM sys.AllDatabasesAllTables WHERE DatabaseName = Parm1 AND TableName = Parm2.
I realize that sys.AllDatabasesAllTables does not exist, but I was hoping maybe someone knew of another way... Is there a good way to make a view in the master database that stores all databases and their table names?
You may use the OBJECT_ID as like below:
IF OBJECT_ID('DatabaseName.SchemaName.TableName') IS NOT NULL
BEGIN
-- Code Here
END
ELSE
BEGIN
-- Code Here
END
GO
Another possible solution, but not tested though is to use a kind of Try-Catch statement?
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
So in the catch block, you could return the message you were looking for.