6

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

  • 3
    You **cannot** pass schema, database, table or column names as a **parameter** - if you want to do this, you must use *dynamic SQL* – marc_s May 16 '18 at 06:28
  • how can I use __dynamic__ SQL for this code? –  May 16 '18 at 06: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. – juergen d May 16 '18 at 06:33
  • 1
    [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/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 – marc_s May 16 '18 at 06:33
  • I want to create a procedure that receives a database name as a parameter and give me all of that database tables information. –  May 16 '18 at 06:40
  • i've edited it. ```DECLARE @DBname VARCHAR(100)``` but it has also error. –  May 16 '18 at 06:42
  • Does this answer your question? [How can I do something like: USE @databaseName](https://stackoverflow.com/questions/3788566/how-can-i-do-something-like-use-databasename) – Wai Ha Lee Jun 24 '21 at 09:54

3 Answers3

6

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)
Emdad
  • 822
  • 7
  • 14
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
Alfin E. R.
  • 741
  • 1
  • 7
  • 24
  • 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. –  May 16 '18 at 06:46
  • 2
    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 May 16 '18 at 06:47
  • @marc_s since Query should be NVARCHAR, should the Dbname also need to be NVARCHAR as well? – Alfin E. R. May 16 '18 at 06:49
  • 1
    Just EXEC(@QUERY) will work. Also please declare the @DBName as VARCHAR(255) – Gopakumar N.Kurup May 16 '18 at 08:19
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+']'
Airwolf1986
  • 1