use northwind
set nocount on

---------------------------------------------------
--DDL/DML
create table myTable (myTableColumn varchar(15))
create table table1 (name varchar(15))
create table table2 (name varchar(15))

go
insert myTable 
select 'Table1' union 
select 'table2'

insert table1 (name)
select 'tg' union all
select null

insert table1 (name)
select 'HalaszJ' union all
select null
go
---------------------------------------------------
--Get all names from all tables
declare @sql varchar(8000)
select @sql = coalesce (@sql + ' Union all ' + 
	'select name from ' + myTableColumn + ' where name is not null',
	'select name from ' + myTableColumn + ' where name is not null')
from	myTable
where	myTablecolumn is NOT NULL

--print @sql
exec(@sql)

go

drop table myTable
drop table table1
drop table table2

----------------------------------------------------------------------------
--create a table to hold combined data from the 1500 tables
create table MemberClassName
	(collectionid int not null
	,[name] varchar(50) not null
	,Primary Key clustered (collectionid, [name]))
go

----------------------------------------------------------------------------
--paste the ouput from this statement into a new QA window  
--run the pasted output it to populate new table
select distinct 'insert MemberClassName (collectionid, [name]) select distinct ' 
+ convert(varchar, collectionid) + ', [name] from [' 
+ memberClassName + ']'  from v_collection

----------------------------------------------------------------------------
--create a function to build a CSV string of names by collectionid
if object_id('dbo.fn_namesByCollectionid') > 0
	drop function dbo.fn_namesByCollectionid
go
create function dbo.fn_namesByCollectionid(@cid int)
returns varchar(8000)
as
begin
	declare @csv varchar(8000)
	select	@csv = coalesce(@csv + ', ' + [name], [name])
	from	MemberClassName
	where	collectionid = @cid

	return @csv
end

----------------------------------------------------------------------------
--statement to return your data using the new combined table and function
select	p.manufacturer,
	p.name,
	p.version,
	c.memberclassname,
	jerryRun = dbo.fn_namesByCollectionid(c.collectionid)
from	v_package p
join v_advertisement a on p.packageid = a.packageid
join v_collection c on a.collectionid = c.collectionid
where	a.advertisementname like 'web%'
----------------------------------------------------------------------------