CODEWITHSUNDEEP
sqlsql-server
Kaliyug Antagonist
Kaliyug Antagonist

Reputation: 3612

Regex to select table names

SQL Server 2012.

The below query fetches all the schema and the corresponding tables that exist in a container DB :

select  table_schema,TABLE_NAME from information_schema.tables where        
TABLE_SCHEMA not in ('cdc') and table_type in ('base table','view')

enter image description here

The cdc schema is as follows :

enter image description here

The facts :

  1. The cdc schema has two Change Tables (CT)(data_personnel_ct, datalake_personnel_new_ct) for ONE table(dbo.datalake_personnel) in the dbo schema
  2. There can be two(or more, in future) CT for a table in any of the schemas

I am trying to write a query which provides the list of all the CT tables under the cdc schema based on ALL the tables found in the other schema but I dunno how to include a regex(if possible, some thing like SchemaName_TableName_NEW_CT) in the select clause(the below query selects only one CT table) :

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,TABLE_TYPE from information_schema.tables where
TABLE_SCHEMA in ('cdc') 
and table_name in (select  table_schema+'_'+TABLE_NAME+'_'+'CT' from information_schema.tables where        
TABLE_SCHEMA not in ('cdc') and table_type in ('base table','view'))
order by TABLE_SCHEMA, TABLE_NAME;

Upvotes: 0

Views: 1257

Answers (4)

Binyamin Regev
Binyamin Regev

Reputation: 1030

Take Zohar Peled code and make it work faster using reverse string function:

on REVERSE (t1.TableName) LIKE 'TC%'

You save the strings concatinating and LIKE 'TC%' works faster than LIKE '%CT'

Upvotes: 1

Liu
Liu

Reputation: 982

Try this, first it create a temp table contains all Change Table (CT) names and then try to check if there are matchings in cdc schema 

create table #temp(tempName varchar(1000))
GO
Insert into #temp
select a.name + '_' + b.name + '_CT' from 
sys.schemas a
left join
sys.tables b on a.schema_id = b.schema_id
where a.name != 'cdc'

select * from #temp
GO


select * from
sys.schemas a
left join
sys.tables b on a.schema_id = b.schema_id
left join 
#temp c on c.tempName = b.name
where c.tempName != null and a.name = 'cdc'

Drop table #temp

Upvotes: 0

Luke Franklin
Luke Franklin

Reputation: 355

the below will only work providing:

  • i have understood the question correctly!
  • all your tables end with ct

you can use LIKE and NOT LIKE and add in a % sign to say I am searching for everything ending with ct.

table_name NOT LIKE '%ct' would return all tables that don't have ct at the end of their name

table_name LIKE '%ct' would return all tables that have ct at the end of their name

you can also do table_name LIKE '%ct%' which would return all tables that contain the letter ct (this is of course least optimal)

    select 
    TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,TABLE_TYPE 
from information_schema.tables 
where
    TABLE_SCHEMA in ('cdc')  and
        (table_name not like '%ct' and TABLE_SCHEMA not in ('cdc') and table_type in ('base table','view'))

order by 
    TABLE_SCHEMA, TABLE_NAME;

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Try this:

select t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME, t1.TABLE_TYPE 
from information_schema.tables as t1
inner join
(
    select table_schema+'_'+TABLE_NAME as TableName 
    from information_schema.tables 
    where TABLE_SCHEMA  <> 'cdc' 
    and table_type in ('base table','view')
) as t2
on t1.TableName LIKE t2.TableName +'%CT'
where t1.TABLE_SCHEMA = 'cdc'
order by TABLE_SCHEMA, TABLE_NAME;

Upvotes: 1

Related Questions