Dynamics Tips: SQL Server

1) SQL Procedure of search phrase on all Database

2) Backup All Database

--------------------------------------------------------  
-- Backup All Database     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com     
--------------------------------------------------------    
DECLARE @name VARCHAR(50) -- database name    
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name   
SET @path = 'C:\Backup\'  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
DECLARE db_cursor CURSOR   
FOR   
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')   
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0      
   BEGIN          
     SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
     BACKUP DATABASE @name TO DISK = @fileName         
     FETCH NEXT FROM db_cursor INTO @name       
   END    
CLOSE db_cursor    
DEALLOCATE db_cursor

3) Cleanup Log Files in MSDB Database

----------------------------------------------------------------------------
---- Cleanup log files in MSDB Database
---- 1. sysmaintplan_log
---- 2. sysmaintplan_logdetail
---- Author : Venugopal G A
----------------------------------------------------------------------------

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];
ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

truncate table msdb.dbo.sysmaintplan_logdetail;
truncate table msdb.dbo.sysmaintplan_log;

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])
REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])
REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

4) Stored Procedure to Search On ALL DB

/*************************************************************************/
/*         SQL Procedure of search phrase on all Database                */
/*            Is developed by Venugopal G A, MCDBA                       */
/*                                                                       */
/*            venuasg@gmail.com,  http://dynegp.blogspot.com             */ 
/*************************************************************************/
if exists 
(select * from sysobjects where id = object_id('dbo.spSearchOnAllDB') )
 drop procedure dbo.spSearchOnAllDB
GO

CREATE PROCEDURE spSearchOnAllDB @phrase varchar(8000), @OutFullRecords bit=0 AS

/*
   How to Run Sample 
      exec  spSearchOnAllDB 'Sugar%'
      exec  spSearchOnAllDB '%soft%'
      exec  spSearchOnAllDB '_5234_57%', 1
      exec  spSearchOnAllDB M_cro_oft
*/

declare @sql varchar(8000)
declare @tbl varchar(128) 
declare @col varchar(128)
declare @id_present bit

declare @is_char_phrase bit
declare @min_len int
declare @loop_idx int
declare @loop_chr char(1)

set nocount on

if IsNull(@phrase,'')=''
begin
  raiserror('Phrase is absent',16,-1)
  return
end

select @loop_idx=1, @is_char_phrase=0, @min_len=0 

while @loop_idx<=LEN(@phrase)
begin
  set @loop_chr=SUBSTRING(@phrase,@loop_idx,1)
  if @loop_chr not in ('%','_') set @min_len=@min_len+1
  if @is_char_phrase=0 and @loop_chr not in ('%','_','0','1','2','3','4','5','6','7','8','9','.')  
       set @is_char_phrase=1
  set @loop_idx=@loop_idx+1
end 

create table #tbl_res 
                (TableName varchar(128) not NULL,
                 ColumnName varchar(128) not NULL,
                 Id int NULL,
                 ColumnValue varchar(7500) not NULL)


create table #tbl_res2 
                (TableName varchar(128) not NULL,
                 ColumnName varchar(128) not NULL,
                 Id int NULL,
                 ColumnValue varchar(7500) not NULL)



declare CRR cursor local fast_forward for
  select t.name, c.name, 1 
  from sysobjects t, syscolumns c 
  where t.type='U'
  and c.id=t.id
  and c.status&0x80=0 -- Not IDENTITY
  and exists (select * from syscolumns c2 where t.id=c2.id and c2.status&0x80=0x80 and c2.xtype in (48,52,56))
  and (   (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and c.length>=@min_len) -- char only
       or (@is_char_phrase=0 and c.xtype not in (34,165,173,189,61,58,36))) -- char and numeric
  union select t.name, c.name, 0 
  from sysobjects t, syscolumns c 
  where t.type='U'
  and c.id=t.id
  and not exists (select * from syscolumns c2 where t.id=c2.id and c2.status&0x80=0x80 and c2.xtype in (48,52,56))
  and (   (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and c.length>=@min_len) -- char only
       or (@is_char_phrase=0 and c.xtype not in (34,165,173,189,61,58,36))) -- char and numeric
  order by 1,2
