Dynamic SQL with Parameter in FROM clause

Executing a block of dynamic SQL via sp_executesql, where the @Database1 variable is a parameter of the stored procedure.

 SET @SQL = ''
 SET @SQL =
'SELECT @@SERVERNAME, @DB, ''Schema name'', s.name, t.name, stype.Name, scol.length 
 INTO   ##TableList
 FROM   '+@DB+'.sys.tables t

INNER JOIN '+@DB+'.sys.schemas s
  ON t.schema_id = s.schema_id'

EXEC sp_executesql @SQL, N'@DB UNIQUEIDENTIFIER', @DB = @Database1

However I am unsure how to use the parameter from the sp_executesql call, in the from clause of the dynamic SQL. Is it possible to do this or should I consider a different approach?

Answers


Try to execute this procedure: Don't know what ''Schema name'' is, so I leave it. Try to change it.

ALTER PROCEDURE [dbo].[Procedure]

(
  @DB            VARCHAR(50)
)
AS
DECLARE @SQL NVARCHAR(max);

BEGIN

CREATE TABLE #TableList(
                SERVERNAME NVARCHAR(255), 
                DB NVARCHAR(255), 
                Schema_name  NVARCHAR(255), 
                s_name  NVARCHAR(255), 
                t_name  NVARCHAR(255), 
                stype_Name  NVARCHAR(255), 
                scol_length INTEGER);

    SET @SQL ='INSERT INTO #TableList(
                           SERVERNAME,
                           DB,
                           Schema_name,  
                           s_name,  
                           t_name,  
                           stype_Name, 
                           scol_length)
               SELECT      @@SERVERNAME, 
                           @DB, 
                           ''Schema name'', 
                           s.name, 
                           t.name, 
                           stype.Name, 
                           scol.length 
               FROM       [' + @DB + '].[sys].[tables] t
               INNER JOIN [' + @DB + '].[sys].[schemas] s ON (t.schema_id = s.schema_id)'

EXEC sp_executesql @SQL; 

SELECT SERVERNAME,
       DB,
       Schema_name,  
       s_name,  
       t_name,  
       stype_Name, 
       scol_length
FROM  #TableList

END
GO

Need Your Help

How do I correct the range in d3?

javascript d3.js

Here is a jsfiddle of the chart I am trying to implement:

Flex AMFPHP site worked in WAMP 2.2 but fails in WAMP 2.4

php actionscript flex3 wamp amfphp

I previously implemented a website using Flex 3 and AMFPHP 1.9 served by WAMP 2.2. When I upgraded to WAMP 2.4, my website no longer worked, with error "send failed". I had previously defined my