Question :
I need to know the row count of a certain table that exists (or don’t) in multiple databases.
Here’s what I came with:
DECLARE @NomeBase as char(60)
declare @base as char(100)
declare @select as char(1000)
DECLARE CBases CURSOR FOR select databasename from sig_orgao_web
--gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO @NomeBase
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @base =rtrim(@NomeBase) + '.dbo.sia_pais' -- database.dbo.table format
select @base
set @select = 'SELECT cast( count(*) as char(70)) from ' + @base
exec (@select)
FETCH NEXT FROM CBases INTO @NomeBase
END
CLOSE CBases
DEALLOCATE CBases
I know that tablenames have to be static, so I’m creating the select statements dynamically. The results are like this:
SIIG_DAT_DESENV.dbo.sia_pais
13
SIIG_DAT_HOMOLOGACAO.dbo.sia_pais
13
And it goes on.
The thing is, if the table doesn’t exist in my database or if the user which is running this script on management studio doesn’t have access to a certain database, an error will be thrown.
Is there a better way to do this?
Answer :
I would use the system metadata to make all of this an easier experience. The query from this article will show you how to use the metadata to determine the rowcount for all your tables in a database.
It’d look like
SELECT
s.[Name] as [Schema]
, t.[name] as [Table]
, SUM(p.rows) as [RowCount]
, db_name() AS dbname
FROM
sys.schemas s
LEFT OUTER JOIN
sys.tables t
ON s.schema_id = t.schema_id
LEFT OUTER JOIN
sys.partitions p
ON t.object_id = p.object_id
LEFT OUTER JOIN
sys.allocation_units a
ON p.partition_id = a.container_id
WHERE
p.index_id in(0,1) -- 0 heap table , 1 table with clustered index
AND p.rows is not null
AND a.type = 1 -- row-data only , not LOB
and t.[name] = 'sia_pais'
GROUP BY
s.[Name]
, t.[name]
ORDER BY
1
, 2;
Great, now you know the row count for a single table. You can then patch that into your existing cursor. The other nice thing about this route is it doesn’t matter whether the table exists or not. It will simply not return a row if the table is gone.
And for the sake of providing a mostly full solution
DECLARE
@NomeBase as char(60)
, @base as nvarchar(4000)
, @select as nvarchar(4000);
-- Create a template query to capture table counts
-- Since it's lightning fast, I delay filtering until the end
SELECT
@base = N'USE <dbname/>;
INSERT INTO
##rowCounts
SELECT
s.[Name] as [Schema]
, t.[name] as [Table]
, SUM(p.rows) as [RowCount]
, db_name() AS dbname
FROM
sys.schemas s
LEFT OUTER JOIN
sys.tables t
ON s.schema_id = t.schema_id
LEFT OUTER JOIN
sys.partitions p
ON t.object_id = p.object_id
LEFT OUTER JOIN
sys.allocation_units a
ON p.partition_id = a.container_id
WHERE
p.index_id in(0,1)
AND p.rows is not null
AND a.type = 1
GROUP BY
s.[Name]
, t.[name]
';
--
if object_id('tempdb..##rowCounts') IS NOT NULL
BEGIN
DROP TABLE ##rowCounts;
END;
-- Create a global temporary table to keep track of table counts
CREATE TABLE
##RowCounts
(
[SchemaName] sysname
, [TableName] sysname
, [RowCounts] bigint
, [DatabaseName] sysname
);
-- Enumerate through all of your databases
DECLARE CBases CURSOR FOR
-- select name from sys.databases
select databasename from sig_orgao_web
--gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO @NomeBase
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Replace our token with the actual database name
SET @select = replace(@base, '<dbname/>', @NomeBase);
EXEC(@select);
FETCH NEXT FROM CBases INTO @NomeBase;
END;
CLOSE CBases;
DEALLOCATE CBases;
-- Retrieve actual counts
SELECT
*
FROM
##RowCounts RC
WHERE
RC.TableName = 'sia_pais'
AND RC.SchemaName = 'dbo';
What remains? Your account might not have access to a given database. The template could be further armored by wrapping a try/catch around the whole thing. But for this case, I assume you have access and if you don’t, it comes roaring back at you instead of silently swallowing errors.
In order to prevent errors in your script you really need to check and verify
- That the user has access to the database
- That the table exists
- That the user has select rights on the table
Once you verified the above you can then execute the script to get the row count.
The script below should do the above for you. Its a bit crude and could be improved.
set nocount on
DECLARE @NomeBase as char(60)
declare @base as char(100)
declare @select as char(1000)
DECLARE CBases CURSOR FOR select name from databasename from sig_orgao_web
--gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO @NomeBase
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(
SELECT
Name
FROM
sys.sysdatabases
WHERE
@NomeBase in ( SELECT NAME from sys.sysdatabases where has_dbaccess(name) = 1)
)
BEGIN
set @base =rtrim(@NomeBase) + '.dbo.sia_pais' -- database.dbo.table format
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@base) AND type in (N'U'))
BEGIN
IF ( Select HAS_PERMS_BY_NAME(@base,'OBJECT', 'INSERT')) = 1
BEGIN
select @base
set @select = 'SELECT cast( count(*) as char(70)) from ' + @base
exec (@select)
END
ELSE
BEGIN
SELECT 'Table ' + @base + 'No Select Access'
END
END
ELSE
BEGIN
SELECT 'Table ' + @base + 'Does Not Exist'
END
END
ELSE
BEGIN
SELECT 'No Permission for Database: ' + @NomeBase
END
FETCH NEXT FROM CBases INTO @NomeBase
END
CLOSE CBases
DEALLOCATE CBase
Use this query and you will get particular table count from all databases:
SP_MSFOREACHDB “SELECT ”’?”’ D,ROWS FROM [?].SYS.PARTITIONS x INNER JOIN [?].SYS.tables y ON x.object_id=y.object_id WHERE NAME=’ItemUnicode'”
I like the responses above but this is a little more streamlined.
DECLARE @tablename SYSNAME = 'sia_pais';
DECLARE @cmd NVARCHAR(2000) = 'USE [?] IF OBJECT_ID('''+@tablename+''') IS NOT NULL
BEGIN
SELECT DB_NAME();
EXEC sp_spaceused '''+@tablename+''';
END';
EXEC sp_msforeachdb @command1 = @cmd;