Get Record Count for Each Table in Your Database

When working on data migration or performing database audits, one of the most common tasks is to verify the number of records in each table. This step is crucial for planning, estimating migration time, and validating data integrity before and after the migration process.

In this blog post, I’ll show you a simple and effective way to get the row count for every table in your SQL Server database using a temporary table and a handy (though undocumented) stored procedure.

Here’s a snippet of SQL code you can copy, paste, and execute directly in your SQL Server Management Studio (SSMS):

-- Step 1: Create a temporary table to store results

CREATE TABLE #counts

(

    table_name VARCHAR(255),

    row_count INT

);

 

-- Step 2: Use sp_MSForEachTable to iterate over each table

EXEC sp_MSForEachTable

    @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?';

 

-- Step 3: View the results

SELECT table_name, row_count

FROM #counts

ORDER BY table_name, row_count DESC;

 

-- Step 4: Clean up by dropping the temp table

DROP TABLE #counts;

How It Works

·         #counts: This is a temporary table where we store each table's name along with its row count.

·         sp_MSForEachTable: This is a built-in but undocumented stored procedure provided by SQL Server. It loops through each table in the database and executes the given command in this case, counting the rows.

·         SELECT ... ORDER BY: We retrieve the data from our temp table and order it so that we can easily identify tables with the most or fewest rows.

·         DROP TABLE: As a good practice, we drop the temp table once we’re done.

What other ideas can you add to this post that I may have not mentioned?

How to cut the string up to first slash “\” from the right-hand side in SQL Server

Suppose we have a string in which we are having slash “\” character in between and we have the condition to cut the string up to the first slash from the right-hand side then we can handle this scenario with the help of the below statements.

Declare @V_String Varchar(100)

Set @V_String='Hello User\How are you doing today'

 

Select @V_String

O/P: Hello User\How are you doing today

Now as per the condition to cut the string up to the first slash dynamically, then we can use the below logic to get the desired output.

Select (reverse(left(reverse(@V_String), charindex('\', reverse(@V_String)) - 1)))

O/P: How are you doing today

 

 

What other ideas can you add to this post that I may have not mentioned?

How to convert excel imported decimal date value to real date in SQL Server

Suppose we have one excel sheet that we have imported into a table i.e. “Emp” after records are imported from excel to the table through import wizard date changes to some decimal value now condition is to convert that decimal value to the real date.

Query: select Id, CreatedDate from Emp

O/P:

Id	CreatedDate
101	42741.630556

Here we can see my CreatedDate column changed to some decimal value now we need to convert it into a real date i.e provided in an excel file.

Query: select Id, CreatedDate,

              CAST(CAST(CreatedDate AS FLOAT) -2 AS datetime) as RealDate

      from Emp

O/P: 

Id	CreatedDate	RealDate
101	42741.630556	2017-01-06 15:08:00.040

What other ideas can you add to this post that I may have not mentioned?

In SQL Server DB how to add a comma between Address Details

Suppose we have a condition to add comma between Address1, Address2, City, State, and Zip But add comma only when value is available on that particular column.

 E.g. we have one table i.e. AddressDetails as shown below

Id	Address1	Address2	City	State	Zip
101	5 Wesley	Hermosa	Johnson	Tennessee	123
102	2301 MCLain		New Madison		456

 

Query:

select  Id,

ISNULL(NULLIF(Address1, '') + ',', '') +

ISNULL(NULLIF(Address2, '') + ',', '') +

ISNULL(NULLIF(City, '') + ',', '') +

ISNULL(NULLIF(State, '') + ',', '') +

ISNULL(NULLIF(Zip, '') + '', '')-----Remove last column comma

As Address

from AddressDetails

O/P:-

Id	Address
101	5 Wesley, Hermosa, Johnson,  Tennessee, 123
102	2301 MCLain, New Madison, 456

Here for Id=102 Address2 and State column is empty but still no extra column was added into the result set.

 

What other ideas can you add to this post that I may have not mentioned?

How to identify the long-running queries in SQL Server database

 In the large SQL server database maximum time, we are stuck in the situation when database responding very slow when we check in the server end it shows the utilization of CPU is 95-98%, and when these things happen all processes running in the database starts taking so much time to execute.

To overcome this situation or to identify which query is taking time to execute the below query will help you to get SPID, when batch execution started and which query is taking time etc. so that you can go through that particular query to resolve the issue.

SELECT      r.start_time [Start Time],session_ID [SPID],

            DB_NAME(database_id) [Database],

            SUBSTRING(t.text,(r.statement_start_offset/2)+1,

            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0

            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1

            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1

            END) [Executing SQL],

            Status,command,wait_type,wait_time,wait_resource,

            last_wait_type

FROM        sys.dm_exec_requests r

OUTER APPLY sys.dm_exec_sql_text(sql_handle) t

WHERE       session_id != @@SPID -- don't show this SPID query

AND         session_id > 50 -- don't show system queries

ORDER BY    r.start_time;

What other ideas can you add to this post that I may have not mentioned?

How to add thousand’s separator in SQL server database for numeric value

Suppose we have a column in a table in which we are storing some numeric value and at the time of showing the data in a report or in any platform that we need to show that numeric value with a thousand's separator.

Query:-

Declare @V_ColumnName int

Set @V_ColumnName=1234567890

Print @V_ColumnName

O/P:- 1234567890

In the above block, we have declared one variable i.e @V_ColumnName and in that, we are storing some dummy value and when we execute this block will get the output without thousand’s separator. To show the value in output with thousand’s separator then

Query:-

Declare @V_ColumnName int

Set @V_ColumnName=1234567890

Select CONVERT(VARCHAR, CONVERT(MONEY, @V_ColumnName), 1) as ThousandsValue

 

O/P:- 1,234,567,890.00

As we are adding the comma in between the value that's why we have converted the value to varchar.

What other ideas can you add to this post that I may have not mentioned?

How to find and dynamically delete the default constraint in MS SQL Server

 When we have a condition to delete the constraint where the name of constraint is generated by the database as default then

declare @schema_name Varchar(100)

declare @table_name Varchar(100)

declare @col_name Varchar(100)

declare @Command  varchar(500)

set @schema_name = 'Scott'

set @table_name = 'Employee'

set @col_name = 'IsAdmin'

 

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] 

                                 DROP CONSTRAINT ' + d.name

from sys.tables t

join sys.default_constraints d on d.parent_object_id = t.object_id

join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id

where t.name = @table_name

and t.schema_id = schema_id(@schema_name)

and c.name = @col_name

print @Command

execute (@Command)

When we execute this block it will find the constraint details of that column and drop the default constraint.

What other ideas can you add to this post that I may have not mentioned?