Assign a database name into the variable via SQL in SQL server

Question

I want to assign database name into the declared variable and this is how I tried already:

DECLARE @DBname VARCHAR(100)

SET @DBname = 'PatientTurningSystem'

SELECT TABLE_NAME, TABLE_TYPE
FROM @DBname.INFORMATION_SCHEMA.TABLES

But I get the following error:

Msg 102, Level 15, State 1, Procedure SP_TableDeatails, Line 8
Incorrect syntax near '.'

Solution

You can try the following query

DECLARE @DBname VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)

SET @DBname = 'PatientTurningSystem'

SET @SQL = 'SELECT TABLE_NAME, TABLE_TYPE
FROM ' +@DBname+'.INFORMATION_SCHEMA.TABLES'

EXEC (@SQL)

Comments

[Admin]

it doesn't work for me. this error is: Msg 911, Level 16, State 4, Line 14 Database 'SELECT TABLE_NAME, TABLE_TYPE FROM PatientTurningSystem' does not exist. Make sure that the name is entered correctly.

[marc_s]

First off, the @DBName really must have a length in its declaration - otherwise, this is never going to work. And secondly, the EXEC function expects an NVARCHAR - so please define your @Query to be NVARCHAR, and use the N'....' prefix when settings its value

[Alfin E. R.]

@marc_s since Query should be NVARCHAR, should the Dbname also need to be NVARCHAR as well?

[Gopakumar N.Kurup]

Just EXEC(@QUERY) will work. Also please declare the @DBName as VARCHAR(255)