Help with union

Transact-SQL
1

Hi All,

i have a database that every time we get a new customer a new set of tables get added, I would like to know if it's possible to dynamically create a union so I have all the results are visible in one query currently, the way i achive it at the moment is using a loop and create a query per table but its all sperate queries rather than in one consolidated view

would anyone be able to advise or help me, i don't have access to create tables so I cannot insert into a table :frowning:

DECLARE  @Account As NVARCHAR(100), @TableID As NVARCHAR(100),   @Output varchar(250) , @Mysql As NVARCHAR(Max), @dbname varchar(100)
Set @TableID = '$Item';

DECLARE db_cur cursor for

SELECT   substring(name,1,charindex('$',name)-1)
FROM   SYSOBJECTS
WHERE   xtype = 'U' And name like CONCAT( '%' ,@TableID , '') Order by substring(name,1,charindex('$',name)-1)

open db_cur
fetch next from db_cur into @dbname


while @@FETCH_STATUS = 0
begin

Set @Output = @dbname + @TableID;

--run SQL
Set @MySql = '
                            SELECT 
                            '''+@dbname+''' As AccountName,
                            Count(*) 
                            FROM  ' + CONCAT('[dbo].[' , @Output , ']' ) + ' 
                            
                            '

EXEC sp_executesql @MySql


fetch next from db_cur into @dbname

end

close db_cur
deallocate db_cur
2

I managed to find a solution, posting here incase anyone else ever needs something similar

DECLARE @TableName NVARCHAR(400)
DECLARE @DynSQL NVARCHAR(MAX)
DECLARE @TableID NVARCHAR(100)

Set @TableID = '$Item';

Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR
SELECT substring(name,1,charindex('$',name)-1)
FROM SYSOBJECTS
WHERE xtype = 'U' And name like CONCAT( '%' ,@TableID , '') Order by substring(name,1,charindex('$',name)-1)

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Add the select code.
Set @DynSQL = @DynSQL + ' SELECT
'''+@TableName+''' As AccountName,
Count(*) As NoOfProducts
FROM ' + CONCAT('[dbo].[' , @TableName ,@TableID, ']' )
FETCH NEXT FROM cursor1
INTO @TableName

-- If the loop continues, add the UNION ALL statement.
If @@FETCH_STATUS = 0
BEGIN
    Set @DynSQL = @DynSQL + ' UNION ALL '
END

END

CLOSE cursor1
DEALLOCATE cursor1

--Print @DynSQL
exec sp_executesql @DynSQL

3

something like this will help .. just for idea

use AdventureWorks2019
go 

DECLARE @Name varchar(100) = 'Sales'

DECLARE @SQL varchar(max) = ''

SELECT  @SQL = @SQL + 
  'SELECT '''+name+''''+',count(*) AS COUNT'+' FROM '+'[AdventureWorks2019].[Sales].['+name+']'+' UNION  ALL '
FROM   
   SYSOBJECTS
WHERE   
  xtype = 'U' 
    And 
  name like '%' + @name + '%'

SET  @SQL  = substring(@SQL, 1, len(@SQL)-len(' UNION ALL'))

PRINT @SQL 

EXEC(@SQL)

image