CODEWITHSUNDEEP
sqlsql-server
Psar Tak
Psar Tak

Reputation: 702

How to use database name dynamically in SQL Server

I need to get all table names in SQL Server. To do this I use this code:

select TABLE_NAME  
from INFORMATION_SCHEMA.TABLES

I need use dynamic database name. To do this I tried to use this code:

Declare @dbName varchar(50)='Learn'

use @dbname
go

select TABLE_NAME  
from INFORMATION_SCHEMA.TABLES

But it does not work. How can I do this ?

Upvotes: 1

Views: 5062

Answers (3)

Yasir Majeed
Yasir Majeed

Reputation: 741

Create this stored procedure in master db and call it

CREATE PROCEDURE custom_query_executor
    @dbName     VARCHAR(50)
AS
BEGIN
    DECLARE @query_string nvarchar(4000);

    SET @query_string = 'select TABLE_NAME from ' + CAST(@dbName AS NVARCHAR) +'.INFORMATION_SCHEMA.TABLES';
    EXEC sys.sp_executesql @query_string;
END

OR you can try this

DECLARE @dbName VARCHAR(50);
SET @dbName = 'Learn';

SET @query_string = 'select TABLE_NAME from ' + CAST(@dbName AS NVARCHAR) +'.INFORMATION_SCHEMA.TABLES';
EXEC sys.sp_executesql @query_string;

Upvotes: 2

Dudi Konfino
Dudi Konfino

Reputation: 1136

DECLARE @sql varchar(max)
Declare @dbName varchar(50)='Learn'
SET @sql='
use '+@dbname+'
go

select TABLE_NAME  
from INFORMATION_SCHEMA.TABLES'
exec (@sql)

Upvotes: 1

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

DECLARE @dbName varchar(50)='Learn'
EXEC ('SELECT TABLE_NAME FROM ' +@dbName+'.INFORMATION_SCHEMA.TABLES');

OR SIMPLY:

SELECT TABLE_NAME FROM Learn.INFORMATION_SCHEMA.TABLES

Upvotes: 3

Related Questions