Solved

sp_Msforeachtable only for system tables

Posted on 2011-02-25
4
665 Views
Last Modified: 2012-05-11
how can we make the following idea work?

sp_Msforeachtable 'select "?",count(*) from ? where "?" in (select name from sys.tables where type = ''s'''
0
Comment
Question by:anushahanna
  • 2
4 Comments
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 150 total points
ID: 34981445
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
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 350 total points
ID: 34981570
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
 
LVL 17

Accepted Solution

by:
dbaSQL earned 350 total points
ID: 34981632
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34982644
very good logic - thanks.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now