P P P P P P - 4 months ago 15
SQL Question

Dynamic SQL query to list all master table's data

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
  • 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.