Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
One table is there Ex:tbl_user it is exist on around 70 database..now i want count of the table rows in all database..

In like below format..

databasename usercount
Posted
Comments
Maddy selva 13-May-15 1:16am    
your question is not clear
Member 11337367 13-May-15 1:20am    
k..this is my qeury
create table #tempcount (tablename nvarchar(128), record_count bigint)
EXEC sp_msforeachdb
'insert #tempcount select ''?'' , count(*) from tbl_user_info'
select * from #tempcount
drop table #tempcount

here iam getting same count for all databases..but it is not coming database wise...i want for each database and that table count
Member 11337367 13-May-15 1:22am    
databasename usercount
SYMPHONY 1055
wingsfmcg 1055
AquaLife 1055
BioVitamin 1055
King Fisher 13-May-15 2:27am    
Really? how r u man.

To get the database name use DB_NAME() in your INSERT :
SQL
create table #tempcount (dbname nvarchar(128), tablename nvarchar(128), record_count bigint)
EXEC sp_msforeachdb
'insert #tempcount select DB_NAME(), ''?'' , count(*) from tbl_user_info'
select * from #tempcount
drop table #tempcount
 
Share this answer
 
Comments
Member 11337367 13-May-15 2:21am    
but count coming wrong..it should be coming according to dbname right..?
Another way could be like!

create table #tablist(db sysname, tab sysname, record_count bigint);

exec sp_msforeachdb '
	use [?];
	insert into #tablist 
	SELECT db_name(), sc.name +''.''+ ta.name TableName, SUM(pa.rows) RowCnt
	FROM sys.tables ta
	INNER JOIN sys.partitions pa
	ON pa.OBJECT_ID = ta.OBJECT_ID
	INNER JOIN sys.schemas sc
	ON ta.schema_id = sc.schema_id
	GROUP BY sc.name,ta.name
	HAVING UPPER(ta.name) LIKE UPPER(''tbl_user_info'')
	ORDER BY SUM(pa.rows) DESC;'

select * from #tablist
drop table #tablist
 
Share this answer
 
Comments
King Fisher 13-May-15 2:32am    
Good one 5+.
Member 11337367 13-May-15 2:44am    
Could you tell me why have been used sys.partitions and sys.schema...?
Try This

SQL
Create PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @varSQL1 VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256),RW_CNT VARCHAR(250))
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
IF EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_name=''' + @TableName + ''' )
 BEGIN
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName,(select count(*) from '+@TableName+')
FROM sys.tables
WHERE name LIKE ''' + @TableName + '''
END'
EXEC (@varSQL)


FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
--print @varsql
DROP TABLE #TmpTable
GO


EXEC usp_FindTableNameInAllDatabase 'tbl_user'
 
Share this answer
 
v3
Comments
Member 11337367 13-May-15 3:11am    
while executing error...
Could not find stored procedure 'usp_FindTableNameInAllDatabase'.
King Fisher 13-May-15 3:21am    
What version of Sql server do u have on you machine?
Maddy selva 13-May-15 3:21am    
sql server 2012
Maddy selva 13-May-15 3:16am    
First run the procedure in master and then exeecute EXEC usp_FindTableNameInAllDatabase 'tbl_user'
Member 11337367 13-May-15 3:21am    
k.i did like that..after executing the sp iam getting error like below
Msg 911, Level 16, State 1, Line 1
Database 'H' does not exist. Make sure that the name is entered correctly.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900