Variable Database Name

How to use a variable for the database name in T-SQL?

Put the entire script into a template string, with {SERVERNAME} placeholders. Then edit the string using:

SET @SQL_SCRIPT = REPLACE(@TEMPLATE, '{SERVERNAME}', @DBNAME)

and then run it with

EXECUTE (@SQL_SCRIPT)

---

It's hard to believe that, in the course of three years, nobody noticed that my code doesn't work!

You can't EXEC multiple batches. GO is a batch separator, not a T-SQL statement. It's necessary to build three separate strings, and then to EXEC each one after substitution.

I suppose one could do something "clever" by breaking the single template string into multiple rows by splitting on GO; I've done that in ADO.NET code.

And where did I get the word "SERVERNAME" from?

Here's some code that I just tested (and which works):

DECLARE @DBNAME VARCHAR(255)
SET @DBNAME = 'TestDB'

DECLARE @CREATE_TEMPLATE VARCHAR(MAX)
DECLARE @COMPAT_TEMPLATE VARCHAR(MAX)
DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX)

SET @CREATE_TEMPLATE = 'CREATE DATABASE {DBNAME}'
SET @COMPAT_TEMPLATE='ALTER DATABASE {DBNAME} SET COMPATIBILITY_LEVEL = 90'
SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE'

DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@COMPAT_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

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

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)

variable database name

With considerable effort, yes.

SET @db = 'db1';
SET @q = CONCAT('SELECT * FROM ', @db, '.mycol');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Is there a way to use the actual database name as the Database Variable Name in a Referenced Database of a SQL Server Database Project?

The literal database name could be used. The "Database variable" field has to be omitted when Database Reference is created.

There is a bug which causes Database Variable Name to be empty thereafter.

All you need to do is to reload solution.

Now the database can be referenced by it's actual name.

How to parameterize database name in SQL Server

DECLARE @oltp VARCHAR(50)
SET @oltp = 'Employee';

DECLARE @wh VARCHAR(25)
SET @wh =  '_wh';

DECLARE @DatabaseName SYSNAME = @oltp + @wh;
   
IF NOT EXISTS ( SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
    BEGIN
      DECLARE @ErrorMsg VARCHAR(1000);
      SET @ErrorMsg = QUOTENAME(@DatabaseName)  + ' database does not exist';
      RAISERROR(@ErrorMsg , 16 , 2); 
    END
ELSE 
BEGIN
    DECLARE @Sql NVARCHAR(MAX) = N'SELECT * FROM '  
                               + QUOTENAME(@DatabaseName) 
                               + N'..employee_sales_item;';

    EXEC sys.sp_executesql @Sql;
END

How to use a Database Variable Name in dynamic SQL with visual studio for a SQL Server Project?

You can use the project deployment variable directly in the dynamic SQL, just be sure to correctly quote it:

SELECT @sql = N'SELECT QUOTENAME(b.[COLUMN_NAME]) AS [Column] 
                  FROM ' + QUOTENAME('$(DatabaseName)') + N'.[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a 
                  JOIN ' + QUOTENAME('$(DatabaseName)') + N'.[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] b 
                    ON a.[CONSTRAINT_NAME] = b.[CONSTRAINT_NAME] 
                 WHERE a.[CONSTRAINT_SCHEMA] = ' + QUOTENAME(@schema, '''') + N'
                   AND a.[TABLE_NAME] = ' + QUOTENAME(@table, '''') + N'
                   AND a.[CONSTRAINT_TYPE] = ''PRIMARY KEY'' '

Passing database name like variable

Use Dynamic SQL for that .

Declare @SQL Nvarchar(100),@dbname varchar(100) 
set @SQL = 'Select *  from '+ @dbname + '.dbo.table1'
Exec Sp_executeSQL @SQL

---

Related Topics