March 15, 2016 at 9:36 am
I am stuck guys.
Ok I need to create a stored procedure that will get a count of records in each table of a specified DB.
So I need to pass in a DBName and then get a count of records in THAT db.
Here is the code I have. The part that gets a count for whatever db The proc is running in works fine;
ALTER PROCEDURE RecordCountsByTable
(@BatchID int, @dbName varchar(200))
as
EXEC RecordCountsByTable 234, 'AvionteFS_Destination'
DECLARE @sql as varchar(4000)
-- The code below is not working. it also returns an error:
-- Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
--SET @sql = 'sp_MSForEachTable @command1=''''INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
--SET @sql = @dbname + '.dbo.sp_executesql @statement = N''' + @sql + ''''
PRINT '' + cast(@SQL as varchar(max))
--exec(@sql)
-- Code below is working fine. This returns a count for each table for whatever db the proc is running in.
CREATE TABLE #counts
(
table_name varchar(255) Primary Key,
row_count int
)
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count
FROM #counts
WHERE Table_Name NOT LIKE 'zz%'
AND Table_Name NOT LIKE '%tmp'
ORDER BY table_name, row_count DESC
SELECT sum(Row_Count)
FROM #Counts
DROP TABLE #Counts
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 15, 2016 at 9:46 am
Looping through the tables and running a SELECT COUNT(*) against them could be quite resource-intensive if you have large tables (and it's good to avoid using the sp_MSForEach* procedures).
I'd just get the row counts from sys.partitions instead, which is both simpler and less resource-intensive.
You could work with something like this:
SELECT table_name=OBJECT_NAME(o.object_id),
table_rows=SUM(p.rows)
FROM sys.objects o
INNER JOIN
sys.partitions p ON o.object_id=p.object_id
WHERE p.index_id IN (0,1)
GROUP BY o.object_id
ORDER BY table_rows DESC;
Cheers!
March 15, 2016 at 9:50 am
Thanks I had something like this as well but I need to specify the DB to get the count from. It will be a stored procedure and will be getting a count from a database other than where the proc is located. Because it is a stored procedure I can not append the USE command; naturally.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 15, 2016 at 9:55 am
You would just need to use dynamic TSQL, which you're already doing.
Something like this:
CREATE PROCEDURE test_use
@db_name sysname
AS
DECLARE @sql nvarchar(max);
SET @sql='USE '+QUOTENAME(@db_name)+'; ';
SET @sql=@sql+'
SELECT table_name=OBJECT_NAME(o.object_id),
table_rows=SUM(p.rows)
FROM sys.objects o
INNER JOIN
sys.partitions p ON o.object_id=p.object_id
WHERE p.index_id IN (0,1)
GROUP BY o.object_id
ORDER BY table_rows DESC;'
EXEC (@sql);
GO
EXEC test_use 'master';
Cheers!
March 15, 2016 at 10:01 am
Very awesome, thank you so much.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 15, 2016 at 10:21 am
I suggest creating the proc in the master db and marking it as a "system" proc. It will then work within the context of the current db, whatever that is, just like system procs do. But the proc name must start with "sp_".
USE master
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_RecordCountsByTable
AS
--no "SET NOCOUNT ON" because you likely want to see the count in this case.
SELECT table_name=OBJECT_NAME(o.object_id),
table_rows=SUM(p.rows)
FROM sys.objects o
INNER JOIN
sys.partitions p ON o.object_id=p.object_id
WHERE p.index_id IN (0,1)
GROUP BY o.object_id
ORDER BY table_rows DESC, table_name;
GO
EXEC sp_MS_marksystemobject 'dbo.sp_RecordCountsByTable'
GO
--sample executions
USE msdb
EXEC dbo.sp_RecordCountsByTable --lists row counts for msdb
EXEC tempdb.dbo.sp_RecordCountsByTable --lists row counts for tempdb
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 15, 2016 at 10:53 am
That is a great suggestion. In those case it wont work because I am having to link server call everything and their are many databases across multiple servers. In other situations however this is a useful tip and I was not aware that you can do that 🙂 So useful all the same for me; thank you.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 15, 2016 at 11:08 pm
Jeffery Williams (3/15/2016)
That is a great suggestion. In those case it wont work because I am having to link server call everything and their are many databases across multiple servers. In other situations however this is a useful tip and I was not aware that you can do that 🙂 So useful all the same for me; thank you.
You have SQL Server 2012. You might want to just look into what CMS can do for you without having to have the linked servers.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 8:15 am
The environment is mixed. Thank you for the recommendation.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply