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)
pinaldave
Nice explaination.
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???
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….