How to get Record Count for all the tables in SQL Server Instance in SQL Server ?

Basically To get the record count for all tables in a SQL Server instance, you can use a combination of SQL queries to retrieve this information. One way to achieve this is by querying the sys.tables system view and dynamically generating a query for each table. Here's a step-by-step explanation:

1.Querying sys.tables to Get Table Names:

Start by querying the sys.tables system view to get a list of all tables in the database. This query retrieves the schema name and table name for each table.

SELECT 
    schema_name(schema_id) AS schema_name,
    name AS table_name
FROM 
    sys.tables;

2.Dynamically Generating SQL Queries:

Use the result from the previous query to dynamically generate SQL queries for each table. You can use a cursor, a loop, or a dynamic SQL query to achieve this. Here’s an example using a cursor:

DECLARE @schema_name NVARCHAR(128);
DECLARE @table_name NVARCHAR(128);
DECLARE @sql_query NVARCHAR(MAX);

DECLARE table_cursor CURSOR FOR
SELECT 
    schema_name(schema_id) AS schema_name,
    name AS table_name
FROM 
    sys.tables;

OPEN table_cursor;

FETCH NEXT FROM table_cursor INTO @schema_name, @table_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql_query = 'SELECT ''' + @schema_name + '.' + @table_name + ''' AS table_name, COUNT(*) AS record_count FROM ' + @schema_name + '.' + @table_name;
    EXEC sp_executesql @sql_query;

    FETCH NEXT FROM table_cursor INTO @schema_name, @table_name;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;

1. This script generates a dynamic SQL query for each table, retrieves the record count, and prints the result.
2. Viewing Results: When you execute the script, you’ll see a result set with columns table_name and record_count for each table in the database.

Note: Be cautious when using dynamic SQL queries, and make sure to validate and sanitize inputs to prevent SQL injection. Additionally, keep in mind that counting records for large tables can have a performance impact. If performance is a concern, consider using statistical sampling or other methods for estimating the record count.