CODEWITHSUNDEEP
t-sqlsql-server-2012dynamic-sql
Vignesh M
Vignesh M

Reputation: 178

check existence of a table from a another database

I need to check the existence of tables from different database.I know how to do this statically. But I want to pass the table name and database name dynamically.

So here is the expectation , 

Declare @tablename varchar(100) = 'testtable', @dbName Varchar(100) = 'TestDB',@isexist varchar(100)
If exists (select 1 from @dbName..sysobjects where name = @tablename)
Set @isexists = 'Table Exists' 
Else set @isexists = 'Table do not exist'

Upvotes: 2

Views: 3751

Answers (3)

SQL Taylor
SQL Taylor

Reputation: 138

I should have read the answers before typing all of this out... 

DECLARE @TableName VARCHAR(100) , 
        @DBName VARCHAR(100) , 
        @Bit BIT ,
        @Parameter NVARCHAR(MAX) = '@Bit BIT OUTPUT'  

DECLARE @SQL NVARCHAR(MAX) =  'IF  EXISTS (SELECT * 
                                             FROM ' + @DBName + '  ..sys.objects 
                                            WHERE object_id = OBJECT_ID ''' + @TableName + ''')'


EXECUTE sp_executesql @SQL, @Parameter, @bit OUTPUT


 SELECT @Bit

Upvotes: 0

Pred
Pred

Reputation: 9042

You can execute dynamic SQL queries via the sp_executesql stored procedure.

It has two 'main' inputs: @stmt is the query stored in an NVARCHAR string and @params which is an NVARCHAR string and defines the additional variables (like in any SP).

So to check and return the result, you should do something like this:

DECLARE
    @DbName sys.sysname = N'YourDatabaseName';

DECLARE
    @TableIsExist BIT = 0
  , @DynamicQuery = N'USE ' + QUOTENAME(@DbName) + '; SET @IsExist CASE WHEN OBJECT_ID(@TableName) IS NULL THEN 0 ELSE 1 END;'


EXEC sp_executesql
    @stmt = @DynamicQuery
  , @params = N' @TableName sys.sysname, @IsExist BIT OUTPUT'
  , @TableName = N'YourTableName', @IsExists = @TableIsExists OUTPUT

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try dynamic query:

Declare @tablename varchar(100) = 'testtable', @dbName Varchar(100) = 'TestDB'

DECLARE @statement NVARCHAR(MAX) = 'If exists (select * from ' + @dbName + '..sysobjects where name = ''' + @tablename + ''')'
+ 'Print ''Table Exists''' +
'Else Print ''Table do not exist'''

EXEC(@statement)

If you need to return some value from dynamic query then you can use sp_executesql with some output parameters like:

Declare @tablename varchar(100) = 'testtable', @dbName Varchar(100) = 'TestDB', @b bit
DECLARE @ParamDefs NVARCHAR(MAX) = '@b BIT OUTPUT'

DECLARE @statement NVARCHAR(MAX) = 'If exists (select * from ' + @dbName + '..sysobjects where name = ''' + @tablename + ''')'
+ 'Set @b = 1' +
' Else Set @b = 0'

EXEC sp_executesql @statement, @ParamDefs, @b OUTPUT

SELECT @b

Upvotes: 7

Related Questions