June 30, 2009 at 6:40 am
Hi all,
how can i get report to get row count for all tables in database please!! guide me!!
June 30, 2009 at 8:00 am
IF EXISTS (SELECT [name] From sysobjects WHERE [name] ='spReporttbleInfo' and xType ='P')
BEGIN
DROP PROCEDURE spReporttbleInfo
END
GO
CREATE PROCEDURE spReporttbleInfo
(
@Database Varchar(100)
)
AS
BEGIN
DECLARE @SQLStr NVARCHAR(4000)
DECLARE @TblName VARCHAR(155)
--Create a temp table and Insert all the User tables in the database
IF EXISTS( SELECT [name] from tempdb..sysObjects WHERE [name] ='#Store' and xtype ='U')
BEGIN
DROP TABLE #Store
END
CREATE TABLE #store (tblName VARCHAR(255))
SET @SQLStr = N'INSERT INTO #Store
SELECT obj.[Name]
FROM ' +@Database + '..sysObjects obj
WHERE xType =''U'''
EXEC sp_ExecuteSQL @SQLStr
--Create a temp table to Store all the info returned by sp_spaced used
IF EXISTS( SELECT [name] from tempdb..sysObjects WHERE [name] ='#details' and xtype ='U')
BEGIN
DROP TABLE #details
END
CREATE TABLE #Details ([Name] VARCHAR(255),[Rows] BIGINT, Reserved VARCHAR(20), [data] VARCHAR(10),indexSize VARCHAR(10), unused VARCHAR(10))
--Cursor to execute sp_spaced used for each of the tables in the database
DECLARE tblCSR CURSOR FOR
SELECT * FROM #store
OPEN tblCSR
FETCH NEXT FROM tblCSR INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLstr =N'EXECUTE ' + @Database + '..sp_spaceUsed ' + @TblName
INSERT INTO #Details
EXEC sp_executeSQL @SQLstr
FETCH NEXT FROM tblCSR INTO @TblName
END
CLOSE tblCSR
DEALLOCATE tblCSR
SELECT * FROM #Details
END
June 30, 2009 at 9:26 pm
pat (6/30/2009)
how can i get report to get row count for all tables in database
Hi,
Try Another method
USE DB
GO
SELECT
a.name, object_name (i.id) TableName, rows as RowCnt
FROM sysindexes i INNER JOIN sysobjects o ON (o.id = i.id AND o.xType = 'U')
inner join sysusers a on o.uid = a.uid
WHERE indid < 2
ORDER BY 3 desc, TableName
ARUN SAS
June 30, 2009 at 9:34 pm
arun.sas (6/30/2009)
pat (6/30/2009)
how can i get report to get row count for all tables in databaseHi,
Try Another method
USE DBGO
SELECT
a.name, object_name (i.id) TableName, rows as RowCnt
FROM sysindexes i INNER JOIN sysobjects o ON (o.id = i.id AND o.xType = 'U')
inner join sysusers a on o.uid = a.uid
WHERE indid < 2
ORDER BY 3 desc, TableName
ARUN SAS
Arun,
Thanks for this... it's something I hadn't seen before.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2009 at 9:38 pm
SimonH (6/30/2009)
IF EXISTS( SELECT [name] from tempdb..sysObjects WHERE [name] ='#Store' and xtype ='U')BEGIN
DROP TABLE #Store
END
Simon,
Just as an FYI, this is not the correct way to check for a temp table. As coded above, it won't find the temp table and if it does exist, you'll get an error when trying to create it again.
Instead, you should run:
if object_id('tempdb..#Store') is not null DROP TABLE #Store
Take a look at this article[/url] - it talks about it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2009 at 10:24 pm
This script will give the row counts and space used for each table in a database.
Script to analyze table space usage
June 30, 2009 at 10:32 pm
WayneS (6/30/2009)
SimonH (6/30/2009)
IF EXISTS( SELECT [name] from tempdb..sysObjects WHERE [name] ='#Store' and xtype ='U')BEGIN
DROP TABLE #Store
END
Simon,
Just as an FYI, this is not the correct way to check for a temp table. As coded above, it won't find the temp table and if it does exist, you'll get an error when trying to create it again.
Instead, you should run:
if object_id('tempdb..#Store') is not null DROP TABLE #Store
This is a better way to check for the existence of a temp table:
if object_id('tempdb..#Store','U') is not null DROP TABLE #Store
When you add the second argument, 'U', to the object_id function, it makes sure it is a table and not some other type of temporary object, like a stored procedure. This works with all versions of SQL Server from 7.0 forward.
July 1, 2009 at 1:07 am
Wayne,
Sorry it should have read Like '#store%'
Perhaps still not the best way to to do it.
Simon
July 19, 2009 at 11:47 pm
Ummmmm.... be careful folks. If you don't run DBCC UPDATEUSAGE(0), you can get some seriously incorrect information. I'll try not to bad mouth the cursor/loop solutions on this too badly... 😉 And, we don't need any temp tables for this either. The simple solutions using SysIndexes are good but you must use the DBCC command I mentioned above first.
As Micheal pointed out in code, rowcounts are the tip of the iceberg. So, try this... details are where they usually are... in the comments in the code...
--_______________________________________________________________________________________________________________________
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up to snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
Basically, I did what MS should have done with sp_SpaceUsed. In fact, I stole most of the code from that computational slice of heaven. 😉 Expect it to take a minute or two the first time you run it because people don't normally include the DBCC command in their normal maintanence. I use the "Rows" and "RowModCtr" columns to decide when I want to update statistics. I also schedule a proc with this in it to take occasional "snapshots" of the databaseto see where growth is occurring and what the growth is being caused by. Comes in handy when it's time to justify more disk space.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply