如何获取所有数据库中一个公共表中的行数 [英] how to get count of number of rows in one common table in all database..

问题描述

一个表是Ex：tbl_user它存在于大约70个数据库..现在我想要计算所有数据库中的表行..



在类似格式以下..



databasename usercount

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

推荐答案

要获取数据库名称，请使用 DB_NAME（）：

To get the database name use DB_NAME() in your INSERT :

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

Another方式可能就像！

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

试试这个

Try This

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'

EXEC usp_FindTableNameInAllDatabase 'tbl_user'

这篇关于如何获取所有数据库中一个公共表中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！