Select Current Database Name OR Not Current Database Name in SQL Server for a Table in SQL Server-CodePudding

I have two databases in SQL Server. Let's say they are DB1 and DB2. When the system starts, the current database is always DB1. BTW, I have to use DB2 for another table.

For the reason, I want to give a table name as a variable like @tablename and want to select a database name for the @tablename. Would it be possible to pull the database name associated with @tablename?

Also, I want to save the database name to a variable like @databasename to print it out.

When I tried to find a database name from the code below, I could get the database name of the table, ExampleTable, among DB1 and DB2.

EXEC sys.sp_msforeachdb
'SELECT ''?'' DatabaseName, name FROM [?].sys.Tables WHERE Name = ''ExampleTable'''

However, I can't go forward to process how to make a code using a variable @table instead of a fixed table name, ExampleTable.

I will use list of tables to input @tablename into the query one by one from the list.

DECLARE @table sysname = 'TableNames';
DECLARE @database_name sysname = 'dbo';
DECLARE @DatabaseName VARCHAR(50)

-- tbl_01 in dbo.DB1
-- tbl_02 in dbo.DB2
-- tbl_03 in dbo.DB1
-- tbl_04 in dbo.DB2


/*
   I need the code block
   (1) To input a table using @table
   (2) To save the database name to a variable like @database_name 

EXEC sys.sp_msforeachdb
'SELECT ''?'' DatabaseName, name FROM [?].sys.Tables WHERE Name =' @table

*/

Please help me to create a script for my work.

CodePudding user response：

The below code contains a set of tools you can use to do what you want to do. I expect your requirements may change a bit as you do this, so that's why I'm giving you the tools first.

These tools are written as simple checks - easy to understand and relatively quick. They currently just do a SELECT 'Yes' if the database exists/etc - but of course you can change that as needed.

DECLARE @DBName_1 nvarchar(100) = N'DB1';
DECLARE @DBName_2 nvarchar(100) = N'DB2';
DECLARE @TableName nvarchar(100) = N'MyTable';
DECLARE @TableNameToCheck nvarchar(200);

-- Is the current database 'DB1'?
IF DB_Name() = @DBName_1 SELECT 'Yes' ELSE SELECT 'No';

-- Check if the database 'DB2' exists
IF DB_ID(@DBName_2) IS NOT NULL SELECT 'Yes' ELSE SELECT 'No';

-- Check if table is in first database
SET @TableNameToCheck = QUOTENAME(@DBName_1)   N'.[dbo].'   QUOTENAME(@TableName);
IF OBJECT_ID(@TableNameToCheck, 'U') IS NOT NULL SELECT 'Yes' ELSE SELECT 'No';

-- Check if table is in second database (note it uses @DBName_2)
SET @TableNameToCheck = QUOTENAME(@DBName_2)   N'.[dbo].'   QUOTENAME(@TableName);
IF OBJECT_ID(@TableNameToCheck, 'U') IS NOT NULL SELECT 'Yes' ELSE SELECT 'No';

Note that you do not actually need the variable @TableNameToCheck - you can just construct it within the OBJECT_ID() function e.g., OBJECT_ID(@DBName_2 N'.dbo.' @TableName , 'U'). However, it can be useful to set it first to check/ensure it's correct, and it has infinitesimal impact on performance.

For your actual task (recording whether the table exists in DB1 or DB2) you can do the following

DECLARE @DBName_1 nvarchar(100) = N'DB1';
DECLARE @DBName_2 nvarchar(100) = N'DB2';
DECLARE @TableName nvarchar(100) = N'MyTable';
DECLARE @DatabaseWithTable nvarchar(100);

IF OBJECT_ID(QUOTENAME(@DBName_1)   N'.[dbo].'   QUOTENAME(@TableName), 'U') IS NOT NULL 
    BEGIN
    SET @DatabaseWithTable = @DBName_1;
    END
ELSE IF OBJECT_ID(QUOTENAME(@DBName_2)   N'.[dbo].'   QUOTENAME(@TableName), 'U') IS NOT NULL 
    BEGIN
    SET @DatabaseWithTable = @DBName_2;
    END
ELSE
    BEGIN
    SET @DatabaseWithTable = N'Not Found';
    END;

SELECT @DatabaseWithTable;

Edit: Added QUOTENAME() as per suggestion/comment from @HABO