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
No comments:
Post a Comment