Dynamic SQL query to list all master table's data

Question

I have N numbers of Master tables in my database. To see all the data in the master tables, currently I'm writing SELECT query for each table. So I like to write a sql query to list all my master table's data in my database.

Since we are using a same standard for all master tables, so all the master tables are ending with the suffix Master only. Like RoleMaster, UserMaster, UserRoleMaster, OfficeMaster, PincodeMaster and many.

What I have tried:

The below query, returns the SELECT query for all master table.

SELECT 'SELECT * FROM ' + name FROM sysobjects WHERE xtype = 'U' AND name LIKE '%Master'

It returns

SELECT * FROM RoleMaster
SELECT * FROM UserMaster
SELECT * FROM UserRoleMaster
SELECT * FROM OfficeMaster
SELECT * FROM PincodeMaster
...
...

Executing the below query always returns one table's data only. The PRINT statement returns SELECT * FROM RoleMaster only.

DECLARE @DynamicSql AS VARCHAR (MAX) = '';
SELECT @DynamicSql = 'SELECT * FROM ' + name FROM sysobjects WHERE xtype = 'U' AND name LIKE '%Master'  
PRINT @DynamicSql
EXEC (@DynamicSql)

What is the issue, what I missed here, why its not list all master table's data.

Answer 1

• You need to add the @DynamicSql variable before start of the dynamic query, like SELECT @DynamicSql = @DynamicSql + '
• After QUOTENAME(T.name) you need to add a delimiter + '; ' to split the each line.

So the below query will works in your case:

DECLARE @DynamicSql AS NVARCHAR (MAX) = '';

SELECT @DynamicSql = @DynamicSql + 
        'SELECT * FROM ' + QUOTENAME(S.name) + '.' + QUOTENAME(T.name) + '; ' 
FROM sys.objects T
JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE T.[type] = 'U' AND T.name LIKE '%Master' 

--PRINT @DynamicSql
EXEC (@DynamicSql)

Edit: Based on this comment, I have updated the answer.