2

I'm currently trying to put together something that will tell me the name of two important tables each of our customers use. Sadly, the names of these tables are not always the same, so I've currently come up with the following T-SQL;

create table #test  (   [Database] sysname,
                                    [TenantId] sysname,
                                    [TasksTable] sysname,
                                    [TimeRecordTable] sysname   )

insert into #test
select      db_name() as [Database], 
                SCHEMA_NAME(tbl.schema_id) as [TenantId], 
                tbl.name AS [TasksTable],
                tbl2.name AS [TimeRecordTable]
from    sys.tables tbl, 
            sys.tables tbl2
where tbl.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') 
and     tbl2.name IN ('TaskTimeRecord','TaskTimeRecord_NEW')
and     SCHEMA_NAME(tbl.schema_id) = '2'

select * from #test
drop table #test

When running this, I'm getting over 200 rows of duplicated data, instead of a single line that includes the results I'm looking for.

Does anyone have any suggestions on how to get this working?

  • It appears that you are doing a cross join between all the tables that match the criteria for tbl1 and all the tables that match the criteria for tbl2. What are you looking for by doing the self join on sys.tables? – Randi Vertongen Mar 21 '19 at 10:00
  • I'm trying to find out what tables our customers are using, without knowing the table names in advance. For example, Schema 2 might have the Tasks and TaskTimeRecord tables, but schema 300 might have the Tasks_NEW and TaskTimeRecord_NEW tables. My goal with this join was to try and return this data, one line per schema, but if this is actually a join, I guess this is the wrong way to achieve this. – Tom_W Mar 21 '19 at 10:14
  • Alright I think im with you,what if there are multiple tables being used for a schema? or is that not possible? Are the table names always going to be the 6 tables defined in the question, or do you need the tables that are heavily used based on the usage statistisc (6 tables defined are Tasks, Task, TaskTimeRecord and their NEW counterparts)? – Randi Vertongen Mar 21 '19 at 10:18
  • Each Schema will have many tables, but the only tables that I'm interested are the 6 tables contained within this T-SQL, nothing else. – Tom_W Mar 21 '19 at 10:28
1

You could use a subquery

SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       (SELECT t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id) as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

However, if there is more than one tablename returned by the subquery, the query will fail with the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If more than one tablename could be returned

If the subquery is an issue due to multiple table names, you could create a comma separated string with FOR XML PATH('').

SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

If both the subquery and the main query could return multiple tablenames

And you still need one row per schema, you could create two comma separated strings

SELECT DISTINCT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TaskTable,
       stuff(((SELECT ', '+ t3.Name from sys.tables t3 where t3.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t3.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW','TaskTimeRecord_NEW','TaskTimeRecord');
  • Thank you for this Randi, this works but ideally I would like to get this information on a single line, for each Schema if that's possible. So, each line will contain DBName, SchemaName, TaskTable, TimeRecordTable. Hope that makes sense.. – Tom_W Mar 21 '19 at 10:41
  • @Tom_W aha got you, what if two tables match TaskTable or TimeRecordTable for one schema? – Randi Vertongen Mar 21 '19 at 10:44
  • 1
    fantastic! Turns out I did get that error message you stated but then your follow up commands, using the FOR XML PATH('') method worked a treat. Thanks so much for your help on this. – Tom_W Mar 21 '19 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.