open CRR
fetch CRR into @tbl, @col, @id_present
while @@FETCH_STATUS=0
begin

  if @OutFullRecords=0
  begin
    set @sql='insert into #tbl_res (TableName,ColumnName,Id,ColumnValue) '
            +'select '+char(39)+@tbl+char(39)+', '
                      +char(39)+@col+char(39)+', '
    if @id_present=1 set @sql=@sql+'IDENTITYCOL, '
                else set @sql=@sql+'NULL, ' 
    set @sql=@sql+'convert(varchar(7500),'+@col+') '
                 +'from '+@tbl+' (nolock) '
                 +'where convert(varchar(8000),'+@col+') like '+char(39)+@phrase+char(39)
  end
  if @OutFullRecords=1
  begin
    set @sql='if exists (select * from '+@tbl+' (nolock) '
                       +'where convert(varchar(8000),'+@col+') like '+char(39)+@phrase+char(39)+') '
            +'select '+char(39)+@tbl+char(39)+' TableName, '+char(39)+@col+char(39)+' ColumnName, * '
            +'from '+@tbl+' (nolock) where convert(varchar(8000),'+@col+') like '+char(39)+@phrase+char(39)
  end
  exec(@sql)
  fetch CRR into @tbl, @col, @id_present
end
close CRR
deallocate CRR
	

if @OutFullRecords=0
begin
  -- For the clients supporting new types:
  --exec('select * from #tbl_res order by 1,2,3')

  -- For the clients who are not supporting new types:
	
INSERT #tbl_res2
select TableName, ColumnName, Id, convert(varchar(255),ColumnValue) ColumnValue from #tbl_res

 /** exec('select TableName, ColumnName, Id, convert(varchar(255),ColumnValue) ColumnValue from #tbl_res order by 1,2,3')**/
end
  
drop table #tbl_res 

/***Select Statement to show tables***/

select TableName,ColumnName,ColumnValue from #tbl_res2 group by TableName,ColumnName,ColumnValue
	order by TableName

GRANT EXECUTE ON dbo.spSearchOnAllDB TO DYNGRP

truncate table #tbl_res2

drop table #tbl_res2

5) SQL Function to retrive only Numbers from a given string

--------------------------------------------------------  
-- SQL Function to retrive only Number     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com     
--------------------------------------------------------    
CREATE FUNCTION dbo.fnNumbersOnly
   (  @InParam   varchar(500) )
   RETURNS varchar(500)
AS
   BEGIN
      IF patindex( '%[^0-9]%', @InParam ) > 0
         BEGIN
            WHILE patindex( '%[^0-9]%', @InParam ) > 0
               BEGIN
                  SET @InParam = Stuff( @InParam, patindex( '%[^0-9]%', @InParam), 1, '' )  
               END 
         END
      RETURN @InParam
   END
GO

-- Test
DECLARE @TestParam varchar(50)
SET @TestParam = 'ab3452SDF094c&h*¤,1r340.95849%eew#y/'
SELECT dbo.fnNumbersOnly( @TestParam )

6) SP to find Database Size Allocation

--------------------------------------------------------  
-- SP to find Database Size Allocation     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com     
-------------------------------------------------------- 

--DROP PROCEDURE [Table_Size]
USE EC1F
GO 
CREATE PROCEDURE Database_Size @Granularity VARCHAR(10) = NULL, @Database_Name sysname = NULL AS 
DECLARE @SQL VARCHAR(5000) 

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '##Results')  
   BEGIN  
       DROP TABLE ##Results  
   END 
    
CREATE TABLE ##Results ([Database Name] sysname, [File Name] sysname, [Physical Name] NVARCHAR(260),  
   [File Type] VARCHAR(4), [Total Size in Mb] INT, [Available Space in Mb] INT, [Growth Units] VARCHAR(15), [Max File Size in Mb] INT) 

