May 28, 2009 at 1:07 am
I have a DB which has 2000 rows. Out of which 133 tables start with the table name 'xxx'. Now, i need fetch tables which starts with 'xxx' and which has a row count>0
May 28, 2009 at 1:23 am
Hi,
I believe this is what you are looking for.
USE
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
where a.table_name like 'xxx%' and a.row_count>0
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Regards
Gurpreet Sethi
May 28, 2009 at 3:32 am
This query is a bit simpler:
SELECT OBJECT_NAME(Id) AS [TableName],
rows as [RowCount] FROM sysindexes
WHERE indid 0
ORDER BY [TableName]
[font="Verdana"]Markus Bohse[/font]
May 28, 2009 at 9:22 am
Just an FYI.., both the methods posted above will give you an approximate value but not the exact value. To get the exact value, you have no option but to do a COUNT on the table.
Something like this should do...
IF ( OBJECT_ID( 'tempdb..#test' ) IS NOT NULL )
DROP TABLE #test
CREATE TABLE #test( tname VARCHAR(200) NOT NULL, trows INT NOT NULL )
INSERT #test( tname, trows )
EXECUTE sp_MSforeachtable 'select ''?'' as tname, count(*) as trows from ? where parsename(''?'',1) like ''xxx%'' having count(*) > 0'
SELECT * FROM #test
Note:
sp_MSforeachtable is an undocumented procedure, so I recommend not to use it in any development work.
--Ramesh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply