July 18, 2008 at 12:01 pm
Hello again....
Last year I had a query that I could run that would list all 90 tables in my database and how many records are in each table. I cannot find this query and was wondering if anyone had any suggestions on how to accomplish this again.
Thanks
Garry
July 18, 2008 at 1:50 pm
SELECT
c.name AS [schema_name],
b.name AS table_name,
a.row_count
FROM
(SELECT
ps.[object_id],
SUM (CASE WHEN ps.index_id < 2 THEN row_count ELSE 0 END) AS [row_count]
FROM sys.dm_db_partition_stats ps
GROUP BY ps.[object_id]) AS a
INNER JOIN sys.all_objects b ON ( a.[object_id]= b.[object_id])
INNER JOIN sys.schemas c ON (b.[schema_id] = c.[schema_id])
WHERE b.type <> N'S' and b.type <> N'IT'
ORDER BY c.name, b.name
* Noel
July 20, 2008 at 7:20 am
That worked great, thanks.
July 20, 2008 at 8:00 am
There it is the code for the Tables and records ( without schema information)!
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY i.rowcnt DESC
:w00t:
July 20, 2008 at 11:42 am
This view works, and provides a lot more information as well:
http://www.sqlservercentral.com/scripts/tables/62545/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 20, 2008 at 11:54 am
This will return all tables Rows informantion...
SELECT OBJECT_NAME(object_id), Rows FROM sys.partitions WHERE index_id = 1
July 20, 2008 at 12:21 pm
Abhijit (7/20/2008)
This will return all tables Rows informantion...SELECT OBJECT_NAME(object_id), Rows FROM sys.partitions WHERE index_id = 1
Actually, this only works on tables with a clustered index, and not all tables have a clustered index.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 21, 2008 at 4:54 am
Select Distinct a.rows,b.Object_id,b.name from sys.partitions a inner join sys.objects b on b.Object_id = a.Object_id
where b.type = 'U'
The logic is same as of Abhijit. It contains all the tables, regardless of the clustered index. I looks fine to me. can someone explain what am I missing...?
Atif Sheikh
July 21, 2008 at 6:17 am
Actually Abhijit's can be fixed pretty easily:
SELECT OBJECT_NAME(object_id), Rows FROM sys.partitions WHERE index_id < 2
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 5:36 am
Sorry...
didn't looked at the where clause...
Atif Sheikh
July 22, 2008 at 5:56 am
hey thxs rbarryyoung,
i just missed the <
Abhijit - http://abhijitmore.wordpress.com
July 22, 2008 at 5:57 am
hey thxs rbarryyoung,
i just missed the <
Abhijit - http://abhijitmore.wordpress.com
July 22, 2008 at 7:13 am
Yep, I figured.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply