http://www.codeproject.com/KB/ajax/IntroAjaxASPNET.aspx
ASP.NET callbacks for AJAX using ICallbackEventHandler interface, AJAX Pro (http://ajaxpro.codeplex.com/) some interesting techniques which are good to know even though we have some latest and simple AJAX techniques like JQuery AJAX.
http://javarevisited.blogspot.com/2011/09/how-to-write-production-quality-code.html
A friend shared this link. Most of the experienced people will surely agree this but its worth to read and refresh ourself once again.
I have used 3 Procedures to make Truncate Table easy, along with truncating all the related tables for a given table.
— TruncateTable is the main proc to be used to see all tables that are getting truncated by not giving 2nd parameter and once you satisfy with the list of tables that are going to be affected then you can run the same proc by giving 2nd parameter as 0 (Zero).
CREATE PROCEDURE [dbo].[TruncateTable]
@TABLE VARCHAR(100),
@NoTruncateOnlyShowRelations BIT = 1
AS
BEGIN
DECLARE @LEVEL INT
SET @LEVEL = 1
CREATE TABLE #FKKeysTable
(PKTableName VARCHAR(100), PKColumnName varchar(100),
FKTableName varchar(100), FKColumnName varchar(100), ForeignKey varchar(100), DROPCon BIT, CREATECon BIT, NESTLEVEL INT)
IF @NoTruncateOnlyShowRelations = 0
BEGIN
EXEC TruncateTableRec @TABLE, @LEVEL
END
ELSE
BEGIN
EXEC ShowTruncateTableRec @TABLE, @LEVEL
END
SELECT
*
FROM
#FKKeysTable
ORDER BY
NESTLEVEL
DROP TABLE #FKKeysTable
END
GO
————————————–
CREATE PROCEDURE [dbo].[TruncateTableRec]
@TABLE VARCHAR(100),
@LEVEL INT
AS
BEGIN
DECLARE @PKTableName varchar(255),
@PKColumnName varchar(255),
@FKTableName varchar(255),
@FKColumnName varchar(255),
@ForeignKey varchar(255),
@SQL varchar(3000)
INSERT INTO #FKKeysTable
SELECT
OBJECT_NAME (f.referenced_object_id) AS PKTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKColumnName,
OBJECT_NAME(f.parent_object_id) AS FKTableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKColumnName,
f.name AS ForeignKey,
0,
0,
@LEVEL
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID
LEFT OUTER JOIN #FKKeysTable AS fkt ON fkt.PKTableName = @TABLE
where
OBJECT_NAME (f.referenced_object_id) = ” + @TABLE + ”
AND (fkt.PKTableName IS NULL AND fkt.PKColumnName IS NULL AND fkt.FKTableName IS NULL
AND fkt.FKColumnName IS NULL AND fkt.ForeignKey IS NULL)
order by
PKTableName
SELECT TOP 1 @PKTableName = PKTableName, @PKColumnName = PKColumnName,
@FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
FROM #FKKeysTable
where DROPCon = 0 AND NESTLEVEL = @LEVEL
WHILE (ISNULL(@ForeignKey, ”) <> ” )
BEGIN
PRINT @TABLE + ‘1’
SET @SQL = ‘IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N”[dbo].[‘+@ForeignKey+’]”) AND parent_object_id = OBJECT_ID(N”[dbo].[‘+@FKTableName+’]”)) ALTER TABLE [dbo].[‘+@FKTableName+’] DROP CONSTRAINT [‘+@ForeignKey+’]’
EXEC (@SQL)
UPDATE #FKKeysTable SET DROPCon = 1 WHERE ForeignKey = @ForeignKey
SET @ForeignKey = ”
SELECT TOP 1 @PKTableName = PKTableName, @PKColumnName = PKColumnName,
@FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
FROM #FKKeysTable
where DROPCon = 0 AND NESTLEVEL = @LEVEL
END
PRINT @TABLE + ‘2’
SET @SQL = ‘TRUNCATE TABLE ‘ + @TABLE
EXEC (@SQL)
PRINT @TABLE + ‘3’
SELECT TOP 1 @PKTableName = PKTableName, @PKColumnName = PKColumnName,
@FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
FROM #FKKeysTable
where CREATECon = 0 AND NESTLEVEL = @LEVEL
WHILE (ISNULL(@ForeignKey, ”) <> ” )
BEGIN
PRINT @TABLE + ‘4’
SET @SQL = ‘DECLARE @LEVEL1 INT SET @LEVEL1 =’ + CAST(@LEVEL AS VARCHAR(10))+ ‘ SET @LEVEL1 = @LEVEL1 + 1 IF((SELECT COUNT(*) FROM ‘ + @FKTableName + ‘) > 0) EXEC dbo.TruncateTableRec ‘ + @FKTableName + ‘,@LEVEL1’
EXEC (@SQL)
PRINT @TABLE + ‘5’
SET @SQL = ‘ALTER TABLE [dbo].[‘+@FKTableName+’] ADD CONSTRAINT [‘+@ForeignKey+’] FOREIGN KEY([‘+@FKColumnName+’]) REFERENCES [dbo].[‘+@PKTableName+’] ([‘+@PKColumnName+’])’
EXEC (@SQL)
UPDATE #FKKeysTable SET CREATECon = 1 WHERE ForeignKey = @ForeignKey
SET @ForeignKey = ”
SELECT TOP 1 @PKTableName = PKTableName, @PKColumnName = PKColumnName,
@FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
FROM #FKKeysTable
where CREATECon = 0 AND NESTLEVEL = @LEVEL
END
END
GO
———————————
CREATE PROCEDURE [dbo].[ShowTruncateTableRec]
@TABLE VARCHAR(100),
@LEVEL INT
AS
BEGIN
DECLARE @PKTableName varchar(255),
@PKColumnName varchar(255),
@FKTableName varchar(255),
@FKColumnName varchar(255),
@ForeignKey varchar(255),
@SQL varchar(3000)
INSERT INTO #FKKeysTable
SELECT
OBJECT_NAME (f.referenced_object_id) AS PKTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKColumnName,
OBJECT_NAME(f.parent_object_id) AS FKTableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKColumnName,
f.name AS ForeignKey,
0,
0,
@LEVEL
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.OBJECT_ID
LEFT OUTER JOIN #FKKeysTable AS fkt ON fkt.PKTableName = @TABLE
where
OBJECT_NAME (f.referenced_object_id) = ” + @TABLE + ”
AND (fkt.PKTableName IS NULL AND fkt.PKColumnName IS NULL AND fkt.FKTableName IS NULL
AND fkt.FKColumnName IS NULL AND fkt.ForeignKey IS NULL)
order by
PKTableName
SELECT TOP 1 @PKTableName = PKTableName, @PKColumnName = PKColumnName,
@FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
FROM #FKKeysTable
where CREATECon = 0 AND NESTLEVEL = @LEVEL
WHILE (ISNULL(@ForeignKey, ”) <> ” )
BEGIN
–if(@LEVEL + 1 < 31)
–BEGIN
SET @SQL = ‘DECLARE @LEVEL1 INT SET @LEVEL1 =’ + CAST(@LEVEL AS VARCHAR(10))+ ‘ SET @LEVEL1 = @LEVEL1 + 1 IF((SELECT COUNT(*) FROM ‘ + @FKTableName + ‘) > 0) EXEC dbo.ShowTruncateTableRec ‘ + @FKTableName + ‘,@LEVEL1’
EXEC (@SQL)
–END
UPDATE #FKKeysTable SET CREATECon = 1 WHERE ForeignKey = @ForeignKey
SET @ForeignKey = ”
SELECT TOP 1 @PKTableName = PKTableName, @PKColumnName = PKColumnName,
@FKTableName = FKTableName, @FKColumnName = FKColumnName, @ForeignKey = ForeignKey
FROM #FKKeysTable
where CREATECon = 0 AND NESTLEVEL = @LEVEL
END
END
GO
——————————-
— Testing
/*
CREATE TABLE [dbo].[PKTable](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [varchar](50) NULL,
CONSTRAINT [PK_PKTable] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FKTable](
[FKCol] [int] IDENTITY(1,1) NOT NULL,
[Col1] [int] NOT NULL,
[Col3] [varchar](50) NULL,
CONSTRAINT [PK_FKTable] PRIMARY KEY CLUSTERED
(
[FKCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FKTableLeve2](
[FKCol] [int] IDENTITY(1,1) NOT NULL,
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL,
[Col3] [varchar](50) NULL,
CONSTRAINT [PK_FKTableLevel2] PRIMARY KEY CLUSTERED
(
[FKCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
— Add FOREIGN KEY Constraints
ALTER TABLE [dbo].[FKTable] ADD CONSTRAINT [FK_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[PKTable] ([Col1])
ALTER TABLE [dbo].[FKTableLeve2] ADD CONSTRAINT [FKLevel2_Col1] FOREIGN KEY([Col1])
REFERENCES [dbo].[FKTable] ([FKCol])
ALTER TABLE [dbo].[FKTableLeve2] ADD CONSTRAINT [FKLevel3_Col1] FOREIGN KEY([Col2])
REFERENCES [dbo].[PKTable] ([Col1])
— INSERT Data
INSERT INTO PKTable VALUES(‘a’)
INSERT INTO PKTable VALUES(‘b’)
INSERT INTO FKTable VALUES(1,’a’)
INSERT INTO FKTable VALUES(2,’b’)
INSERT INTO FKTableLeve2 VALUES(1, 1, ‘a’)
INSERT INTO FKTableLeve2 VALUES(2, 1, ‘b’)
— Execute Procedure to see all the tables (including the recursive sub tables) that PKTable one is related with
EXEC TruncateTable ‘PKTable’
— Execute Procedure with flag 0 to truncate the data with out effecting the Foreign Key relations
EXEC TruncateTable ‘PKTable’, 0
*/
http://sqlservergeeks.com/blogs/RakeshMishra/sql-server-bi/76/delete-vs-truncate
Truncate table does not work in case if there is any foriegn key relation on the table. We have to drop the FK constraint before truncating the data and then we have to recreate the constraint.
Many places I have seen the following suggestion and it will not work.
ALTER TABLE <TABLE1> NOCHECK CONSTRAINT ALL
ALTER TABLE <TABLE1> DISABLE TRIGGER ALL
TRUNCATE TABLE <TABLE1>
ALTER TABLE <TABLE1> CHECK CONSTRAINT ALL
ALTER TABLE <TABLE1> ENABLE TRIGGER ALL
More abt disabling indexes/constraints …
If you have a situation to run some sql command across all user tables in the DB or across all DBs in any SQL Server then read the below link …
http://msdn.microsoft.com/en-us/library/cc671619.aspx
PacMan – The SQL Server Integration Services Package Manager is a utility designed to permit batch operations on arbitrary sets of SSIS packages. Users can select a single package, a Visual Studio project or solution or a file system folder tree and then validate or update all selected packages in one operation.
VJ
Tech Hub 