I'm trying to get a database name by table name in SQL Server. I found INFORMATION_SCHEMA providing database name if I use INFORMATION_SCHEMA.DOMAINS. But I get errors when I make a code block. My code (I'm using at least two databases):
SELECT @dbname = DB_NAME(db.database_id)
FROM sys.databases db
WHERE @dbname NOT IN ('master') AND @dbname IN ('DB1', 'DB2');
For example, table named STUDENT belongs to DB1 while table SCHOOL belongs to DB2. I want to select database name among DB1 and DB2 matching the table name.
DECLARE @table sysname = 'TableNames';
DECLARE @schema sysname = 'dbo';
DECLARE @dbname sysname = 'Test';
SELECT @dbname = INFORMATION_SCHEMA.DOMAINS.DOMAIN_CATALOG
FROM INFORMATION_SCHEMA
WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME = @table;
MS SQL Server Management Studio shows something wrong in my code:
[![enter image description here][1]][1]
If I can't use INFORMATION_SCHEMA I would like another way like code below. How to include a join condition to consider table name when I use sys.tables?
WHERE sys.tables = @table AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = @table