CODEWITHSUNDEEP
sqlsql-server-2012
developer9969
developer9969

Reputation: 5246

sp_MSforeachtable skipping certain tablenames using like

I am trying to skip some internal tables that have a prefix I have written as below but it's not working. Below I would like to skip all tables beginning with xxx_.

Any ideas? Many thanks for your time!

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ? WHERE ''?'' NOT  LIKE ''xxx_%''' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]

Upvotes: 1

Views: 2360

Answers (1)

artm
artm

Reputation: 8584

From raresql

create table #counts 
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
, @whereand = ' And Object_id In (Select Object_id From sys.objects
Where name not like ''xxx%'')'
SELECT table_name, row_count FROM #counts

Upvotes: 2

Related Questions