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

Following cursor query runs through 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 opertation on that table like delete, print or reindex.

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

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

About these ads

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

  1. 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???

  2. 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….

  3. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

  4. 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?

    • 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

  5. 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…..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s