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 '.'

Comras

You cannot pass schema, database, table or column names as a parameter - if you want to do this, you must use dynamic SQL – Postpaid

how can I use dynamic SQL for this code? – Comras

If you need to use dynamic SQL then you are doing something wrong. Why would you need it to be dynamic anyway? That is unusual. – Tripartition

Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use. Your @DBName variable is right now exactly ONE character long - so that won't work anyway.... use DECLARE @DBName VARCHAR(100) or whatever makes sense in your case – Postpaid

I want to create a procedure that receives a database name as a parameter and give me all of that database tables information. – Comras

i've edited it. DECLARE @DBname VARCHAR(100) but it has also error. – Comras

Does this answer your question? How can I do something like: USE @databaseName – Paramagnetism

Answer 1

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)

Evocator

Answer 2

DECLARE @DBname VARCHAR
DECLARE @QUERY VARCHAR(255)

SET @DBname = 'PatientTurningSystem'

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

EXEC @QUERY

Serg

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. – Comras

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 – Postpaid

@Postpaid since Query should be NVARCHAR, should the Dbname also need to be NVARCHAR as well? – Serg

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

Answer 3

I might be misunderstanding your question, but I use the following script to lookup a database_name and set it as a variable value for use in a downstream activity. In this case, to avoid any errors associated with syntax later on (using AWS RDS db names are bracketed) I added '[]' around the name.

In this case you'll only need to change what the script is looking for Like '%BCI%' to your desired value.

Declare @DBnameis VARCHAR(30)
Declare @DBname VARCHAR(30)
    
Set @DBnameis = (select [name] as database_name
     from sys.databases
     Where [name] Like '%BCI%')
    
Select @DBnameis
    
Set @DBname = '['+@DBnameis+']'

Rockafellow