Thursday, 13 November 2014

Example PL-SQL Procedure to truncate tables in SQL server using cursors

Example PL-SQL Procedure to truncate tables in SQL server using Cursors

/* The below procedure truncates all the tables that end with _CDW using cursor function */


DECLARE @name VARCHAR(256) -- table name
DECLARE @fileName VARCHAR(256)


DECLARE db_cursor CURSOR FOR
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_CDW'


OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName =  @name
exec (@fileName)
FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

Check out SQL server date manipulation functions:
http://dwbitechguru.blogspot.ca/2014/11/sql-server-important-date-manipulation.html