SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created

November 30, 2006

SQL SERVER - Simple Cursor to Select Tables in Database with Static Prefix and Date Created looping-arrows Following cursor query runs through the database and find all the table with certain prefixed (‘b_’,’delete_’). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other operation on that table like to delete, print or index.

SET NOCOUNT ON
DECLARE @lcl_name VARCHAR(100)
DECLARE cur_name CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'U'
AND crdate <= DATEADD(m,-1,GETDATE())
AND name LIKE 'b_%'
OPEN cur_name
FETCH NEXT FROM cur_name INTO @lcl_name
WHILE @@Fetch_status = 0
BEGIN
SELECT @lcl_name = 'sp_depends' +@lcl_name
PRINT @lcl_name
--  EXEC (@lcl_name)
FETCH NEXT FROM cur_name INTO @lcl_name
END
CLOSE cur_name
DEALLOCATE cur_name
SET NOCOUNT OFF

Here are a few of the points one should remember with regards to cursors.

Cursors are nothing but loops as they use WHILE loops
Overusing cusrors can negatively impact performance of query as they can use too much of the resources
It is better to use set theory, operations like SELECT…INSERT or INSERT INTO…SELECT rather than cursor to insert one row at one time.

Please note that this is very old blog post and I used it in my production server at that time and till today I use the same logic in my production server after so many years.

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL Cursor, SQL DateTime, SQL Scripts, SQL Server, SQL Stored Procedure

SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database

SQL SERVER – Cursor to Kill All Process in Database

11 Comments. Leave new

Tariq
August 6, 2007 4:26 am
Nice explaination.
Reply
Prashant Pandey
September 16, 2007 11:03 am
As you said that your querry is usefull for print or reindex,
Can u please tell me Wht is the adavntage and disadvantages of re-indexing and wht is the need to reinedxing. When to reindex your table and WHY???
Reply
Naveen
January 6, 2009 6:29 pm
Except and Intersect is not working in MS-SQL 2005…
May i know the solution or way to using these Operators in SQL Server 2005. If there is any possibility to use or not….
Reply
Josh
April 10, 2009 2:36 am
So I noticed that when you used NOCOUNT ON, you also used NOCOUNT OFF at the end of the SQL.
Would this be standard programming practice to pair the ON/OFF? Would you still pair it if the SQL was part of a stored procedure, or just use the NOCOUNT ON and leave out the NOCOUNT OFF?
Reply
Madhivanan
February 5, 2010 8:08 pm
The same can be done without using a cursor
SELECT ‘sp_depends ‘ +name FROM sysobjects
WHERE type = ‘U’
AND crdate <= DATEADD(m,-1,GETDATE())
AND name LIKE 'b_%'
Reply
- TJ
  August 5, 2013 8:50 pm
  Could we use INFORMATION_SCHEMA instead of sysobjects for the same?
  I don’t know what object of INFORMATION_SCHEMA could be used to check the last modified date of a table. I believe we need to involve DMVs here. If there is a work around, please let me know.
  Thanks
  Reply
jagadish
March 12, 2010 6:10 pm
dear all..
i want to do some operation in database in stored procedure… i have to use array data type…..
so if anyone know this… please tell me…..
Reply
- almond
  March 23, 2010 2:56 pm
  Thanks for the method. I done it and succeeded.
  madhi you couldn’t execute the procedure by using your statement
  Reply
Nikhildas
November 24, 2011 10:48 am
Nice Post..
Thanks & Regards,
Nikhildas
Reply
sheheryar irfan
December 19, 2011 12:23 pm
How to write a table from cursor in sql ?
Reply
Vikas
February 14, 2012 5:53 pm
Hi Pinal. Can we display data using Select Cursor like statement
Reply