SQL Server querying system objects

Question

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 could make a view in master that does a union of sys.tables and sys.columns for each database but it wouldn't be dynamic. To be honest that sounds like a maintenance nightmare and something I would run away from immediately if not sooner. I would think that using dynamic sql would be better in this case. — Sean Lange, Jan 26 '16 at 19:28

score 1 · Answer 1 · answered Jan 26 '16 at 19:33

1

Use function OBJECT_ID() as seen in the documentation?

https://msdn.microsoft.com/en-US/library/ms190328.aspx

answered Jan 26 '16 at 19:33

Eduardo

7,631
2
30
31

Rafael · Accepted Answer · 2016-02-20T10:32:00.150

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.

SQL Server querying system objects

2 Answers2