Change database using variable @databaseNameVariable

Question

Change database using variable @databaseNameVariable

Navigation

#1 by (1 votes)

2

How to change the database with the name of the base stored in variable ?; Do the following code, however, do not change the database.

DECLARE @inicio VARCHAR (100)
DECLARE @bdname VARCHAR (100)
DECLARE @usedb VARCHAR (100)

    SET @inicio = (
    SELECT 
        MIN(dbid)
        FROM 
        sysdatabases
        WHERE name IS NOT NULL 
        and name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB'))

    SET @bdname = (
    select name from sysdatabases
    where dbid = @inicio )
    print @bdname

SET @usedb = 'USE ' + @bdname
EXEC (@usedb)
GO

sql sql-server tsql

asked by Cristian Zauco 21.08.2017 в 20:18

source

1 answer

access functions of a variable in a constructor js Problem with relations in DATASET obtained from database

score 1 · Accepted Answer

You are using sysdatabases that is currently obsolete, to obtain information (with the necessary permissions) of the databases that are on your server you should use sys.databases .

Now, there is another point that you should consider. Let's review:

USE master;

DECLARE @bdname VARCHAR(100);
SELECT TOP 1
    @bdname = name
FROM sys.databases
WHERE name IS NOT NULL
      AND name NOT IN ( 'master', 'tempdb', 'model', 'msdb', 'ReportServer'
                         ,'ReportServerTempDB'
                      )
ORDER BY database_id;

SELECT DB_NAME() [BD FUERA DE EXEC];

EXEC (N'
SELECT DB_NAME() [BD EN EXEC];

USE [' + @bdname + '];

SELECT DB_NAME() [BD EN EXEC];
'    );
GO

SELECT DB_NAME() [BD FUERA DE EXEC];

You'll get:

+------------------+
| BD FUERA DE EXEC |
+------------------+
| master           |
+------------------+

+------------+
| BD EN EXEC |
+------------+
| master     |
+------------+

+------------+
| BD EN EXEC |
+------------+
| BD_INICIAL |
+------------+

+------------------+
| BD FUERA DE EXEC |
+------------------+
| master           |
+------------------+

You can see that the change you make dynamically will only happen in the context that is within the EXECUTE .

Reference: