P P P P P P - 1 year ago 74
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

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
only. Like
and many.

What I have tried:

The below query, returns the
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
statement returns
SELECT * FROM RoleMaster

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 Source
  • 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:


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download