A colleague in my office asked me if its possible to have a routine that deletes the tables contents using SQL Server. I definitely said yes and redirected him to the DELETE command in T-SQL. I said that the DELETE command would delete all contents of the table if you will not specify a WHERE statement. Thus doing this,
DELETE FROM TableName
would delete all contents of TableName.
But what if you have a 50+ tables in your database? I guess its not practical to write a DELETE statement for each of the table. That's why I've come up with this solution. I'm gonna show the code first and walk you right through it.
| DECLARE del_cursor CURSOR FOR
SELECT name FROM dbo.sysobjects
WHERE xtype='U' DECLARE @name nvarchar(128)
DECLARE @sql nvarchar(200) OPEN del_cursor
FETCH NEXT FROM del_cursor INTO @name WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DELETE FROM dbo.' + @name
EXEC(@sql)
FETCH NEXT FROM del_cursor INTO @name
END CLOSE del_cursor
DEALLOCATE del_cursor |
So how does this work? Let me dissect each lines of the code. The first line declares a CURSOR variable.
DECLARE del_cursor CURSOR FOR
SELECT name FROM dbo.sysobjects
WHERE xtype='U' You can look at a CURSOR variable as a pointer to a table or a resultset. In our case we are pointing to a resultset,
SELECT name FROM dbo.sysobjects
WHERE xtype='U'
Before you get confused, the sysobjects table is a system table which is present in all Sql Databases. It's the main storage for objects being used in the database (views, stored procedures, tables, etc...). What I am actually doing here is navigate the sysobjects table and look for User defined tables. That's what the WHERE xtype='U' means.
So after creating the cursor I've declared 2 variables.
DECLARE @name nvarchar(128)
DECLARE @sql nvarchar(200) These will be the placeholder for our generated DELETE statement and the tablename. After that, lets open the cursor.
OPEN del_cursor
This action would allocate a name (variable name) for the cursor in the current session. Once opening a cursor don;t forget to close and deallocate it. The next statement initializes the cursor,
FETCH NEXT FROM del_cursor INTO @name
As the statement reads, it would fetch the data from the cursor and store it in the variable @name. We need to do this in order to point our cursor at the very first record in the resultset. So by executing this statement @name now holds the very first name field in our query. Next, we must traverse the resultset.
WHILE @@FETCH_STATUS = 0
This loop would check if the cursor is still pointing in a valid record. This is to prevent over-looping the resultset. The @@FETCH_STATUS is a global variable which gives back any positive/negative value whenever the cursor is pointing at the beginning or end of resultset.
SET @sql = 'DELETE FROM dbo.' + @name
This statement manually creates the DELETE sql statment to be executed. It uses the @name variable which contains the name of the table.
EXEC(@sql)
This is where the actual execution of the generated DELETE sql to the server.
FETCH NEXT FROM del_cursor INTO @name
Calling this command would move the cursor to the next record in the resultset.
CLOSE del_cursor
DEALLOCATE del_cursor
As I said previously, upon using and opening a cursor you must close and deallocate it. If you don't, an error would generate uopn attempting to open the cursor. Because the cursor is still open in the current connection.
If you want to exclude other tables from being cleared you can check the @name variable if it matches your excluded table. Here;s a sample:
IF @name != 'table1' AND @name != 'table2'
BEGIN
SET @sql = 'DELETE FROM dbo.' + @name
EXEC(@sql)
END
FETCH NEXT FROM del_cursor INTO @name
Really hope this simple tutorial helps. :)