CODEWITHSUNDEEP
sql-server
Margaret
Margaret

Reputation: 5919

Check the existence of a table using SQL when the database name is passed to it

I am writing a stored procedure designed to be run against a number of similar tables. Since we archive our databases each year, it takes parameters of not only table name but also database name.

The problem is, I want to check that the table exists, but I do not know the database name ahead of time. Ordinarily I could do this:

IF  EXISTS (
SELECT * 
FROM [Database].sys.objects 
WHERE object_id = OBJECT_ID(@TableName)
 AND type in (N'U'))

But having the DB name passed to it as a variable means I'm pretty sure sys.objects is going to get kind of ratty with me.

Is this possible?

Upvotes: 0

Views: 903

Answers (1)

codingbiz
codingbiz

Reputation: 26376

You need dynamic sql. Notice the space before each new statement to act like new line

declare @db varchar(50) = 'mydbname'
declare @TableName varchar(50) = 'mytablename'
declare @sql varchar(max)

set @sql = 'use '+@db

set @sql = @sql + ' IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID('''+@TableName+''') AND type in (''U''))  
begin 
     select 1 
end'

exec (@sql)

Upvotes: 1

Related Questions