Important Scripts for MS SQL DBA

SQL Code:

   SELECT TOP 20
    est.TEXT AS QUERY,
    DB_NAME(est.dbid) AS DATABASE_NAME,   -- Fixed database name retrieval
    SCHEMA_NAME(est.schema_id) AS SCHEMA_NAME,   -- Added schema name
    eqs.execution_count AS EXEC_CNT,
    eqs.max_elapsed_time AS MAX_ELAPSED_TIME,
    ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count, 0), 0) AS AVG_ELAPSED_TIME,
    eqs.creation_time AS CREATION_TIME,
    ISNULL(eqs.execution_count / NULLIF(DATEDIFF(SECOND, eqs.creation_time, GETDATE()), 0), 0) AS EXEC_PER_SECOND,
    eqs.total_physical_reads AS AGG_PHYSICAL_READS
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
ORDER BY eqs.max_elapsed_time DESC;
  

SQL Code:

SELECT 
    DB_NAME(s.database_id) AS DBName,
    COUNT(s.session_id) AS NumberOfConnections,
    s.login_name
FROM 
    sys.dm_exec_sessions s
WHERE 
    s.database_id IS NOT NULL -- To exclude system databases
GROUP BY 
    s.database_id, s.login_name
ORDER BY 
    DB_NAME(s.database_id);
  

SQL Code:

SELECT database_name, backup_start_date, is_copy_only,
first_lsn as 'LSN of full bakup'
FROM msdb..backupset
WHERE database_name = 'CNA'
ORDER BY backup_start_date DESC
GO;
  

SQL Code:

SELECT r.session_id AS [Session_Id]
,r.command AS [command]
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
,GETDATE() AS [Current Time]
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours]
,CONVERT(VARCHAR(1000), (
SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = - 1
THEN 1000
ELSE (r.statement_end_offset - r.statement_start_offset) / 2
END) 'Statement text'
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command like 'RESTORE%'
or command like 'BACKUP%';
  

SQL Code:

DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)

-- Declare cursor for the databases you want to operate on
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name IN ('providerportal') -- List your databases here
  AND state = 0 -- Database is online
  AND is_in_standby = 0 -- Not read-only for log shipping
ORDER BY 1

-- Open the database cursor
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database