SELECT @SQL =  
'USE ? INSERT INTO ##Results([Database Name], [File Name], [Physical Name],  
[File Type], [Total Size in Mb], [Available Space in Mb],  
[Growth Units], [Max File Size in Mb])  
SELECT DB_NAME(), 
[name] AS [File Name],  
physical_name AS [Physical Name],  
[File Type] =  
CASE type 
WHEN 0 THEN ''Data'''  
+ 
           'WHEN 1 THEN ''Log''' 
+ 
       'END, 
[Total Size in Mb] = 
CASE ceiling([size]/128)  
WHEN 0 THEN 1 
ELSE ceiling([size]/128) 
END, 
[Available Space in Mb] =  
CASE ceiling([size]/128) 
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) 
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) 
END, 
[Growth Units]  =  
CASE [is_percent_growth]  
WHEN 1 THEN CAST(growth AS varchar(20)) + ''%''' 
+ 
           'ELSE CAST(growth AS varchar(20)) + ''Mb''' 
+ 
       'END, 
[Max File Size in Mb] =  
CASE [max_size] 
WHEN -1 THEN NULL 
WHEN 268435456 THEN NULL 
ELSE [max_size] 
END 
FROM sys.database_files 
ORDER BY [File Type], [file_id]' 

--Print the command to be issued against all databases 
PRINT @SQL 

--Run the command against each database 
EXEC sp_MSforeachdb @SQL 

--UPDATE ##Results SET [Free Space %] = [Available Space in Mb]/[Total Size in Mb] * 100 

--Return the Results 
--If @Database_Name is NULL: 
IF @Database_Name IS NULL 
   BEGIN 
       IF @Granularity = 'Database' 
           BEGIN 
               SELECT  
                   T.[Database Name], 
                   T.[Total Size in Mb] AS [DB Size (Mb)], 
                   T.[Available Space in Mb] AS [DB Free (Mb)], 
                   T.[Consumed Space in Mb] AS [DB Used (Mb)], 
                   D.[Total Size in Mb] AS [Data Size (Mb)], 
                   D.[Available Space in Mb] AS [Data Free (Mb)], 
                   D.[Consumed Space in Mb] AS [Data Used (Mb)], 
                   CEILING(CAST(D.[Available Space in Mb] AS decimal(5,1)) / D.[Total Size in Mb]*100) AS [Data Free %], 
                   L.[Total Size in Mb] AS [Log Size (Mb)], 
                   L.[Available Space in Mb] AS [Log Free (Mb)], 
                   L.[Consumed Space in Mb] AS [Log Used (Mb)], 
                   CEILING(CAST(L.[Available Space in Mb] AS decimal(5,1)) / L.[Total Size in Mb]*100) AS [Log  Free %] 
               FROM  
                   ( 
                   SELECT [Database Name], 
                       SUM([Total Size in Mb]) AS [Total Size in Mb], 
                       SUM([Available Space in Mb]) AS [Available Space in Mb], 
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]  
                   FROM ##Results 
                   GROUP BY [Database Name] 
                   ) AS T 
                   INNER JOIN  
                   ( 
                   SELECT [Database Name], 
                       SUM([Total Size in Mb]) AS [Total Size in Mb], 
                       SUM([Available Space in Mb]) AS [Available Space in Mb], 
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]  
                   FROM ##Results 
                   WHERE ##Results.[File Type] = 'Data' 
                   GROUP BY [Database Name] 
                   ) AS D ON T.[Database Name] = D.[Database Name] 
                   INNER JOIN 
                   ( 
                   SELECT [Database Name], 
                       SUM([Total Size in Mb]) AS [Total Size in Mb], 
                       SUM([Available Space in Mb]) AS [Available Space in Mb], 
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]  
                   FROM ##Results 
                   WHERE ##Results.[File Type] = 'Log' 
                   GROUP BY [Database Name] 
                   ) AS L ON T.[Database Name] = L.[Database Name] 
               ORDER BY D.[Database Name] 
           END 
   ELSE 
       BEGIN 
           SELECT [Database Name], 
               [File Name], 
               [Physical Name], 
               [File Type], 
               [Total Size in Mb] AS [DB Size (Mb)], 
               [Available Space in Mb] AS [DB Free (Mb)], 
               CEILING(CAST([Available Space in Mb] AS decimal(5,1)) / [Total Size in Mb]*100) AS [Free Space %], 
               [Growth Units], 
               [Max File Size in Mb] AS [Grow Max Size (Mb)]  
           FROM ##Results  
       END 
   END 

