Why does the informationschema bring back malformed resuts with a like operator?

Question

im having trouble with the below script:-

USE master

DECLARE @COMPANY CHAR(3)
declare @createdatabase char(50)
declare @fromdatabase char (50)
declare @sql1 char(100)
declare @logicaldatabasename Char(100)
declare @logicaldatabaselog char (100)

set @fromdatabase = 'a'
set @createdatabase = 'b'

SET @COMPANY = 'PUK'

;with q as (
SELECT [name] [logical_name]
FROM sys.[master_files]
WHERE [database_id] = DB_ID(@fromdatabase))
select @logicaldatabasename = q.[logical_name]
from q
where q.[logical_name] not like '%log%'
;with q as (
SELECT [name] [logical_name]
FROM sys.[master_files]
WHERE [database_id] = DB_ID(@fromdatabase))
select @logicaldatabaselog = q.[logical_name]
from q
where q.[logical_name] like '%log%'

select @logicaldatabasename AS LOGICALDATABASENAME, @logicaldatabaselog AS LOGICALDATABSELOG

--BACKUP DATABASE @fromdatabase
--TO DISK = '\folder\'+@fromdatabase+'.bak'
Set @sql1 = 'CREATE DATABASE '+@createdatabase
Exec (@sql1)
RESTORE DATABASE @createdatabase FROM DISK = '\flder\DANTEST.bak' 
WITH MOVE @logicaldatabasename TO 'folder\b.mdf',
MOVE @logicaldatabaselog TO '\folder\b_log.ldf',
REPLACE 
select * from sys.master_files where database_id = db_id(@createdatabase)
;
go
use b
go
;
DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR


#NAME?
SELECT
  IST.TABLE_NAME AS tblName
FROM
  nhba_a.INFORMATION_SCHEMA.TABLES IST
WHERE RIGHT(IST.TABLE_NAME,3) <> 'PUK' AND IST.TABLE_TYPE = 'BASE TABLE' and ist.TABLE_NAME like '%_%'

ORDER BY IST.TABLE_NAME

--
DECLARE @tblName VARCHAR(255)
--
DECLARE @sql  NVARCHAR(4000)
DECLARE @crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

OPEN cCursor
FETCH cCursor
INTO @tblName

WHILE @@fetch_status = 0
BEGIN
if @tblname like '%_%'
SET @sql = 'DROP TABLE '+QUOTENAME(@tblName);
EXEC sp_executesql @sql;

  FETCH cCursor
  INTO @tblName
END

when using this:-

SELECT
  IST.TABLE_NAME AS tblName
FROM
  nhba_a.INFORMATION_SCHEMA.TABLES IST
WHERE RIGHT(IST.TABLE_NAME,3) <> 'PUK' AND IST.TABLE_TYPE = 'BASE TABLE' and ist.TABLE_NAME like '%_%'

ORDER BY IST.TABLE_NAME

I get tables without a _ but I have searched for tables with only _.

could anyone explain why its not coming back with the correct results and what sql is actually doing in the background?

I have tried varies ways to bring up the correct results but have not able to find out what the problem is because the logic is sound.

Answer 1

When you are using LIKE operator the underscore _ actually referees to any ONE character. It means _ has a special meaning with the LIKE operator.

If you want to return table names where there is an underscore then you need to escape the underscore in your where clause, something like...

WHERE TABLE_NAME like '%\_%' ESCAPE '\'

Or you can also use the square brackets to escape the underscore without using the keyword ESCAPE

WHERE TABLE_NAME like '%[_]%'