How to select all the tables from the database using like operator or any other way

Transact-SQL
1

Sample code is below:

DECLARE @reportExecutionId1 AS INT;
SET @reportExecutionId1 = 1;

SELECT 'DROP TABLE ' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE '[ReptoolResult]%' AND TABLE_NAME LIKE 'ER' + CAST(@reportExecutionId1 AS VARCHAR) + '_%'
ORDER BY TABLE_SCHEMA, TABLE_NAME

Expected result set is below:

DROP TABLE [ReptoolResult].[ER1_1]
DROP TABLE [ReptoolResult].[ER1_2]
DROP TABLE [ReptoolResult].[ER1_3]

but the above sql should return below and don't want like below

DROP TABLE [ReptoolResult].[ER1_1]
DROP TABLE [ReptoolResult].[ER103_1]
DROP TABLE [ReptoolResult].[ER115_1]
DROP TABLE [ReptoolResult].[ER116_1]
DROP TABLE [ReptoolResult].[ER126_1]
DROP TABLE [ReptoolResult].[ER127_1]
DROP TABLE [ReptoolResult].[ER154_1]

Could you please suggest me any other way/solution.

2

Hi.
Try removing the "[" and "]" signs in the WHERE clause
because I believe those brackets are not inside the TABLE_SCHEMA field.

If instead there were, you should write the first

[[]

because otherwise it would be mistaken for the wildcard character.

3

Thanks for your reply.

Not working.

image
image1920×1080 369 KB

4

In the WHERE you have an underscore _.
You have to write it enclosed in square brackets, like this:

[_]
5

Working good.

Thanks gdl.

6

I'm glad it works.
For more information, In this documentation:

read the paragraph "Use wildcard characters as literals".