How To DELETE Records Batch Wise By Using Cursor ?

DECLARE @TblName Varchar(Max), @MSQL Nvarchar(Max)
DECLARE TopTableDelete Cursor
FOR
SELECT TOP 5 Name FROM Sysobjects WHERE xtype = 'U' order by name
OPEN TopTableDelete
FETCH NEXT FROM TopTableDelete INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN      
 SET @MSQL = 'DELETE RDYadav FROM
     (SELECT SrlNo = ROW_NUMBER() OVER(ORDER BY GETDATE()),*
       FROM [dbo].['+@TblName +']) RDYadav
       WHERE SrlNo >100000'
 EXEC(@MSQL)    
 --Print @MSQL
 FETCH NEXT FROM TopTableDelete INTO @TblName
END
CLOSE TopTableDelete
DEALLOCATE TopTableDelete

How To Find How Many Tables Are There & How Many Records Are There Each Table Having ?

CREATE TABLE #COUNTS( TABLE_NAME VARCHAR(255), ROW_COUNT INT)
EXEC SP_MSFOREACHTABLE @COMMAND1='INSERT #COUNTS (TABLE_NAME, ROW_COUNT) SELECT ''?'', COUNT(*) FROM ?'
SELECT TABLE_NAME, ROW_COUNT FROM #COUNTS ORDER BY TABLE_NAME, ROW_COUNT DESC
DROP TABLE #COUNTS

dbaraja.blogspot.com

Query to find particular object belonging to what database in SQL Server

Suppose, if your SQL Server having more than 10000 databases and you got a challenge to find a particular object (proc/function/table/view..etc) is in which database?

Either we have to login into each Database and check the sysobjects
or we can run a single query in master db which can query all the DB's, search what you want and bring that you...later one is the better option because it`s save the timing also...)
Use below query to find the result

Exec sp_MSforeachdb 'if EXISTS (select * from ?..sysobjects where name like ''Prods'')
Begin
Select name from sysdatabases where name like ''?''
END'
---You just need to replace "ProdsTable" with your required object name and you can very well add more conditions to that query...

How To INSERT No.Of Records By Using T-SQL

Just Create  One Table Like
Create Table Prods1 (Pid int,PName varchar(20),Qty Int)

After complete table creation, Next following below code.

declare @pid int=1
while @pid<=1000000000000000
begin
insert into prods1 values(@pid ,'Sweet'+CONVERT(varchar(20),@pid),40)
select @pid=@pid+1
end