Find number of rows in table using cursor in SQL

Regarding MSSQL,

Following code uses sys.objects table to retrieve ‘table names’, and running cursor on it to loop through each tables, then used system procedure ‘sp_executesql‘ to run a simple query to find number of records in it.

declare @tblname as nvarchar(200)
declare curForCount cursor for
select name from sys.objects where type=’u’ and name ‘sysdiagrams’

OPEN curForCount
fetch next from curForCount
into @tblname
WHILE @@FETCH_STATUS=0
BEGIN

declare @query nvarchar(1000);
set @query=’select ”’ + @tblname + ‘ — ” + cast(count(*) as nvarchar(20)) from ‘ + @tblname

exec sp_executesql @query

fetch next from curForCount
into @tblname
END
CLOSE curForCount
DEALLOCATE curForCount

 

Other Code stuff from Siddharth Borania:

LINQ queries with join in c#

https://siddharthboraniait.wordpress.com/2012/09/06/linq-query-in-csharp-asp-net-join

SQL – compare number of records in each tables of different database at remote server

Some time during development you need to compare tables(same tables) of different different database , and things becomes tough if both database are on remote server.

At such time you need to query to separate database of remote server.

Using ‘Linked server’ you can query remote database.

To establish linked server, first you have to create two linked server in order to access them, you can add it using following command:

exec sp_addlinkedserver @server=[server1]
exec sp_addlinkedserver @server=[server2]

(Assuming you are connected with same database locally,  and you have to also give authentication information in case of any there)

Then by running ‘cursor’ on all tables you can achieve result.

declare tmpCUR cursor for

select name from sysobjects where xtype=’U’

/*   to scan each tables in database   */

open tmpCUR

declare @tmpName nvarchar(400)

fetch next from tmpCUR into @tmpName While @@fetch_Status = 0
BEGIN

declare @query nvarchar(1000)

set @query=’select ”’ + @tmpName+”’,count(*) from [server1].[database1].dbo.’ + @tmpName
exec sp_executesql @query

set @query=’select ”’ + @tmpName+”’,count(*) from [server2].[database2].dbo.’ +@tmpName
exec sp_executesql @query

fetch next from tmpCUR
into @tmpName

END

CLOSE tmpCUR
DEALLOCATE tmpCUR