Simple use of all tables and cursors under SQL Server dynamic get database

The requirement is that there are more than 2000 tables under the Stock library in SQL Server. Each table is required to delete one of today's error data.

First you have to get the names of all the tables, the code is as follows:

Select  from Where XType='U'and<>'inserthistory ' Order  by Name;

Sysobjects is a system table that records each object created in the database (constraints, defaults, logs, rules, stored procedures, and so on) as a row in the table. Only within tempdb, each temporary object occupies one row in the table. If you are more interested in sysobjects, you can go to my previous published article, SQL Server system table sysobjects use method, implement loop traversal all tables (GO)

Next is the use of cursors.

In a relational database, our thinking about queries is set-oriented. The cursor breaks this rule, and the cursor makes us think in a progressive way. For developers of Class C, this way of thinking will be more comfortable.

To continue to understand the cursor, go to: http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

For usage please see my comment below.

1 Declare @tbName varchar( -);--Define the data temp variable for each row of the cursor2 DeclareMyCursorcursor--Define a global cursor, which can be assigned directly to the global cursor, but must be released manually at the end.3  for4 SelectName fromSysObjectsWhereXType='U'  andName<>'inserthistory' Order  byName;--Assign a value to the cursor assignment5 OpenMyCursor6 7 Fetch Next  fromMyCursor into @tbName;---the first row is assigned to a temporary variable8  while 9 @ @FETCH_STATUS=0Ten begin One     exec('Delete from'+@tbName+'where DateDiff (Day,[time],getdate ()) =0;'); A     Fetch Next  fromMyCursor into @tbname;  - End  - --Close Cursors the CloseMyCursor - --Releasing Cursors - deallocateMyCursor

Finally, I would like to make some suggestions on using cursors

• If you can avoid cursors, try not to use cursors
• Be sure to close and release after you run out of use
• Try not to define cursors on large amounts of data
• Try not to update the data using cursors
• Try not to use insensitive, static, and keyset to define cursors
• If possible, use the Fast_forward keyword to define the cursor
• If only the data is read, it is best to use the Forward_only parameter when reading only with the FETCH next option

Simple use of all tables and cursors under SQL Server dynamic get database