-- Loop through each database
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL for the table cursor
    SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR 
                SELECT ''['' + table_schema + ''].['' + table_name + '']'' as tableName 
                FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
                WHERE table_type = ''BASE TABLE'''
    EXEC (@cmd)
    
    -- Open the table cursor
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @Table

    -- Loop through each table and rebuild indexes
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- Dynamic SQL to rebuild all indexes on the table
            SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
            PRINT @cmd -- Uncomment to see the generated commands
            EXEC (@cmd)
        END TRY
        BEGIN CATCH
            -- Error handling block
            PRINT '--- Error for Table: ' + @Table
            PRINT 'Command: ' + @cmd
            PRINT ERROR_MESSAGE()
            PRINT '---'
        END CATCH

        -- Fetch next table
        FETCH NEXT FROM TableCursor INTO @Table
    END

    -- Close and deallocate the table cursor
    CLOSE TableCursor
    DEALLOCATE TableCursor

    -- Fetch next database
    FETCH NEXT FROM DatabaseCursor INTO @Database
END

-- Close and deallocate the database cursor
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
  

SQL Code:

exec sp_updatestats;
  

SQL Code:

SELECT 'ALTER SCHEMA dbo TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'sysadm'
AND (DbObjects.Type IN ('U', 'P', 'V'));
  

SQL Code:

ALTER SCHEMA dbo TRANSFER [sysadm].[TableName];
ALTER SCHEMA dbo TRANSFER [sysadm].[ProcedureName];
ALTER SCHEMA dbo TRANSFER [sysadm].[ViewName];
  

SQL Code:

Grant execute on sp;
GRANT EXEC TO User_name;
  

SQL Code:

DBCC FREEPROCCACHE;
GO

USE tempdb;
GO

-- Shrink tempDB data file
DBCC SHRINKFILE ('tempdev', 1);
GO

-- Shrink tempdb log file
DBCC SHRINKFILE ('templog', 1);
GO
  

SQL Code:

Use [DB_Name]
Go
Set NOCOUNT ON
Go
--Script 1
Print Convert(Varchar(50),'USE '+DB_Name())
SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
'U'
,'S'
)
AND NAME NOT IN (
'dbo'
,'guest'
,'sys'
,'INFORMATION_SCHEMA'
)
  

SQL Code:

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id;
  

SQL Code:

if object_id('tempdb..#dbsize') is not null
drop table #dbsize;
GO

create table #dbsize (
    database_name sysname,
    current_size int,  -- Fixed typo here
    max_size int,
    is_unlimited int,
    current_log_size int,
    max_log_size int,
    is_log_unlimited int
);

exec sp_msforeachdb '
insert #dbsize
select ''?'',
sum(case when type_desc = ''LOG'' then 0 else size end),
sum(case when type_desc = ''LOG'' then 0 else max_size end),
min(case when type_desc = ''LOG'' then 0 else max_size end),
sum(case when type_desc = ''LOG'' then size else 0 end),
sum(case when type_desc = ''LOG'' then max_size else 0 end),
min(case when type_desc = ''LOG'' then max_size else 0 end)
from [?].sys.database_files';

select database_name as DatabaseName,
       current_size / 128.0 as CurrentDBSize,
       case when is_unlimited = -1 then 'unlimited' else format(max_size/128.0, '0.##') end as MaxDBSize,
       current_log_size / 128.0 as CurrentLogSize,
       case when is_log_unlimited = -1 then 'unlimited' else format(max_log_size / 128.0, '0.##') end as MaxLogSize
from #dbsize
order by database_name;
GO

if object_id('tempdb..#dbsize') is not null
drop table #dbsize;
GO;
  

SQL Code:

Use Master
BACKUP LOG User_db TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR;

Use User_db
dbcc loginfo;

DBCC SHRINKFILE (user_db_log, EMPTYFILE);
  

SQL Code:

-- Declare variables
DECLARE @dbname VARCHAR(50)  
DECLARE @statement NVARCHAR(MAX)

-- Declare cursor
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD
FOR   
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb')
AND state_desc='online'

-- Create a table to store results
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserPermissions]') AND type in (N'U'))
BEGIN
    CREATE TABLE dbo.UserPermissions (
        ServerName VARCHAR(50),
        dbname VARCHAR(50),
        UserName VARCHAR(50),
        TypeOfLogin VARCHAR(50),
        PermissionLevel VARCHAR(50),
        TypeOfRole VARCHAR(50)
    )
END

-- Open cursor and loop through databases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    BEGIN TRY
        -- Construct dynamic SQL
        SELECT @statement = 'USE ' + @dbname + ';' + 
            'SELECT
            ServerName = @@servername, 
            dbname = db_name(db_id()), 
            p.name AS UserName, 
            p.type_desc AS TypeOfLogin, 
            pp.name AS PermissionLevel, 
            pp.type_desc AS TypeOfRole
        FROM sys.database_role_members roles
        JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
        JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
        WHERE p.name = ''Test_User'';'  -- Change the user name

        -- Execute dynamic SQL and insert into the table
        INSERT INTO dbo.UserPermissions
        EXEC sp_executesql @statement;

    END TRY
    BEGIN CATCH
        -- Error handling, just in case an error occurs for any database
        PRINT 'Error occurred for database: ' + @dbname;
    END CATCH

    -- Fetch next database
    FETCH NEXT FROM db_cursor INTO @dbname  
END  

-- Close and deallocate cursor
CLOSE db_cursor  
DEALLOCATE db_cursor;
  

SQL Code:

SELECT
    job_id
    ,name
    ,enabled
    ,date_created
    ,date_modified
FROM msdb.dbo.sysjobs
ORDER BY date_created;
  

SQL Code:

ALTER DATABASE User_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (User_db, REPAIR_ALLOW_DATA_LOSS);
GO

ALTER DATABASE User_db SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
  

SQL Code:

SELECT
    DSR.registry_key,
    DSR.value_name,
    DSR.value_data
FROM sys.dm_server_registry AS DSR
WHERE
    DSR.registry_key LIKE N'%MSSQLServer\\Parameters';
  

CMD Code:

bcp User_db.dbo.Table_name out "L:\DBA_Backup\Table_name.txt" -c -T
bcp User_db.dbo.Table_name in "C:\fruit\inventory.txt" -c -T
  

SQL Code:

DECLARE @QueryString NVARCHAR(MAX);

-- Construct the dynamic SQL query
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
    + 'SELECT '
    + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
    + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
    , COUNT(*) AS [RowCount] FROM '
    + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
    + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
    sOBJ.type = 'U' -- User tables
    AND sOBJ.is_ms_shipped = 0x0 -- Exclude system tables
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name;

-- Execute the dynamic SQL
BEGIN TRY
    EXEC sp_executesql @QueryString;
END TRY
BEGIN CATCH
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;
GO
  

SQL Code:

SELECT TOP 10 
    s.session_id,                    -- Session ID of the user session
    r.status,                         -- Status of the request (running, suspended, etc.)
    r.cpu_time,                       -- CPU time consumed by the request in milliseconds
    r.logical_reads,                  -- Number of logical reads (pages read from cache)
    r.reads,                          -- Number of physical reads (pages read from disk)
    r.writes,                         -- Number of writes performed by the request
    r.total_elapsed_time / (1000 * 60) 'Elaps M', -- Total elapsed time in minutes
    SUBSTRING(
        st.TEXT, 
        (r.statement_start_offset / 2) + 1, 
        ((CASE r.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.TEXT) 
            ELSE r.statement_end_offset 
        END - r.statement_start_offset) / 2) + 1
    ) AS statement_text,              -- The specific SQL statement being executed (parsed from the SQL text)
    COALESCE(
        QUOTENAME(DB_NAME(st.dbid)) + N'.' + 
        QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + 
        QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), 
        ''
    ) AS command_text,               -- Command text (in fully qualified format if available)
    r.command,                        -- The command being executed (e.g., SELECT, UPDATE, etc.)
    s.login_name,                     -- Login name of the user
    s.host_name,                      -- Hostname of the machine from which the user is connected
    s.program_name,                   -- Program or application name
    s.last_request_end_time,          -- The time when the last request was completed
    s.login_time,                     -- The time when the user logged into SQL Server
    r.open_transaction_count         -- Number of open transactions for the session
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID             -- Excludes the current session (to avoid the query being listed in results)
ORDER BY r.cpu_time DESC;
  

SQL Code:

-- Use the following query to identify queries with high CPU usage that contain at least one missing index in the query plan.

-- Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,  -- Convert CPU time to milliseconds
    q.[text],                                              -- The query text
    p.query_plan,                                          -- The query execution plan
    qs_cpu.execution_count,                                -- Number of times the query was executed
    q.dbid,                                                -- Database ID the query was executed on
    q.objectid,                                            -- Object ID (could be a table or index)
    q.encrypted AS text_encrypted                          -- Whether the query text is encrypted
FROM
(
    -- Subquery to get the top 500 queries based on total CPU time
    SELECT TOP 500
        qs.plan_handle,
        qs.total_worker_time,
        qs.execution_count
    FROM sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time DESC  -- Order by highest CPU time usage
) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q        -- Get the SQL text for the query plan
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p         -- Get the execution plan for the query
WHERE p.query_plan.exist('declare namespace
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:MissingIndexes')=1  -- Check if the execution plan contains MissingIndexes

SQL Code:

-- Query 1: System-level physical memory details
SELECT 
    total_physical_memory_kb / 1024 AS [Total Physical Memory in MB],
    available_physical_memory_kb / 1024 AS [Physical Memory Available in MB],
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- Query 2: Process-level memory details for SQL Server
SELECT 
    physical_memory_in_use_kb / 1024 AS [Physical Memory Used in MB],
    process_physical_memory_low AS [Physical Memory Low],
    process_virtual_memory_low AS [Virtual Memory Low]
FROM sys.dm_os_process_memory;

-- Query 3: SQL Server memory commit details
SELECT 
    committed_kb / 1024 AS [SQL Server Committed Memory in MB],
    committed_target_kb / 1024 AS [SQL Server Target Committed Memory in MB]
FROM sys.dm_os_sys_info;
  

SQL Code:

DECLARE @svrName VARCHAR(255)
DECLARE @sql VARCHAR(400)

-- By default, it will take the current server name, or we can set the server name manually
SET @svrName = @@SERVERNAME
SET @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

PRINT @sql

-- Creating a temporary table
CREATE TABLE #output
(
    line VARCHAR(255)
)

-- Inserting disk name, total space, and free space value into the temporary table
INSERT INTO #output
EXEC xp_cmdshell @sql

-- Script to retrieve the values in GB from PS Script output
SELECT 
    @@SERVERNAME AS servername,
    RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) AS drivename,
    ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line) + 1, CHARINDEX('%', line) - CHARINDEX('|', line) - 1)) AS FLOAT) / 1024, 0) AS capacityGB,
    ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line) + 1, CHARINDEX('*', line) - CHARINDEX('%', line) - 1)) AS FLOAT) / 1024, 0) AS freespaceGB,
    ROUND(100 * (ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line) + 1, CHARINDEX('*', line) - CHARINDEX('%', line) - 1)) AS FLOAT) / 1024, 0)) /
                 (ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line) + 1, CHARINDEX('%', line) - CHARINDEX('|', line) - 1)) AS FLOAT) / 1024, 0))), 0) AS percentfree
FROM #output
WHERE line LIKE '[A-Z][:]%'
ORDER BY drivename

-- Dropping the temporary table
DROP TABLE #output

GO

-- Uncomment and configure as needed for enabling xp_cmdshell
-- sp_configure 'xp_cmdshell', 1
-- GO

-- Reconfigure with override
-- GO

-- sp_configure 'show advanced options', 1
-- GO

-- Reconfigure with override
-- GO
  

SQL Code:

SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY cp.usecounts DESC;
  

SQL Code:

WITH LoginProperties AS (
    SELECT
        SL.name AS LoginName,
        LOGINPROPERTY(SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime,
        LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration,
        LOGINPROPERTY(SL.name, 'IsExpired') AS IsExpired,
        LOGINPROPERTY(SL.name, 'IsMustChange') AS IsMustChange,
        LOGINPROPERTY(SL.name, 'IsLocked') AS IsLocked,
        LOGINPROPERTY(SL.name, 'LockoutTime') AS LockoutTime,
        LOGINPROPERTY(SL.name, 'BadPasswordCount') AS BadPasswordCount,
        LOGINPROPERTY(SL.name, 'BadPasswordTime') AS BadPasswordTime,
        LOGINPROPERTY(SL.name, 'HistoryLength') AS HistoryLength,
        SL.is_policy_checked AS IsPolicyChecked
    FROM sys.sql_logins AS SL
    WHERE SL.is_expiration_checked = 1
)
SELECT 
    LoginName,
    PasswordLastSetTime,
    DaysUntilExpiration,
    DATEADD(dd, CONVERT(int, DaysUntilExpiration), CONVERT(datetime, PasswordLastSetTime)) AS PasswordExpiration,
    IsPolicyChecked,
    IsExpired,
    IsMustChange,
    IsLocked,
    LockoutTime,
    BadPasswordCount,
    BadPasswordTime,
    HistoryLength
FROM LoginProperties
ORDER BY PasswordLastSetTime DESC;
  

SQL Code:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio';
  

SQL Code:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy';
  

SQL Code:

-- Check for blocking requests
SELECT session_id, blocking_session_id, status, wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

-- Check for waiting tasks and blocking session details
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0;
GO

-- Get session details for session_id 63
SELECT session_id, host_name, program_name, login_name, status, logical_reads
FROM sys.dm_exec_sessions
WHERE session_id = 63;
GO

-- Get request details for session_id 63
SELECT session_id, status, command, sql_handle, database_id, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = 63;
GO

-- Retrieve SQL text for a specific sql_handle (replace xxxx with actual sql_handle)
SELECT * 
FROM sys.dm_exec_sql_text('xxxx');  -- Replace 'xxxx' with the actual sql_handle;
  

SQL Code:

exec sp_help_revlogin;
  

SQL Code:

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
' 

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 ...),
     E02(N) AS (SELECT 1 FROM E01 a, E01 b),  
     E04(N) AS (SELECT 1 FROM E02 a, E02 b),
     E08(N) AS (SELECT 1 FROM E04 a, E04 b),
     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)
)
select   
  row_number() over (order by ItemOrder) as ItemID,   
  Item   
from ItemSplit;