USE DB;
GO
--Create The Table. We'll pull info from here for our dynamic SQL
CREATE TABLE DynamicSQLExample (
TableID int IDENTITY NOT NULL CONSTRAINT PKDynamicSQLExample PRIMARY KEY,
SchemaName varchar(128) NOT NULL,
TableName varchar(128) NOT NULL
);
GO
/* Populate the table. In this case, We're grabbing every user ** table object in this database */
INSERT INTO DynamicSQLExample
SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.schemas s JOIN sys.tables t
ON s.schema_id = t.schema_id;
GO
DECLARE @SchemaName varchar(128)
DECLARE @TableName varchar(128)
-- Now, grab the table name that goes with our ID
SELECT @SchemaName = SchemaName, @TableName = TableName
FROM DynamicSQLExample
WHERE TableID = 3
-- Finally, pass that value into the EXEC statement
EXEC ('SELECT * FROM ' + @SchemaName + '.' + @TableName)
