top of page

Automating Row Count Retrieval for User Databases

Write a script ensures you get row counts for all tables in all user databases, excluding the system databases.


We can use the undocumented stored procedure sp_msforeachdb, which loops the databases and all calls to every object in the database using sp_msforechtable.





Solution:


-- Create a temporary table to store the database name, table name, and row count
CREATE TABLE #TableRowCounts (
    [databaseNAme] VARCHAR(100), -- Name of the database
    [TableName] VARCHAR(128), -- Name of the table
    [RowCount] INT -- Number of rows in the table
);
-- Use sp_MSforeachdb to iterate through each database
EXEC sp_MSforeachdb
@command1 = '
    -- Check if the current database is not a system database
    IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    BEGIN
        -- Insert row count data into the temporary table
        INSERT INTO #TableRowCounts ([databaseNAme], [TableName], [RowCount])
        EXEC(''USE [?]; EXEC sp_MSforeachtable @command1 = ''''INSERT INTO #TableRowCounts ([databaseNAme], [TableName], [RowCount]) SELECT ''''''?'''''' AS [databaseNAme], ''''''&'''''' AS [TableName], COUNT(*) AS [RowCount] FROM &'''''''' , @replacechar = ''&'''')
    END';
-- Select the data from the temporary table to view the row counts
SELECT * FROM #TableRowCounts;
-- Drop the temporary table to clean up
DROP TABLE #TableRowCounts;


15 views1 comment

1 Comment


han gu
han gu
Jun 14

代写论文 http://www.emwchinese.com/ 行业在一定程度上缓解了留学生的学业压力,提供了针对性的帮助。然而,我们的调查发现,这种便捷的背后,往往伴随着学术诚信问题。大量留学生因为使用代写服务而受到学术处分,这不仅影响了他们的学业生涯,也对他们未来的职业生涯带来了隐患。

Like
bottom of page