THE SQL SERVER BLOG

Test Format Post

2011-09-29T15:33:00.000-07:00

Hi Baljeet,Thanks for reading it!Cheers up! I have not taken this from anywhere! :-)Here is the story behind the curtain. Actually the idea to write

Maintaining and Administering Database Servers, Determining physical and logical database design, Database/Query Performance Tuning, Working closely with development team & Programming various database objects viz. stored procedures, views, triggers, writing complex SQL Scripts, Deploying Database scripts for applications on database servers, SSIS Development

How to empty all the tables in a database?

2008-08-18T02:50:00.000-07:00

Sometimes it is exercised to empty all the tables in a database by deleting all the records from all the tables or by truncating every table in a database. This is indeed needed while we are developing a real life application and playing around with the database in order to carry out testing. But as it seems to be this task is not that much easy and trivial because usually there exists so many Primary Key-Foreign Key relationships between parent and child tables at many levels in such types of applications. If you start deleting records in improper sequence, records might not be deleted from every table successfully as depending upon the option ON DELETE CASCADE set on foreign tables records might not be deleted from parent tables as long as their corresponding child records are existing and you don’t want to be warned by SQL Server with the error message “The DELETE statement conflicted with the REFERENCE constraint…” every time you try to delete the records from some tables and at the same time you also don’t want to remember the complete hierarchy of parent-child relationships between all the tables in order to carry out delete operation in proper sequence. While emptying tables, sometimes we need to reset the SEED value of identity fields which the TRUNCATE command does it for us and DELETE command does not. But unfortunately TRUNCATE also does not work because as long as the foreign key constraints are existing, parent tables can’t to be truncated. Keeping all these in mind, I needed to create a solution which solves this problem.

I wrote the following stored procedure that performs this task for us. Just create this stored procedure in the database for which the tables need to be emptied.

-----------------------

Create Procedure dbo.sp_EmptyAllTables (@ResetIdentity Bit)

Begin

Declare @SQL VarChar(500)

Declare @TableName VarChar(255)

Declare @ConstraintName VarChar(500)

Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type='FOREIGN KEY'

Open curAllForeignKeys

Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName

While @@FETCH_STATUS=0

Begin

Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName

Execute(@SQL)

Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName

End

Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE='BASE TABLE'

Open curAllTables

Fetch Next From curAllTables INTO @TableName

While @@FETCH_STATUS=0

Begin

Set @SQL = 'DELETE FROM ' + @TableName

If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity')=1

Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)'

Execute(@SQL)

Fetch Next From curAllTables INTO @TableName

End

Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName

While @@FETCH_STATUS=0

Begin

Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName

Execute(@SQL)

Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName

End

Close curAllTables

Deallocate curAllTables

Close curAllForeignKeys

Deallocate curAllForeignKeys

End

-----------------------

The above stored procedure accepts one parameter (@ResetIdentity) which can be either 0 or 1 depending upon whether we need to reset the SEED of identity fields or not (1 for resetting otherwise 0). To empty all the tables while resetting the SEED value of identity fields at the same time, just call this store procedure in the following way:

sp_EmptyAllTables 1

I hope this technique will save some of your time needed to perform the task of emptying all the tables in a database.

-Bihag Thaker

CTE (Common Table Expression) In SQL Server 2005

2008-05-02T23:02:00.000-07:00

Hello All,

CTE (Common Table Expression) is a new feature provided by Microsoft SQL Server 2005. In real world, we often need to query hierarchical data from the database. For example, to get a list of hierarchical list of all the employees, list of product categories etc. CTE fulfills this requirement and let us query the database recursively.

Let’s us do this practically. Assume that we store different categories of computer books and any category can have sub-categories. For this, we will create a table named tblCategories with the following structure and insert some categories into this table as shown below:

Create Table tblCategories
(
CategoryID Int Constraint PK_tblCategories_CategoryID Primary Key,
CategoryName VarChar(100),
ParentCategoryID Int Constraint FK_tblCategories_ParentCategoryID References tblCategories(CategoryID)
)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(1,'Languages',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(2,'Networking',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(3,'Databases',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(4,'Visual Basic',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(5,'C#',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(6,'Java',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(7,'VB.Net',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(8,'VB 6.0',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(9,'Desktop Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(10,'Web Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(11,'ActiveX Objects and VB 6.0',8)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(12,'Network Security',2)

Now if you query the database with the following SELECT command,

Select * From tblCategories Where CategoryID = 1

You will get the following result:

CategoryID CategoryName ParentCategoryID
----------- ---------------- ----------------
1 Languages NULL

(1 row(s) affected)

No surprise! But what, if you want to get the list of all the categories/sub-categories falling under the root category ‘Languages’? To do this, you will need to perform a recursive query and to do that we use CTE.

Let’s do this with the help of CTE. To create CTE we will use the following syntax:

With cteCategories
AS (
Select CategoryID,CategoryName,ParentCategoryID
From tblCategories
Where CategoryID=1

Union All

Select C.CategoryID,C.CategoryName,C.ParentCategoryID
From tblCategories As C Inner Join cteCategories As P On C.ParentCategoryID = P.CategoryID

)

Select CategoryID,CategoryName,ParentCategoryID From cteCategories

Run the above query and see the result as shown below:

CategoryID CategoryName ParentCategoryID
----------- ---------------------------------------------------- ----------------
1 Languages NULL
4 Visual Basic 1
5 C# 1
6 Java 1
7 VB.Net 4
8 VB 6.0 4
11 ActiveX Objects and VB 6.0 8
9 Desktop Application Development with VB.Net 7
10 Web Application Development with VB.Net 7

(9 row(s) affected)

And this is what we indeed needed...!

I hope this article would have helped you. If you want to know how CTE worked in this example please wait a while for my upcoming articles.

If you have any comments, they are always welcomed!

Thank you.

-Bihag Thaker