• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

sp_Msforeachtable only for system tables

how can we make the following idea work?

sp_Msforeachtable 'select "?",count(*) from ? where "?" in (select name from sys.tables where type = ''s'''
0
anushahanna
Asked:
anushahanna
  • 2
3 Solutions
 
SharathData EngineerCommented:
This is how you can skip some tables when using this undocumented procedure.
http://www.sqlservercurry.com/2010/06/how-to-skip-table-or-database-while.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+sqlservercurry/blog+(Sql+Server+2005/2008+Blog+With+Tips+Tricks+and+Articles)

But I don't think you can use this proc to get row count for system tables.
0
 
dbaSQLCommented:
Remember, sp_msforeachtable is undocumented, and unsupported.  But try this:


EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results
( name nvarchar(128),
rows char(11)
)',
@command1 = 'SELECT ''?'' as TableName, COUNT(1) as TotalRows FROM ? WITH(NOLOCK)',
@whereand = 'or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1',
@postcommand = 'SELECT * FROM ##Results; DROP TABLE ##Results'
Go
0
 
dbaSQLCommented:
Actually, this is better:

EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results
( name nvarchar(128),
rows char(11)
)',
@command1 = 'INSERT ##Results SELECT CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS ''Table'',CAST(rowcnt as varchar(6)) AS ''#Rows'' FROM sysindexes WHERE indid IN (0,1) AND OBJECTPROPERTY(id, ''IsUserTable'') = 0 GROUP BY id, rowcnt, reserved, dpages ORDER BY ''Table'';',
@postcommand = 'SELECT * FROM ##Results; DROP TABLE ##Results'
Go
0
 
anushahannaAuthor Commented:
very good logic - thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now