-- Query the names of data tables whose total number of records is not 0
-- Declare Variables
Declare @ tablename nvarchar (250)
-- Declare a cursor mycursor. The number of parameters in the SELECT statement must be the same as the variable name retrieved from the cursor.
Declare mycursor cursor for select name from SYS. Tables order by name
-- Open the cursor
Open mycursor
-- Retrieve the data from the cursor and assign values to the variables we just declared.
Fetch next from mycursor into @ tablename
-- Determine the cursor status
-- 0 fetch statement succeeded
--- 1 The fetch statement fails or this row is not in the result set
--- 2 the extracted row does not exist
Declare @ tablenamelist nvarchar (4000)
Set @ tablenamelist =''
While (@ fetch_status = 0)
Begin
-- Display the value we retrieve with the cursor each time
-- Print 'a data entry is successfully retrieved from the curss'
Declare @ SQL as nvarchar (500), @ I as int;
Set @ SQL = n' select @ P = isnull (count (1), 0) from
'+ @ Tablename + '';
Exec sp_executesql @ SQL, n' @ P as int output', @ P = @ I output
-- Name of the table whose output records are not 0
If @ I> 0
Begin
If (@ tablenamelist = '')
Set @ tablenamelist = @ tablename
Else
Set @ tablenamelist = @ tablenamelist + ',' + @ tablename
Print 'select * from' + @ tablename + ';'
End
-- Use a cursor to retrieve the next record
Fetch next from mycursor into @ tablename
End
-- Close the cursor
Close mycursor
-- Undo cursor
Deallocate mycursor
Select @ tablenamelist