April 21, 2014 at 2:38 pm
whats the syntax to run sp_MSforeachdb against all databases that will inlude
database name In the output for:
SELECT Count(*) Count_in_TableName FROM dbo.TableName
or
select Count_in_TableName=(SELECT Count(*) FROM dbo.TableName)
so it looks like this...
Server NameDatabase NameCount_in_TableName
server1 db1 6
server2 db2 3
server3 db3 9
April 21, 2014 at 2:44 pm
Do a loop or a cursor, construct a SQL statement for each database, and execute separately.:cool:
April 21, 2014 at 2:47 pm
thank you
i know about this one...however
do you have an idea for specifically sp_foreachdb?
April 21, 2014 at 2:49 pm
IF OBJECT_ID('tempdb.dbo.#row_counts') IS NOT NULL
DROP TABLE #row_counts
CREATE TABLE #row_counts (
db_name nvarchar(128) NOT NULL,
table_name nvarchar(128) NOT NULL,
row_count int NULL
)
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
RETURN
USE [?]
EXEC sp_MSforeachtable @replacechar = ''!'', @command1 = ''
INSERT INTO #row_counts ( db_name, table_name, row_count )
SELECT ''''?'''' AS db_name, ''''!'''' AS table_name, COUNT(*) FROM !''
'
SELECT *
FROM #row_counts
ORDER BY
db_name, table_name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 21, 2014 at 2:57 pm
looks good, thank you!!!
so there is no way to use just something like
sp_foreachdb ' db_name() + select count(*) fro table' ??
i know it's the wrong syntax....just for you to have an idea what im looking
🙂 🙂
April 21, 2014 at 3:02 pm
this query would be an order of magnitude faster (at least!) for getting row counts per table.
something like this is what you'd wnat to wrap with sp_msForEachdb:
SELECT o.name AS ObjectName,
ps.row_count AS TheCount
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY o.name
Lowell
April 21, 2014 at 3:06 pm
Lowell (4/21/2014)
this query would be an order of magnitude faster (at least!) for getting row counts per table.something like this is what you'd wnat to wrap with sp_msForEachdb:
SELECT o.name AS ObjectName,
ps.row_count AS TheCount
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY o.name
True, for getting approximate row counts. User has to decide which he/she prefers.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 21, 2014 at 3:33 pm
The row counts in sys.dm_db_partition_stats and sys.partitions are accurate. The old inaccuracies that sysindexes had don't occur. While the documentation for sys.partitions says 'approximate', the value is in fact accurate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply