Name of a data table whose total number of records in the database is not 0

Source: Internet
Author: User

-- 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.