Create dynamic list of tables based on list with wildcards

Question

So I have a list of tables that I want (with wildcards)

CREATE TABLE [config].[datalist](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [order] [int] NULL,
    [dbname] sysname NULL,
    [schemaname] sysname NULL,
    [tablename] sysname NULL
) 
GO
SET IDENTITY_INSERT [config].[datalist] ON 
GO
INSERT [config].[datalist] ([id], [order], [dbname], [schemaname], [tablename]) VALUES (1, 1, N'TEST', N'audit*', N'*')
GO
INSERT [config].[datalist] ([id], [order], [dbname], [schemaname], [tablename]) VALUES (7, 2, N'TEST', N'conversie', N'BR')
GO
INSERT [config].[datalist] ([id], [order], [dbname], [schemaname], [tablename]) VALUES (8, 3, N'TEST', N'datalibrary', N'T*')
GO
INSERT [config].[datalist] ([id], [order], [dbname], [schemaname], [tablename]) VALUES (9, 3, N'BIML', N'*', N'*')
GO
SET IDENTITY_INSERT [config].[datalist] OFF
GO

SELECT  [id]
      ,[order]
      ,[dbname]
      ,[schemaname]
      ,[tablename]
  FROM [UITWISSEL].[config].[datalist]

That gives me

id  order   dbname  schemaname  tablename
1   1   TEST    audit*  *
7   2   TEST    conversie   BR
8   3   TEST    datalibrary T*
9   3   BIML    *   *

Now what I need is a list of all tables mentioned in the table config.datalist

So I want a list of:

id 1) all tables in database TEST of which the schemaname starts with audit

id 7) all tables in database TEST in the schema conversie with the name BR

id 8) all tables in database TEST in schema 'datalibrary' of which the name starts with a T

id 9) all tables in database BIML in all schema's

I have the following code but I this works only for the database BIML. How to get this code to work for all databases mentioned in config.datalist.dbname?

select distinct  t.name as TableName
, 'SELECT  * FROM ' + quotename(replace(replace(ds.[dbname] ,'[',''),']',''))+'.'+
quotename(s.name)+'.'+
quotename(t.name) AS selectquery 
,s.name as schemaname
,ds.[order]
from BIML.sys.schemas s
left join BIML.sys.tables t on s.schema_id = t.schema_id  
inner join [UITWISSEL].[config].[datalist] ds on s.name like  replace(ds.schemaname,'*','%') COLLATE DATABASE_DEFAULT
inner join [UITWISSEL].[config].[datalist] dt on t.name like  replace(ds.tablename,'*','%') COLLATE DATABASE_DEFAULT
order by ds.[order] , s.name, t.name

That gives me

TableName   selectquery schemaname  order
connections SELECT  * FROM [BIML].[config].[connections]    config  3
FilesToImport   SELECT  * FROM [BIML].[config].[FilesToImport]  config  3

score 1 · Answer 1 · answered Jun 25 '21 at 10:33

Assuming you are parametrising the statement to get the list of tables, then you could do this:

DECLARE @ListID int = 8; --Your parameter

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @DatabaseName sysname,
        @SchemaName sysname,
        @TableName sysname;

SELECT @DatabaseName = REPLACE(dbname,'*','%'),
       @SchemaName = REPLACE(schemaname,'*','%'),
       @TableName = REPLACE(tablename,'*','%')
FROM config.datalist
WHERE id = @ListID;

SET @SQL = N'SELECT s.[name] AS SchemaName,' + @CRLF +
           N'       t.[name] AS TableName' + @CRLF +
           N'FROM ' + QUOTENAME(@DatabaseName) + N'sys.schemas s' + @CRLF + 
           N'     JOIN ' + QUOTENAME(@DatabaseName) + N'sys.tables t ON s.schema_id = t.schema_id' + @CRLF +
           N'WHERE s.[name] LIKE @SchemaName' + @CRLF +
           N'  AND t.[name] LIKE @TableName;';

PRINT @SQL;
--EXEC sys.sp_executesql @SQL, N'@SchemaName sysname, @TableName sysname', @SchemaName, @TableName; --Uncomment to run

score 0 · Accepted Answer · answered Jun 25 '21 at 11:12

declare @i as int = 1
declare @db_list table (id int identity(1,1), dbname sysname, [order] int)
declare @resultlist table ([order] int,dbname sysname, schemaname sysname, tablename sysname, selectquery  nvarchar(4000))
declare @act_dbname sysname
declare @act_order int =1
declare @sql as nvarchar(4000)

insert into @db_list 
select distinct dbname, [order] from UITWISSEL.[config].[datalist] order by [order]

while @i <= (select max(id) from @db_list)
BEGIN

set @act_dbname = (select dbname from @db_list where id = @i)
set @act_order = (select [order] from @db_list where id = @i)
set @sql = '

select distinct  
'+ cast(@act_order as varchar(4)) + ' as [order]
,'''+ @act_dbname +''' as dbname
,s.name as schemaname
,t.name as tablename
, ''SELECT  * FROM '' + quotename('''+ @act_dbname + ''')+''.''+
    quotename(s.name)+''.''+
    quotename(t.name) 
AS selectquery 

from  '+ @act_dbname + '.sys.schemas s  
left join '+ @act_dbname + '.sys.tables t on s.schema_id = t.schema_id  
inner join [UITWISSEL].[config].[datalist] ds on s.name like  replace(ds.schemaname,''*'',''%'') COLLATE DATABASE_DEFAULT
inner join [UITWISSEL].[config].[datalist] dt on t.name like  replace(ds.tablename,''*'',''%'') COLLATE DATABASE_DEFAULT
order by [order] , s.name, t.name
'
print  @sql 
insert into @resultlist exec sp_executesql @sql
set @i = @i+1
END

select * from @resultlist order by [order], schemaname, tablename

Careful, this attempt introduces injection issues. The OP properly used `QUOTENAME` when they injected the values, and so too should you. — Thom A, Jun 25 '21 at 11:14

Create dynamic list of tables based on list with wildcards

2 Answers2