9

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

Improve this question
7
  • 4
    You cannot pass schema, database, table or column names as a parameter - if you want to do this, you must use dynamic SQL Commented May 16, 2018 at 6:28
  • how can I use dynamic SQL for this code? Commented May 16, 2018 at 6:32
  • 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. Commented May 16, 2018 at 6:33
  • 1
    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 Commented May 16, 2018 at 6:33
  • I want to create a procedure that receives a database name as a parameter and give me all of that database tables information. Commented May 16, 2018 at 6:40

3 Answers 3

Reset to default
7

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
DECLARE @DBname VARCHAR
DECLARE @QUERY VARCHAR(255)

SET @DBname = 'PatientTurningSystem'

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

EXEC @QUERY
Improve this answer

4 Comments

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.
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
@marc_s since Query should be NVARCHAR, should the Dbname also need to be NVARCHAR as well?
Just EXEC(@QUERY) will work. Also please declare the @DBName as VARCHAR(255)
0

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+']'
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.