--Return the Results 
--If @Database_Name is provided 
ELSE 
   BEGIN 
       IF @Granularity = 'Database' 
           BEGIN 
               SELECT  
                   T.[Database Name], 
                   T.[Total Size in Mb] AS [DB Size (Mb)], 
                   T.[Available Space in Mb] AS [DB Free (Mb)], 
                   T.[Consumed Space in Mb] AS [DB Used (Mb)], 
                   D.[Total Size in Mb] AS [Data Size (Mb)], 
                   D.[Available Space in Mb] AS [Data Free (Mb)], 
                   D.[Consumed Space in Mb] AS [Data Used (Mb)], 
                   CEILING(CAST(D.[Available Space in Mb] AS decimal(5,1)) / D.[Total Size in Mb]*100) AS [Data Free %], 
                   L.[Total Size in Mb] AS [Log Size (Mb)], 
                   L.[Available Space in Mb] AS [Log Free (Mb)], 
                   L.[Consumed Space in Mb] AS [Log Used (Mb)], 
                   CEILING(CAST(L.[Available Space in Mb] AS decimal(5,1)) / L.[Total Size in Mb]*100) AS [Log  Free %] 
               FROM  
                   ( 
                   SELECT [Database Name], 
                       SUM([Total Size in Mb]) AS [Total Size in Mb], 
                       SUM([Available Space in Mb]) AS [Available Space in Mb], 
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]  
                   FROM ##Results 
                   WHERE [Database Name] = @Database_Name 
                   GROUP BY [Database Name] 
                   ) AS T 
                   INNER JOIN  
                   ( 
                   SELECT [Database Name], 
                       SUM([Total Size in Mb]) AS [Total Size in Mb], 
                       SUM([Available Space in Mb]) AS [Available Space in Mb], 
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]  
                   FROM ##Results 
                   WHERE ##Results.[File Type] = 'Data' 
                       AND [Database Name] = @Database_Name 
                   GROUP BY [Database Name] 
                   ) AS D ON T.[Database Name] = D.[Database Name] 
                   INNER JOIN 
                   ( 
                   SELECT [Database Name], 
                       SUM([Total Size in Mb]) AS [Total Size in Mb], 
                       SUM([Available Space in Mb]) AS [Available Space in Mb], 
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]  
                   FROM ##Results 
                   WHERE ##Results.[File Type] = 'Log' 
                       AND [Database Name] = @Database_Name 
                   GROUP BY [Database Name] 
                   ) AS L ON T.[Database Name] = L.[Database Name] 
               ORDER BY D.[Database Name] 
           END 
       ELSE 
           BEGIN 
               SELECT [Database Name], 
                       [File Name], 
                      [Physical Name], 
                      [File Type], 
                      [Total Size in Mb] AS [DB Size (Mb)], 
                      [Available Space in Mb] AS [DB Free (Mb)], 
                      CEILING(CAST([Available Space in Mb] AS decimal(5,1)) / [Total Size in Mb]*100) AS [Free Space %], 
                      [Growth Units], 
                      [Max File Size in Mb] AS [Grow Max Size (Mb)]  
               FROM ##Results  
               WHERE [Database Name] = @Database_Name 
           END 
   END 
DROP TABLE ##Results

-- exec database_size

Pages

SQL Server

Blogger templates