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 ?
DECLARE @dbName varchar(50)='Learn'
EXEC ('SELECT TABLE_NAME FROM ' +@dbName+'.INFORMATION_SCHEMA.TABLES');
OR SIMPLY:
SELECT TABLE_NAME FROM Learn.INFORMATION_SCHEMA.TABLES
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;
DECLARE @sql varchar(max)
Declare @dbName varchar(50)='Learn'
SET @sql='
use '+@dbname+'
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES'
exec (@sql)
