Script DBMail Profiles

User Lowell posted a script to script out DBMail’s profile at this thread:
http://www.sqlservercentral.com/Forums/Topic982618-391-1.aspx

I include the script here for future reference:

USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)        
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS profile
  '
  +
  '
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    --CREATE Account [' + a.name + ']
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ',
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ',
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ',
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ',
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ',
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ',
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ',
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ',
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ', 
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + ';
  END --IF EXISTS  account
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''') 
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  ' 
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id 
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows  
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows  
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows  
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows  
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,  
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),  
       ItemSplit(  
                 ItemOrder,  
                 Item  
                ) as (  
                      SELECT N,  
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))  
                      FROM Tally  
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)  
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf  
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter  
                     )  
  select  
    row_number() over (order by ItemOrder) as ItemID,  
    Item  
  from ItemSplit  
 

 

Find Broken Loop COnnection from Two Columns

 
 Create table Sample
 (
  Rec_Id Int Not null,
  Name varchar(30) null,
  ID1 varchar(10) null,
  ID2 varchar(10) null,
 CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED 
(
  [Rec_Id] ASC
 ))
  

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
 
;with mycte as
(select Rec_Id,name, id1, id2  
,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
 
 
from sample )
, mycte1 as(
select m0.Rec_id,m0.name, m0.id1,  m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2
, Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
from mycte m0 
Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
)

Select Rec_id,name, id1,id2 
from mycte1
WHERE rn2=1 AND cnt>0
Order by Rec_id

/*
Rec_id	name	id1	id2
6	B	B5Y13	NULL
10	C	C6JPQ	C5Z19
13	D	D5Z18	D5Z17

*/



drop table sample

 

Convert String Type British Datetime Data (DD/MM/YYYY hh:mm:ss ) in Varchar/NVarchar to Standard Datetime with T-SQL

create table test (strdt varchar(255))
--British datetime in string
insert into test values 
('13/12/2013 13:33:00 +00:00')
,('01/01/2013 13:33:00 +00:00')
,('28/02/2013 13:33:00 +00:00')
,('31/10/2013 13:33:00 +00:00')
Select 
--string manipulation and cast to datetime
Cast(substring(strdt,7,4)+substring(strdt,4,2)+substring(strdt,1,2) +' '+ substring(strdt,12,8) as datetime),

--Convert string to British datetime first and then convert the British datetime to standard
  convert(datetime, convert(datetime, Left(strdt,20), 103),112)
   
FROM test

--Or
Select   CONVERT(datetime2(3),strdt, 103) AS DateValue
,try_convert(datetime2(3),strdt,103 )
,Try_PARSE(strdt as datetime2(3) using 'en-GB')

FROM test


--clean up
drop table test

 


 

Strip non-numeric character in a column

create table test (t_id int primary key, col varchar(50))
INSERT INTO test
VALUES      (1,'462'),
            (2,'E08R'),
            (3,'E07'),
            (4,'E09'),
            (5,'E06'),
            (6,'360'),
            (7,'E04'),
            (8,'E11R'),
            (9,'E02R') 

 

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 
processTable as (
    select col,Cast(col1 as int) col1   from test
        cross apply (
            select (select C + ''
            from (select n, substring(Col, n, 1) C 
			FROM Num1  where n<=datalength(Col)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)

		)

SELECT col,col1 FROM processTable
 
 --http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


--Another option
;with
mycte1 as
(select  col, REPLACE(col,SUBSTRING(col,PATINDEX('%[^0-9]%',col),1),'') col1 from test )
,mycte2 as (
select col, REPLACE(col1,SUBSTRING(col1,PATINDEX('%[^0-9]%',col1),1),'') col2 from mycte1)
,mycte3 as (
select col, REPLACE(col2,SUBSTRING(col2,PATINDEX('%[^0-9]%',col2),1),'') col3 from mycte2)
,mycte4 as (
select col, REPLACE(col3,SUBSTRING(col3,PATINDEX('%[^0-9]%',col3),1),'') col4 from mycte3)

Select col, Cast(col4 as int) from mycte4

drop table test

--Another example
create table test (COLUMN_A varchar(100))
INSERT INTO test VALUES
('AAAA AAAAAAA CO A.A. # 4030'),      	
('BBBBBB BBB BBB CO. #  4260.'),  	
('CCC CCCCC #3001'),
('CCC CCCCC 3001')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 

processTable as (

select Substring(COLUMN_A, 0,Charindex('#',  COLUMN_A)) COLUMN_A , Cast(col1 as int) COLUMN_B   from test
        cross apply (
            select (select C + ''
            from (select n, substring(COLUMN_A, n, 1) C 
            FROM Num1  where n<=datalength(COLUMN_A)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)
 
        )

 Select * from processTable

 Drop table test

 

Running Total (deduct value) (T-SQL)

DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price int)
 
INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 1000),(2, 'B',500),(3, 'C', 200),(4, 'E', 300)
 

 ;with mycte1 as (
select ProductID, ProductName, row_number() Over(Order by ProductID)  rn,Price
 
from @Products)
 
, mycte2 AS
(
 SELECT ProductID, ProductName, rn, Price
 FROM mycte1
 WHERE rn = 1
 UNION ALL
 SELECT n.ProductID, n.ProductName, n.rn,  (m.Price - n.Price) as price 
 FROM mycte2 m INNER JOIN mycte1 n
 ON n.rn = m.rn + 1
)
SELECT ProductID, ProductName,Price FROM mycte2
 ORDER BY ProductID
 
--OPTION (MAXRECURSION 0)
;



 

Find Capital Letter Position with PatIndex (T-SQL)

--1.Find first Capital letter position:
SELECT patindex('%[A-Z]%'  ,'where is the Capital Letter'  COLLATE Latin1_General_BIN)

--Or use different collation:
SELECT patindex('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%','where is the Capital Letter'  COLLATE SQL_Latin1_General_CP1_CS_AS)


--2.Find all capital letter position
-- We can use  either  collation: Latin1_General_BIN or SQL_Latin1_General_CP1_CS_AS to change case sensitive level

;with mycte as (
Select 65 as i, char(65) as Capitalletter
Union all
Select i+1 i, char(i+1) from mycte
WHERE i<90)

SELECT Capitalletter, patindex('%'+Capitalletter +'%','where is the Capital Letter' COLLATE SQL_Latin1_General_CP1_CS_AS)
FROM mycte 
/*
Capitalletter	pos
A	0
B	0
C	14
D	0
E	0
F	0
G	0
H	0
I	0
J	0
K	0
L	22
M	0
N	0
O	0
P	0
Q	0
R	0
S	0
T	0
U	0
V	0
W	0
X	0
Y	0
Z	0

*/


--http://www.techonthenet.com/ascii/chart.php
--http://technet.microsoft.com/en-us/library/ms187323.aspx



 

Combine Columns to Row (Two columns) –XML Path

--DDLs for sample table and data
Create table Employee(ID int,Department int,Name Varchar(50) )
Insert into Employee values (1,1,'j.Doe')

Create table Executive(ID int,Department int,Name Varchar(50) )
Insert into Executive values (1,1,'Doe')
Insert into Executive values (2,1,'David')
 
Create table Supervisor(ID int,Department int,Name Varchar(50) )
Insert into Supervisor values (1,1,'Doe')
Insert into Supervisor values (2,1,'Jane') 

Create table Departments(ID int,DepartName Varchar(50) )
insert into Departments values(1,'Marketing')


;with mycte as (
Select em.ID,em.Name as EmName,d.DepartName, ex.Name as ExName,s.Name as SuperName 
FROM Employee em 
INNER JOIN Executive  ex on em.Department=ex.Department 
INNER JOIN Supervisor s On s.Department=em.Department 
INNER JOIN Departments d on em.Department=d.ID
)


SELECT Distinct ID, EmName,DepartName
,SupervisorNames = STUFF((
SELECT distinct ',' + SuperName
 FROM mycte m
 WHERE m.ID = m1.ID AND m.EmName = m1.EmName AND m.DepartName = m1.DepartName

 FOR XML PATH('')), 1, 1, '')


, ExecutiveNames = STUFF((
SELECT distinct ',' + ExName
 FROM mycte m
 WHERE m.ID = m1.ID AND m.EmName = m1.EmName AND m.DepartName = m1.DepartName

 FOR XML PATH('')), 1, 1, '') 
 FROM mycte m1


--Clean up
drop table Employee,Executive,Supervisor,Departments
 
 
 


 

Dynamic Pivot With Parameter–(T-SQL)

If object_id('table1','U') is not null
drop table table1
 
create table table1 (PID int, Pname varchar(50), PartId int)
insert into table1 Values(0,'Length',1), (1,'Breadth',1),(2,'Height',1),(0,'Area',2),(1,'Volume',2)     

If object_id('table2','U') is not null
drop table table2

create table table2 (SampleID  int , PID int, Pvalue varchar(10), PartId int)
insert into table2  values (0,0,'10',1), (0,1,'10',1), (0,2,'fail',1), (1,0,'20',1),(1,1,'fail',1),(1,2,'fail',1), (0,0,'10',2), (0,1,'10',2)


--Assign your partid value here
 Declare @partId int=1

--SET @partId =2

 
DECLARE @col AS NVARCHAR(max)=''
DECLARE @sql AS NVARCHAR(max)
 
SELECT @col=stuff( (SELECT ',['+ PName  +']'
FROM (Select distinct top 100 Percent  PName, t2.Pid from table2 t2 
inner join table1 t1 on t1.PID =t2.PID and t1.PartId=t2.PartId  Where t2.PartId=@PartId order by t2.Pid) p2
 
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
--print @col
 
SET @sql='SELECT [SampleID], ' + @col
+ ' FROM (
Select  SampleId, PName, PValue from table2 t2 
inner join table1 t1 on t1.PID =t2.PID and t1.PartId=t2.PartId  
Where t2.PartId='+ Cast(@PartId as Nvarchar(50)) +' ) src
PIVOT (MAX(PValue) FOR [PName] IN ('+ @col + ')) AS pvt'

--print @sql
--EXEC(@sql)
EXEC sp_executesql @sql