Tuesday, August 11, 2009

Deleting script data from Data Base

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Delete_SpamCode]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tablename nvarchar(20)
declare @schema_name nvarchar(20)
declare @column_name nvarchar(20)
declare @strQ nvarchar(1000)

declare testcursor cursor for
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
open testcursor
FETCH NEXT FROM testcursor INTO @tablename,@schema_name,@column_name
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY
set @strQ= 'update '+@tablename+' set '+@column_name+'=SUBSTRING('+@column_name+',0,CHARINDEX(''<'','+@column_name+'))
WHERE CHARINDEX(''<'','+@column_name+')>0 AND CHARINDEX(''>'','+@column_name+')>0'
print @strQ
EXECUTE(@strQ)


END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
FETCH NEXT FROM testcursor INTO @tablename,@schema_name,@column_name
END
CLOSE testcursor
DEALLOCATE testcursor

END

--exec [sp_Delete_SpamCode]

No comments: