Create a paging stored procedure for all user data tables in the database

Code
Create procedure generatepageproc
As
Begin
/*
// Create a paging stored procedure for all user data tables in the database
*/
-- Get the data table name
Declare @ tablename nvarchar (255)
Declare @ SQL nvarchar (4000)
Declare mycursor cursor
For select name from SYS. objects where type = 'U' and name <> 'dtproperties'
-- Obtain the field name, ID, field serial number, number of bytes occupied, number of decimal places, and allowed null.
Open mycursor
Fetch next from mycursor into @ tablename
While (@ fetch_status = 0)
Begin
Set @ SQL =
'
-- Drop stored procedure if it already exists
If exists (
Select *
From information_schema.routines
Where specific_name = ''' + @ tablename + '_ getpage''
)
Drop procedure '+ @ tablename +' _ getpage
Go
Create procedure [DBO]. ['+ @ tablename +' _ getpage]
@ Pagesize int,
@ Pageindex int,
@ Orderby varchar (1, 255)
As
Begin
Declare @ startrow int, @ endrow int
Set @ startrow = (@ pageindex-1) * @ pagesize + 1
Set @ endrow = @ startrow + @ pagesize-1
Declare @ SQL nvarchar (4000)
Set @ SQL =
''Select * from (select row_number () over (order by ''+ @ orderby +'') as rowid, * from' + @ tablename + ') as sourse''
+ ''Where rowid between'' + ltrim (rtrim (STR (@ startrow) + ''and'' + ltrim (rtrim (STR (@ endrow )))
Print @ SQL
Exec sp_executesql @ SQL
End
Go'
Exec sp_executesql @ SQL
Fetch next from mycursor into @ tablename
End
Close mycursor
Deallocate mycursor
End