ClearData

We can use this sp to clear data in tables. I mainly created this sp for two reason. 1.This is useful to commence the testing from scratch. 2.Whenever we ask the database backup from our customer. They hesitant to give the production database because of confidential. I have used Delete and Truncate statement to clear the data. Please refer SQL Books online difference of Delete and Truncate statement.

/* To Execute the sp
--exec ClearTableData
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[ClearTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClearTableData]
GO

Create Proc ClearTableData
As

Declare @TableName Varchar(1000), @Reference Varchar(4), @SQL Varchar(8000), @ObjectName Varchar(8000),
@TabName Varchar(1000)

SET NOCOUNT ON

If db_name() In ('master', 'model', 'msdb', 'tempdb')
Begin
Print 'system database are not allowed to clear'
return 0
End

Set @ObjectName = ''
Set @TabName = ''

-- Delete data in Child Object and its Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select SO.Name "ChildObject",
Case When (Select Count(*) From SysReferences Where rkeyid = SO.id) = 0 Then 'N' Else 'Y' End As Ref
From SysObjects SO, SysReferences SR, SysObjects SO1
Where SO.id = SR.fkeyid And SO.xtype = 'U' And SO1.ID = SR.rkeyid
Order By SO1.Name Desc, SO.Name
OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference

WHILE @@FETCH_STATUS = 0
BEGIN
If @Reference = 'Y'
Begin
-- Concatenate Primary Key Table name
Set @ObjectName = @ObjectName + ',' + @TableName
End
Else If @Reference = 'N'
Begin
Begin
-- Clear data from Foreign Key Tables(Child object)
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor

If Len(@Reference) > 0
Begin
-- Clear data for Concatenated Primary Key Tables
Set @ObjectName = Right(@ObjectName,Len(@ObjectName)-1)

While Len(@ObjectName) > 0
Begin
If (CHARINDEX(',',@ObjectName) > 0)
Begin
Set @TabName = Substring(@ObjectName, 1, CharIndex(',',@ObjectName)-1)
Set @ObjectName = SUBSTRING(@ObjectName, Len(@TabName) + 2, Len(@ObjectName))
End
Else
Begin
Set @TabName = @ObjectName
Set @ObjectName = ''
End
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TabName + ']'
exec (@SQL)
End
End
End
End
--Delete Data in Standalone Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id In (Select rkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TableName + ']'
exec (@SQL)
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
-- Delete data for BaseDate/Master Table
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name

OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

Begin
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
SET NOCOUNT OFF

Convert Unique Identifier to Varchar data type

SET QUOTED_IDENTIFIER OFF

DECLARE @ObjectID Int, @ObjectName Varchar(100),
@EmptyString Char(1), @IsDefaultBound Char(1),
@ExecuteScript Varchar(8000), @IndexName Varchar(256),
@Loop Int

set nocount on

Create Table #TableInformation (ObjectID Int Not Null, ObjectName Varchar(100) Not Null,
Parent_ObjectID Int Not Null, Parent_ObjectName Varchar(100) Not Null,
ObjectType Char(2) Not Null)
-- IsRowGuid Char(1) Not Null, IsDefaultBound Char(1) Null)
DECLARE csrTableName CURSOR FOR
SELECT SO.ID, SO.Name
FROM SysObjects SO
WHERE SO.xType = 'U'
ORDER BY SO.Name

Set @EmptyString = ''

OPEN csrTableName

FETCH NEXT FROM csrTableName
INTO @ObjectID, @ObjectName

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN

-- 36 = Unique Identifier data type

--Generate Script to Drop Foreign Constraint & Select statement will generate Foreign Key ID, Foreign Key Name,
--Parent Object ID & Name for Foreign Key and Object Type As "FK"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
From SysForeignKeys Where rkeyID = @ObjectID
Union
Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
From SysForeignKeys Where fkeyID = @ObjectID

--Generate Script to Drop Primary and Unique Constraint & Select statement will generate Primary Key ID,
--Primary Key Name, Parent Object ID & Name for Primary Key and Object Type As "PK"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select ID, Object_Name(ID), @ObjectID, @ObjectName, 'C'
From SysObjects Where Parent_Obj = @ObjectID And xType In ('PK', 'UQ')

--Generate Script to Default Constraint & Select statement will generate Parent Object ID, Default Column Name &
--Parent Object ID & Name for Default Column and Object Type As "D"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select cDefault, Object_Name(SC.cDefault), @ObjectID, @ObjectName, 'C'
From SysColumns SC, SysComments SCM
Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsDefaultCnst') = 1 And
SC.xType = 36

--Generate Script to Drop ROWGUID & Select statement will generate RowGUID Column ID, RowGUID Column Name &
--Parent Object ID & Name for RowGUID Column and Object Type As "RG"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select ID, Name, @ObjectID, @ObjectName, 'RG'
From SysColumns Where ID = @ObjectID And Number = 0 And ColumnProperty(@ObjectID, Name, 'IsRowGuidCol') = 1

--Generate Script to Alter Column to Varchar(38) & Select statement will generate UniqueIdentifier Type ID,
--UniqueIdentifier Column Name, Parent Object ID & Name for UniqueIdentifier Column and Object Type As "UI"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select xType, Name, ID, Object_Name(ID), 'UI'
From SysColumns Where ID = @ObjectID And xType = 36

--Generate Script to UnBind Defaults for ID Column & Select statement will generate Parent Object ID,
--Default Column Name & Parent Object ID & Name for Default Column and Object Type As "DF"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select @ObjectID, SC.Name, @ObjectID, @ObjectName, 'DF'
From SysColumns SC, SysComments SCM
Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsConstraint') = 0 And
SC.xType = 36

Declare csrIndexName CURSOR FOR
Select Name From SysIndexes
Where ID = @ObjectID

OPEN csrIndexName

FETCH NEXT FROM csrIndexName
INTO @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Loop = 0
While @Loop < 10
Begin
IF Exists (Select SC.Name, SC.ID From SysColumns SC, SysIndexes SI
Where SC.ID = SI.ID And SI.ID = @ObjectID And
SI.Name = @IndexName And SC.xtype = 36 And SI.Name Not Like '%_WA_%' And
SC.Name = Index_col(Object_Name(@ObjectID), SI.indid,@Loop))
Begin
--Generate Script to drop Index & Select statement will generate Parent Object ID of the Index Column,
--IndexName, Parent Object ID & Name of the Table Name, and Object Type As "I"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select @ObjectID, @IndexName, @ObjectID, @ObjectName, 'I'
Break
End
Set @Loop = @Loop + 1
End

FETCH NEXT FROM csrIndexName
INTO @IndexName
END

CLOSE csrIndexName
DEALLOCATE csrIndexName

FETCH NEXT FROM csrTableName
INTO @ObjectID, @ObjectName
END

CLOSE csrTableName
DEALLOCATE csrTableName

PRINT @EmptyString

Select * From #TableInformation Order By ObjectType

PRINT @EmptyString

DECLARE csrScript CURSOR FOR


Select Case When ObjectType = 'DF' Then
'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Name = "' + ObjectName + '" And cDefault > 0 )' + Char(13) +
'Exec sp_unbindefault "[' + Parent_ObjectName + '].' + ObjectName + '"' + Char(13) --+ 'GO'

When ObjectType = 'RG' Then
'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Number = 0 And
Name = "' + ObjectName + '" And ColumnProperty(Object_ID ("[' + Parent_ObjectName + ']")' + ', Name, "IsRowGuidCol") = 1 )' + Char(13) +
'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] DROP ROWGUIDCOL' + Char(13) --+ 'GO'

When ObjectType = 'C' Then
'If Exists (Select ID From SysObjects Where ID = Object_ID("[' + ObjectName + ']") And Parent_Obj = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) +
'ALTER TABLE [' + Parent_ObjectName + '] DROP CONSTRAINT [' + ObjectName + ']' + Char(13) --+ 'GO'

When ObjectType = 'I' Then
'If Exists (Select ID From SysIndexes Where Name = "' + ObjectName + '" And ID = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) +
'DROP INDEX [' + Parent_ObjectName + '].' + ObjectName + + Char(13) --+ 'GO'

When ObjectType = 'UI' Then
'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] Varchar(38)' + Char(13) --+ 'GO'

End As "Script"
From #TableInformation Order By ObjectType

Set @ExecuteScript = ''

OPEN csrScript

FETCH NEXT FROM csrScript
INTO @ExecuteScript

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN
--Print (@ExecuteScript)
Exec (@ExecuteScript)
FETCH NEXT FROM csrScript
INTO @ExecuteScript
END

CLOSE csrScript
DEALLOCATE csrScript

Drop Table #TableInformation

set nocount off

GO

Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server

When I first started using SQL Server as a novice, I was initially confused as to the differences between the SELECT statement, views, and stored procedures. They all seemed to perform more or less the same task (retrieve data), and I wanted to know the pros and cons of using each.

Why would SQL Server offer three different options to retrieve data from database? As a developer and new DBA, I took it upon myself to learn everything I could about these options, why they may be required, and when they should be used. This article is a result of my learning and experience, and explains the differences between SELECT statements, views, and stored procedures for the DBA or developer new to SQL Server. I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code into Query Analyzer I have provided in order to more fully understand and appreciate the differences between the SELECT statement, views, and stored procedures. I have divided this article into three parts to better explain this information.