Sree,
I altered the proc to remove the size column and use the COALESCE
function to transpose the data for you. You will have to figure out how
to get the output to a file...use either BCP or DTS package.
Good luck,
Mike
http://www.SQLScriptSafe.com
Tool that Integrates VSS and SQL Server
-----Original Message-----
From: [Email Address Removed]
Sent: Tuesday, October 21, 2003 9:42 PM
To: LazyDBA.com Discussion
Subject: RE: Record Counts for all Tables
Hi Micael,
I need to automate the process to Excel sheet.. is there any way to do
that. and more over i only need to get row counts for specific tables
only
not all.. please help.
Regards,
Sree.
"Michael Finley"
<[Email Address Removed] To: "LazyDBA.com
Discussion"
tions.com> <[Email Address Removed] cc:
10/22/2003 10:29 AM Subject: RE: Record
Counts for all Tables
Sree,
Use the attached stored proc to generate the number of recs and size and
paste into Excel. Once in excel, you can easily transform from the
columns to rows by copying the cells then using the "Paste Special" -
Transpose option.
Good luck,
Mike
http://www.SQLScriptSafe.com
Tool that Integrates VSS and SQL Server
-----Original Message-----
From: [Email Address Removed] Tuesday, October 21, 2003 8:56 PM
To: LazyDBA.com Discussion
Subject: Record Counts for all Tables
Friends,
need help again
I've some tables where i need to get all the Record counts of Specified
tables (or Sql Queries) and transfer them to an excel sheets like this..
tables
====================================================
table1 table2 table3 table4
====================================================
40 Recors 20 Records 38 Records 23 Records,
====================================================
Regards,
Sree.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] attached file: spGetTableRowCountAndSize.txt)
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] master
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_table_rowcounts_size' AND type = 'P')
DROP PROC sp_show_table_rowcounts_size
GO
CREATE PROC sp_show_table_rowcounts_size
(
@top int = NULL,
@include_system_tables bit = 0
)
AS
/*************************************************************************************************
Purpose: To list the size of all tables in the database in descending order (that is bigger tables first).
Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to
see the top 3 biggest tables in your database. Optionally, you can use @include_system_tables
parameter, to include systemt tables in the output.
NOTE: Always create this procedure in the master database and call it from the required databases,
as if the stored procedure is created in that database (That is, don't prefix the stored procedure
with the database name).
Tested on: SQL Server 7.0
Date created: January-22-2002 21:37 GMT
Date modified: February-18-2002 11:31 GMT
Examples:
To list all the user tables in the database along with their sizes:
EXEC sp_show_table_rowcounts_size
To see the top three biggest tables in your database:
EXEC sp_show_table_rowcounts_size 3
To list all the user AND system tables in the database along with their sizes:
EXEC sp_show_table_rowcounts_size @include_system_tables = 1
To see the top three biggest user or system tables in your database:
EXEC sp_show_table_rowcounts_size 3, 1
*************************************************************************************************/
BEGIN
IF @top > 0
SET ROWCOUNT @top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count]
into #tmpTableRowCount
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Row count] DESC
SET ROWCOUNT 0
Create table #FlattenedTable(CSV_Rows varchar(5000))
DECLARE @TableList varchar(5000)
SELECT @TableList = COALESCE(@TableList + ', ', '') +
CAST([Table Name] AS varchar(50))
FROM #tmpTableRowCount
DECLARE @RowCountList varchar(5000)
SELECT @RowCountList = COALESCE(@RowCountList + ', ', '') +
CAST([Row count] AS varchar(50))
FROM #tmpTableRowCount
Insert INTO #FlattenedTable
Select Rtrim(@TableList)
Insert INTO #FlattenedTable
Select RTRIM(@RowCountList)
SELECT * from #FlattenedTable
Drop table #tmpTableRowCount
drop table #FlattenedTable
END
GO
GRANT EXEC ON sp_show_table_rowcounts_size TO Public
MS Sql Server LazyDBA home page