sp_MSforeachtable problem with syntax

Question

EXEC sp_MSforeachtable @command1='SELECT ''?'' TableName'

, @whereand='and ''?'' not in (''[dbo].[tblHolidays]'')'

If I run the first line only, I get output including:
TableName

[dbo].[tblHolidays]

If I run both lines, it does not exclude this table. What am I missing?

Answer 1

Hi @Tom van Stiphout

If you just want to find table names that don't include [dbo].[tblHolidays], you don't need to split into two parameters.

EXEC sp_MSforeachtable @command1='SELECT ''?'' TableName where ''?'' not in (''[dbo].[tblHolidays]'')'

I tried to use one parameter to achieve the desired effect and it seemed to be successful.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@whereand='and ''?'' not in (''[dbo].[tblHolidays]'')'

First, sp_MSforeachtable isn't documented and so not supported to use it.

Then "IN (table)" is a wrong syntax, that never works, independend how you execute the query. It should be more

SELECT *
FROM yourTable

See IN (Transact-SQL) => B. Using IN with a subquery