Archive for category Transact-SQL

sp_MSForeachDb and sp_MSForeachTable

Posted by Ozer Senturk on Tuesday, 9 March, 2010 
EXEC dbo.sp_MSForeachDb 'DUMP TRANSACTION ? WITH NO_LOG DBCC SHRINKFILE (N''?'', EMPTYFILE)'
EXEC dbo.sp_MSForeachTable 'TRUNCATE TABLE DINDirect.dbo.[?]'
Tags: , Category: ICT, Transact-SQL Comments (0)

Function for replacing HTML characters

Posted by Ozer Senturk on Tuesday, 9 March, 2010 
CREATE FUNCTION [dbo].[replace_html_characters]
(
      @html_content NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
 SET @html_content=REPLACE(@html_content,'"','"');
 SET @html_content=REPLACE(@html_content,'&','&');
 SET @html_content=REPLACE(@html_content,'<','<');
 SET @html_content=REPLACE(@html_content,'>','>');
 SET @html_content=REPLACE(@html_content,' ',' ');
 SET @html_content=REPLACE(@html_content,'¡','¡');
 SET @html_content=REPLACE(@html_content,'¢','¢');
 SET @html_content=REPLACE(@html_content,'£','£');
 SET @html_content=REPLACE(@html_content,'¤','¤');
 SET @html_content=REPLACE(@html_content,'¥','¥');
 SET @html_content=REPLACE(@html_content,'¦','¦');
 SET @html_content=REPLACE(@html_content,'§','§');
 SET @html_content=REPLACE(@html_content,'¨','¨');
 SET @html_content=REPLACE(@html_content,'©','©');
 SET @html_content=REPLACE(@html_content,'ª','ª');
 SET @html_content=REPLACE(@html_content,'«','«');
 SET @html_content=REPLACE(@html_content,'¬','¬');
 SET @html_content=REPLACE(@html_content,'­','­');
 SET @html_content=REPLACE(@html_content,'®','®');
 SET @html_content=REPLACE(@html_content,'¯','¯');
 SET @html_content=REPLACE(@html_content,'°','°');
 SET @html_content=REPLACE(@html_content,'±','±');
 SET @html_content=REPLACE(@html_content,'²','²');
 SET @html_content=REPLACE(@html_content,'³','³');
 SET @html_content=REPLACE(@html_content,'´','´');
 SET @html_content=REPLACE(@html_content,'µ','µ');
 SET @html_content=REPLACE(@html_content,'¶','¶');
 SET @html_content=REPLACE(@html_content,'·','·');
 SET @html_content=REPLACE(@html_content,'¸','¸');
 SET @html_content=REPLACE(@html_content,'¹','¹');
 SET @html_content=REPLACE(@html_content,'º','º');
 SET @html_content=REPLACE(@html_content,'»','»');
 SET @html_content=REPLACE(@html_content,'¼','¼');
 SET @html_content=REPLACE(@html_content,'½','½');
 SET @html_content=REPLACE(@html_content,'¾','¾');
 SET @html_content=REPLACE(@html_content,'¿','¿');
 SET @html_content=REPLACE(@html_content,'€','€');
 SET @html_content=REPLACE(@html_content,'À','À');
 SET @html_content=REPLACE(@html_content,'Á','Á');
 SET @html_content=REPLACE(@html_content,'Â','Â');
 SET @html_content=REPLACE(@html_content,'Ã','Ã');
 SET @html_content=REPLACE(@html_content,'Ä','Ä');
 SET @html_content=REPLACE(@html_content,'Å','Å');
 SET @html_content=REPLACE(@html_content,'Æ','Æ');
 SET @html_content=REPLACE(@html_content,'Ç','Ç');
 SET @html_content=REPLACE(@html_content,'È','È');
 SET @html_content=REPLACE(@html_content,'É','É');
 SET @html_content=REPLACE(@html_content,'Ê','Ê');
 SET @html_content=REPLACE(@html_content,'Ë','Ë');
 SET @html_content=REPLACE(@html_content,'Ì','Ì');
 SET @html_content=REPLACE(@html_content,'Í','Í');
 SET @html_content=REPLACE(@html_content,'Î','Î');
 SET @html_content=REPLACE(@html_content,'Ï','Ï');
 SET @html_content=REPLACE(@html_content,'Ð','Ð');
 SET @html_content=REPLACE(@html_content,'Ñ','Ñ');
 SET @html_content=REPLACE(@html_content,'Ò','Ò');
 SET @html_content=REPLACE(@html_content,'Ó','Ó');
 SET @html_content=REPLACE(@html_content,'Ô','Ô');
 SET @html_content=REPLACE(@html_content,'Õ','Õ');
 SET @html_content=REPLACE(@html_content,'Ö','Ö');
 SET @html_content=REPLACE(@html_content,'×','×');
 SET @html_content=REPLACE(@html_content,'Ø','Ø');
 SET @html_content=REPLACE(@html_content,'Ù','Ù');
 SET @html_content=REPLACE(@html_content,'Ú','Ú');
 SET @html_content=REPLACE(@html_content,'Û','Û');
 SET @html_content=REPLACE(@html_content,'Ü','Ü');
 SET @html_content=REPLACE(@html_content,'Ý','Ý');
 SET @html_content=REPLACE(@html_content,'Þ','Þ');
 SET @html_content=REPLACE(@html_content,'ß','ß');
 SET @html_content=REPLACE(@html_content,'à','à');
 SET @html_content=REPLACE(@html_content,'á','á');
 SET @html_content=REPLACE(@html_content,'â','â');
 SET @html_content=REPLACE(@html_content,'ã','ã');
 SET @html_content=REPLACE(@html_content,'ä','ä');
 SET @html_content=REPLACE(@html_content,'å','å');
 SET @html_content=REPLACE(@html_content,'æ','æ');
 SET @html_content=REPLACE(@html_content,'ç','ç');
 SET @html_content=REPLACE(@html_content,'è','è');
 SET @html_content=REPLACE(@html_content,'é','é');
 SET @html_content=REPLACE(@html_content,'ê','ê');
 SET @html_content=REPLACE(@html_content,'ë','ë');
 SET @html_content=REPLACE(@html_content,'ì','ì');
 SET @html_content=REPLACE(@html_content,'í','í');
 SET @html_content=REPLACE(@html_content,'î','î');
 SET @html_content=REPLACE(@html_content,'ï','ï');
 SET @html_content=REPLACE(@html_content,'ð','ð');
 SET @html_content=REPLACE(@html_content,'ñ','ñ');
 SET @html_content=REPLACE(@html_content,'ò','ò');
 SET @html_content=REPLACE(@html_content,'ó','ó');
 SET @html_content=REPLACE(@html_content,'ô','ô');
 SET @html_content=REPLACE(@html_content,'õ','õ');
 SET @html_content=REPLACE(@html_content,'ö','ö');
 SET @html_content=REPLACE(@html_content,'÷','÷');
 SET @html_content=REPLACE(@html_content,'ø','ø');
 SET @html_content=REPLACE(@html_content,'ù','ù');
 SET @html_content=REPLACE(@html_content,'ú','ú');
 SET @html_content=REPLACE(@html_content,'û','û');
 SET @html_content=REPLACE(@html_content,'ü','ü');
 SET @html_content=REPLACE(@html_content,'ý','ý');
 SET @html_content=REPLACE(@html_content,'þ','þ');
 SET @html_content=REPLACE(@html_content,'ÿ','ÿ');
 SET @html_content=REPLACE(@html_content,'<o:p></o:p><',' ');
 SET @html_content=REPLACE(@html_content,'&amp;ldquo;','&amp;#8220;');
 SET @html_content=REPLACE(@html_content,'&amp;rdquo;','&amp;#8221;');
 SET @html_content=REPLACE(@html_content,'&amp;rsquo;','&amp;#8217;');
 SET @html_content=REPLACE(@html_content,'&amp;lsquo;','&amp;#8216;');
 SET @html_content=REPLACE(@html_content,'&amp;ndash;','&amp;#8211;');

 RETURN @html_content;
END

UPDATE
 dbo.policies
SET
 [comments] = dbo.replace_html_characters([comments])
WHERE
 comments IS NOT NULL AND
 comments<>'';
Tags: , Category: ICT, Transact-SQL Comments (0)

Changing the seed of a table, i.e. reseed a table

Posted by Ozer Senturk on Tuesday, 9 March, 2010 
DBCC CHECKIDENT('relations', RESEED, 10000000)
Tags: , , Category: ICT, Transact-SQL Comments (0)

Clearing log file contents

Posted by Ozer Senturk on Tuesday, 9 March, 2010 
DUMP TRANSACTION Kuijf WITH NO_LOG
DBCC SHRINKFILE (N'Kuijf_log', EMPTYFILE)
Tags: , , , Category: ICT, Transact-SQL Comments (0)

Toggling the status of a database, i.e. making a database online/offline

Posted by Ozer Senturk on Tuesday, 9 March, 2010 
ALTER DATABASE Kuijf SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE Kuijf SET ONLINE
Tags: , , Category: ICT, Transact-SQL Comments (0)
« Older Entries