Wrapper scripts

SQL Server 2008 Wrapper script to get a detailed list of server configurations.

Print 'Server Name,Server Type,Server Version, Edition , service pack and platform

'
set nocount on
SELECT @@Servername AS ServerName ,
+ ' '+ CASE
WHEN Substring(@@version,len(@@version)-4,4)='(VM)' THEN 'VM'
ELSE 'PHYSICAL'
END AS 'Server Type',
@@version AS 'Server Version, Edition , service pack and platform'

Print '

'
Print'Quick view of SQL Server Details

'
Set Nocount on
--Declare the variables
DECLARE @UserDefinedObjects INT
--Set the variables
SET @UserDefinedObjects=0

SELECT
CASE
WHEN Len(CONVERT(sysname, SERVERPROPERTY('InstanceName')))>0 THEN SERVERPROPERTY('InstanceName')
ELSE 'Default Instance'
END AS 'Named/Default Instance',
CASE
WHEN SERVERPROPERTY('IsClustered')=1 THEN 'YES'
ELSE 'NO'
END AS 'IsClustered',
CASE
WHEN EXISTS ( SELECT * FROM [master].[sys].[all_objects]  WITH (NOLOCK) WHERE name like 'xp_sqllitespeed%') THEN 'YES'
ELSE 'NO'
END AS 'SQLLiteSpeed Installed',
CASE
WHEN SERVERPROPERTY('IsFullTextInstalled')=1 THEN 'YES'
ELSE 'NO'
END AS 'IsFullTextInstalled',
CASE
WHEN EXISTS (SELECT * FROM [msdb].[dbo].[log_shipping_primary_databases]  WITH (NOLOCK) ) THEN 'YES'
WHEN EXISTS (SELECT * FROM [msdb].[dbo].[log_shipping_primary_secondaries]  WITH (NOLOCK) ) THEN 'YES'
ELSE 'NO'
END AS 'Log Shipping',
CASE
WHEN EXISTS (SELECT * FROM [msdb].[sys].[database_mirroring]  WITH (NOLOCK) WHERE mirroring_partner_name IS NOT NULL) THEN 'YES'
ELSE 'NO'
END AS 'Database Mirroring',
CASE
WHEN EXISTS (SELECT *  FROM [master].[sys].[databases]  WITH (NOLOCK) WHERE (is_published=1 OR is_subscribed=1 OR is_merge_published=1 OR is_distributor=1)) THEN 'YES'
ELSE 'NO'
END AS 'Replication',
CASE
WHEN EXISTS (SELECT * FROM [master].[sys].[servers]  WITH (NOLOCK) WHERE is_linked=1) THEN 'YES'
ELSE 'NO'
END AS 'Linked Servers',
   CASE
WHEN EXISTS ( SELECT * FROM [msdb].[dbo].[sysssispackages] WITH (NOLOCK) ) THEN 'YES'
ELSE 'NO'
END AS 'SSIS Packages',
CASE
WHEN EXISTS ( SELECT * FROM [msdb].[dbo].[sysmaintplan_plans] WITH (NOLOCK) ) THEN 'YES'
ELSE 'NO'
END AS 'Maintenance Plans',
CASE
WHEN NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysmail_profile] WITH (NOLOCK) ) THEN 'NO'
       ELSE 'YES'
END AS 'DBMail',
CASE
WHEN EXISTS ( SELECT * FROM [msdb].[dbo].[sysoperators] WITH (NOLOCK) ) THEN 'YES'
ELSE 'NO'
END AS 'Operators',
CASE
WHEN EXISTS ( SELECT * FROM [msdb].[dbo].[sysalerts] WITH (NOLOCK) ) THEN 'YES'
ELSE 'NO'
END AS 'Alerts',
CASE
WHEN EXISTS (SELECT * FROM [master].[sys].[all_objects]  WITH (NOLOCK) WHERE type='X' and is_ms_shipped=0) THEN 'YES'
ELSE 'NO'
END AS 'Extended Stored Procedures',
CASE
WHEN @UserDefinedObjects>0 THEN 'YES'
ELSE 'NO'
END AS  'User defined Extended SP''s',
CASE
WHEN EXISTS ( SELECT * FROM [master].[sys].[messages] WITH (NOLOCK) WHERE message_id> 50001 ) THEN 'YES'
ELSE 'NO'
END AS 'UserDefined Messages',
CASE
WHEN EXISTS ( SELECT  * FROM sys.backup_devices WITH (NOLOCK) ) THEN 'YES'
ELSE 'NO'
END AS 'Backup Devices'

Print'

'
Print 'Lists the SQL Server Aliases

'
set nocount on
GO
CREATE TABLE #Alias
(Output VARCHAR(1024))
DECLARE @advoptions varchar(20)
IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'show advanced options' and value_in_use = 0)
BEGIN
USE [master]
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
SET @advoptions='Advoption'
End

--for xp_cmdshell
DECLARE @XPCMDshell varchar(20)
IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell' and value_in_use = 0)
BEGIN
USE [master]
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
SET @XPCMDshell = 'CmdshellEnabled'
END

if exists(SELECT [name] FROM sys.configurations WHERE NAME LIKE '%64%')
begin
INSERT INTO #Alias exec xp_cmdshell 'reg query "HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"'
DELETE FROM #Alias WHERE Output IS NULL or Output like 'HKEY%'
if exists (select * from #Alias where Output like '%error%')
print 'Aliases not defined'
else
select SQL_Alias_64bit=Output from  #Alias
end

else

begin
INSERT INTO #Alias exec xp_cmdshell 'reg query "HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo"'
DELETE FROM #Alias WHERE Output IS NULL or Output like 'HKEY%'
if exists (select * from #Alias where Output like '%error%')
print 'Aliases not defined'
else
select SQL_Alias_32bit=Output from  #Alias
end

drop table #Alias

IF @XPCMDshell = 'CmdshellEnabled'
BEGIN
USE [master]
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

END
IF @advoptions='Advoption'
BEGIN
USE [master]
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE
End

Print'

'
Print 'Host names and Ip addresses that are listed under either LINKEDSERVER_NAME or SRV_DATASOURCE

'
Set nocount on
SELECT CAST(name as varchar(20)) as LINKEDSERVER_NAME,CAST(srv.provider as varchar(15)) as SRV_PROVIDERNAME,
CAST(srv.product as varchar(15)) as SRV_PRODUCT,CAST(srv.data_source as varchar(20)) as SRV_DATASOURCE,
CAST(srv.provider_string as varchar(20)) as SRV_PROVIDERSTRING,CAST(srv.CATALOG as varchar(15)) as SRV_CAT
FROM sys.servers srv WHERE is_linked = 1 and srv.data_source is not null

Print '

'      
Print 'Host names and IP adddress used in master..sysservers

'
Select CAST(srvname as varchar(20)) as ServerName,CAST(providername as varchar(15)) as ProviderName,
CAST(srvproduct as varchar(15)) as ServerProduct,CAST(datasource as varchar(15)) as DataSource,
CAST(providerstring as varchar(15)) as ProviderString,CAST( catalog as varchar(20)) as Catalog
FROM master..sysservers

Print '

'
Print 'Servers Participating in Mirrioring

'
SELECT cast(@@servername as varchar(20)) as Current_Server, CAST(db_name(database_id) as varchar(20))
as Database_Name, CAST(Mirroring_Role_Desc as varchar(15)) as Mirror_Role_Desc ,
CAST(Mirroring_Partner_Name as varchar(30)) as Mirror_Partner_Name ,
CAST(Mirroring_Partner_Instance as varchar(20)) as Mirror_Partner_Instance  ,
CAST(Mirroring_Witness_Name as varchar(15)) as Mirror_Witness_Name
FROM sys.database_mirroring where mirroring_role in (1, 2)

Print '

'
Print'Servers Participating in Logshipping

'
set nocount on
GO
select cast(lp.primary_server as varchar(15)) AS Primary_Server,
cast(lp.primary_database as varchar(15)) AS [Database],
cast(lps.secondary_server as varchar(15)) AS Secondary_Server,
cast(lsp.monitor_server as varchar(15)) AS Monitor_Server
from
master.dbo.sysDatabases SD
inner join msdb.dbo.log_shipping_monitor_primary LP on lp.primary_database=SD.name
inner join msdb.dbo.log_shipping_primary_secondaries lps on sd.name=lps.secondary_database
inner join msdb.dbo.log_shipping_primary_databases lsp on lp.primary_database=lsp.primary_database

Print'

'
Print'Host names and IP adddress used in Sysadmin..Serversettings

'
IF  EXISTS (SELECT * FROM sys.databases WHERE name = N'Sysadmin')
Begin
select * from Sysadmin..ServerSettings where settingname='ServerName'or
 (SettingValue like '%[0-9].[0-9].[0-9].[0-9]%' -- 1.1
or SettingValue like '%[0-9].[0-9].[0-9][0-9].[0-9]%' -- 1.11
or SettingValue like '%[0-9].[0-9].[0-9][0-9][0-9].[0-9]%' -- 1.111
or SettingValue like '%[0-9].[0-9][0-9].[0-9][0-9].[0-9]%' --11.11
or SettingValue like '%[0-9].[0-9][0-9].[0-9].[0-9]%' -- 11.1
or SettingValue like '%[0-9].[0-9][0-9].[0-9][0-9][0-9].[0-9]%' -- 11.111
or SettingValue like '%[0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9]%' -- 111.111
or SettingValue like '%[0-9].[0-9][0-9][0-9].[0-9][0-9].[0-9]%' -- 111.11
or SettingValue like '%[0-9].[0-9][0-9][0-9].[0-9].[0-9]%') -- 111.1
and SettingValue not like '%[0-9].00.00.[0-9]%'
End

else

Print 'Sysadmin DB Doesnot Exists'

Print '

'
Print 'Host names and IP address used in SQL Agent Job steps

'
Declare @servername varchar(128)
Declare @SQLCMD varchar(2000)
Declare @SQLCMD1 varchar(2000)
Declare @name varchar(2000)
--Retreives the Server  info
 create table #tmpObjectsfound (job_name varchar(200),Step_id varchar(200),step_name varchar(500),serverfound varchar(500),IP_Information varchar(1000))


DECLARE servername_cursor CURSOR READ_ONLY FOR
select srvname from master..sysservers

OPEN servername_cursor

FETCH NEXT FROM servername_cursor
INTO @servername

WHILE @@FETCH_STATUS = 0
BEGIN

Select @SQLCMD = 'select s.name,ss.Step_id,ss.step_name,' + '''' + @servername + '''' + ' as ServernameFound  from msdb..sysjobs s join msdb..sysjobsteps ss on s.job_id=ss.job_id
where command like ' + '''' + '%' + @servername + '%' + ''''
 
insert #tmpObjectsFound (job_name,Step_id,step_name,serverfound)
exec(@SQLCMD)

FETCH NEXT FROM servername_cursor
INTO @servername
END

CLOSE servername_cursor
DEALLOCATE servername_cursor


--Retreives the IP information
declare @localserver varchar (100)
set @localserver=(select @@SERVERNAME)

Select @SQLCMD1 = 'select s.name,ss.Step_id,ss.step_name,
' + '''' + @localserver + '''' + ' as ServernameFound,ss.command from msdb..sysjobs s join msdb..sysjobsteps ss on s.job_id=ss.job_id
where (command like ''%[0-9].[0-9].[0-9].[0-9]%'' -- 1.1
or command like ''%[0-9].[0-9].[0-9][0-9].[0-9]%'' -- 1.11
or command like ''%[0-9].[0-9].[0-9][0-9][0-9].[0-9]%'' -- 1.111
or command like ''%[0-9].[0-9][0-9].[0-9][0-9].[0-9]%'' --11.11
or command like ''%[0-9].[0-9][0-9].[0-9].[0-9]%'' -- 11.1
or command like ''%[0-9].[0-9][0-9].[0-9][0-9][0-9].[0-9]%'' -- 11.111
or command like ''%[0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9]%'' -- 111.111
or command like ''%[0-9].[0-9][0-9][0-9].[0-9][0-9].[0-9]%'' -- 111.11
or command like ''%[0-9].[0-9][0-9][0-9].[0-9].[0-9]%'') -- 111.1
and command not like ''%[0-9].00.00.[0-9]%'''

insert #tmpObjectsFound (job_name,Step_id,step_name,serverfound,IP_Information)

exec(@SQLCMD1)
select CAST(job_name as varchar(30)) as Job_name,CAST(step_id as int) as step_id,CAST(step_name as varchar(30)) as Step_Name,
cast(serverfound as varchar(20)) as serverfound,cast(ip_information as varchar(50)) as ip_information from #tmpObjectsFound
drop table #tmpObjectsFound

Print '

'
Print 'Lists the DTS Packages

'
select distinct cast(name as varchar(40)) as Name ,cast(owner as varchar(20)) as Owner from msdb..sysdtspackages

Print'

'

Print 'Lists the SQL Services installed

'
SET NOCOUNT ON

--  Temporary Tables
CREATE TABLE #Services
(oOutput VARCHAR(1024))

CREATE TABLE #ServicesDetail
(oOutput VARCHAR(1024))

CREATE TABLE #ServicesDetail1
(oOutput VARCHAR(1024))

CREATE TABLE #ServicesFinal
(ServiceName VARCHAR(200),
ServiceOwner VARCHAR(200),
ServiceStartTp VARCHAR(200),
ServiceBinary VARCHAR(200),
STATE VARCHAR(150))

DECLARE @advanced_options varchar(20)
IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'show advanced options' and value_in_use = 0)
BEGIN
USE [master]
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
SET @advanced_options='Advoption Enabled'
End

--for xp_cmdshell
DECLARE @XP_CMDshellstatus varchar(20)
IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell' and value_in_use = 0)
BEGIN
USE [master]
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
SET @XP_CMDshellstatus = 'Cmdshell Enabled'
END

-- sc query is used to query the entire service control manager and then filters
-- by anything with "SQL" in it's name.  /I option ignores Case.
INSERT INTO #Services EXEC xp_cmdshell 'sc query state= all |find /I "Reportserver"|find /I "service_name"'
INSERT INTO #Services EXEC xp_cmdshell 'sc query state= all |find /I "sql"|find /I "service_name"'
INSERT INTO #Services EXEC xp_cmdshell 'sc query state= all |find /I "OLAP"|find /I "service_name"'
INSERT INTO #Services EXEC xp_cmdshell 'sc query state= all |find /I "DTS"|find /I "service_name"'


-- Remove NULL records
DELETE FROM #Services WHERE oOutput IS NULL

-- Cursor variables
DECLARE @curServNm  VARCHAR(100)
DECLARE @cCMD       VARCHAR(100)
DECLARE @cBinary    VARCHAR(150)
DECLARE @cOwner     VARCHAR(100)
DECLARE @cStartTp   VARCHAR(100)
DECLARE @cCMD1      VARCHAR(100)
DECLARE @cstate     VARCHAR(100)

DECLARE cCursor CURSOR FOR
SELECT RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) )) AS ServiceName
FROM #Services

OPEN cCursor
FETCH NEXT FROM cCursor INTO @curServNm

 WHILE @@FETCH_STATUS = 0

  BEGIN

   --  Option  to query SC.
   SET @cCMD = 'sc qc "#SERVICENAME#"'
   SET @cCMD = REPLACE(@cCMD, '#SERVICENAME#', @curServNm)
     
    INSERT INTO #ServicesDetail EXEC xp_cmdshell @cCMD

    DELETE FROM #ServicesDetail WHERE oOutput IS NULL
   
    -- second table
   
    SET @cCMD1 = 'sc query "#SERVICENAME#"'
   SET @cCMD1 = REPLACE(@cCMD1, '#SERVICENAME#', @curServNm)
 
 
    INSERT INTO #ServicesDetail1 EXEC xp_cmdshell @cCMD1

    DELETE FROM #ServicesDetail1 WHERE oOutput IS NULL
                         
         
    SELECT @cBinary = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
    FROM   #ServicesDetail
    WHERE  PATINDEX('%BINARY_PATH_NAME%', oOutPut) > 0
   
    SELECT @cOwner = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
    FROM   #ServicesDetail
    WHERE  PATINDEX('%SERVICE_START_NAME%:%', oOutPut) > 0
   
    SELECT @cStartTp = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
    FROM   #ServicesDetail
    WHERE  PATINDEX('%START_TYPE%:%', oOutPut) > 0
   
    SELECT @cstate = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
    FROM   #ServicesDetail1
    WHERE  PATINDEX('%STATE%:%', oOutPut) > 0

    INSERT INTO #ServicesFinal (
     ServiceName,
     ServiceOwner,
     STATE,
     ServiceStartTp,
     ServiceBinary
      )
    VALUES(
     @curServNm,
     @cOwner,
     @cstate,
     @cStartTp,
     @cBinary)

FETCH NEXT FROM cCursor INTO @curServNm
END

CLOSE cCursor
DEALLOCATE cCursor

-- Final result set
SELECT CAST(ServiceName as varchar(40)) as ServiceName,CAST(ServiceOwner as varchar(30)) as ServiceOwner,
cast (ServiceStartTp as varchar(20)) as ServiceStartTp,cast(State as varchar(15)) as State FROM #ServicesFinal

-- Clean-up objects
IF OBJECT_ID('TempDB.dbo.#Services') IS NOT NULL
 DROP TABLE #Services

IF OBJECT_ID('TempDB.dbo.#ServicesDetail') IS NOT NULL
 DROP TABLE #ServicesDetail


IF OBJECT_ID('TempDB.dbo.#ServicesDetail1') IS NOT NULL
 DROP TABLE #ServicesDetail1

IF OBJECT_ID('TempDB.dbo.#ServicesFinal') IS NOT NULL
 DROP TABLE #ServicesFinal

 IF @XP_CMDshellstatus = 'Cmdshell Enabled'
BEGIN
USE [master]
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

END
IF @advanced_options='Advoption Enabled'
BEGIN
USE [master]
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE
End

Print 'Find all references to sysservers in DDL across all databases

'

Declare @server_name varchar(128)
Declare @DB_Name varchar(128)
Declare @DB_Id int
Declare @SQL_CMD varchar(2000)

Create Table #tmpObjects_Found (dbname varchar(128), objectname varchar(128), ServerFound varchar(128))

DECLARE database_cursor CURSOR READ_ONLY FOR
select name, dbid from master..sysdatabases
where DATABASEPROPERTYEX ( name , 'status' ) = 'ONLINE'
and name != 'tempdb'

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @DB_Name, @DB_Id

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE servername_cursor CURSOR READ_ONLY FOR
select srvname from master..sysservers

OPEN servername_cursor

FETCH NEXT FROM servername_cursor
INTO @server_name

WHILE @@FETCH_STATUS = 0
BEGIN

Select @SQL_CMD = 'select ' + '''' + @DB_Name + '''' + ' as DBName,' + 'object_name(id, ' + convert(varchar(10), @DB_Id) + ') as ObjectName, ' + '''' + @server_name + '''' + ' as ServerFound from ' + '[' + @DB_Name + ']' + '..syscomments (NOLOCK) where text like ' + '''' + '%' + @server_name + '%' + ''''
--print @SQL_CMD
insert #tmpObjects_Found (DBName, ObjectName, ServerFound)
exec(@SQL_CMD)
FETCH NEXT FROM servername_cursor
INTO @server_name
END

CLOSE servername_cursor
DEALLOCATE servername_cursor

FETCH NEXT FROM database_cursor
INTO @DB_Name, @DB_Id
END

CLOSE database_cursor
DEALLOCATE database_cursor

select distinct cast(dbname as varchar(20)) as dbname, cast(objectname as varchar(50)) as objectname, cast(ServerFound as varchar (30)) as Serverfound from #tmpObjects_Found
drop table #tmpObjects_Found

Print '

'
Print 'Ip Address in DDL for all databases

'
--Find Ip Address in DDL for all databases
Declare @servername1 varchar(128)
Declare @DBName varchar(128)
Declare @DBId int
Declare @SQL nvarchar(max)


create table #tmp_Objects_found (database_name varchar(100),schemaname varchar(100),Object_name varchar(200),object_Type varchar(500))

DECLARE database_cursor CURSOR READ_ONLY FOR

select name, dbid from master.sys.sysdatabases where databasepropertyex(name, 'Status') IN ('ONLINE')

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @DBName, @DBId

WHILE @@FETCH_STATUS = 0
BEGIN

SET @servername1 = (SELECT @@servername)

SET @SQL = 'USE [' + @DBName + '];                                                    
                                                INSERT INTO #tmp_Objects_found
                                                SELECT DISTINCT ''' + @DBName + ''', OBJECT_SCHEMA_NAME(id),object_name(id),so.type_desc from syscomments sc join sys.objects so on sc.id=so.object_id
where (text like ''%[0-9].[0-9].[0-9].[0-9]%'' -- 1.1
or text like ''%[0-9].[0-9].[0-9][0-9].[0-9]%'' -- 1.11
or text like ''%[0-9].[0-9].[0-9][0-9][0-9].[0-9]%'' -- 1.111
or text like ''%[0-9].[0-9][0-9].[0-9][0-9].[0-9]%'' --11.11
or text like ''%[0-9].[0-9][0-9].[0-9].[0-9]%'' -- 11.1
or text like ''%[0-9].[0-9][0-9].[0-9][0-9][0-9].[0-9]%'' -- 11.111
or text like ''%[0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9]%'' -- 111.111
or text like ''%[0-9].[0-9][0-9][0-9].[0-9][0-9].[0-9]%'' -- 111.11
or text like ''%[0-9].[0-9][0-9][0-9].[0-9].[0-9]%'') -- 111.1
and text not like ''%[0-9].00.00.[0-9]%'''
--PRINT @SQL
EXEC sp_executesql @SQL;
FETCH NEXT FROM database_cursor
INTO @DBName, @DBId
END

CLOSE database_cursor
DEALLOCATE database_cursor
SELECT CAST(database_name as varchar(20)) as database_name,CAST(schemaname as varchar(15)) as schemaname,
CAST(Object_name as varchar(20)) as Object_name,CAST(object_type as varchar(20)) as object_type
FROM #tmp_Objects_found
DROP TABLE #tmp_Objects_found;

Print '

'
Print 'Script will retreive the last backup date for all

'
SELECT cast(sd.Name as varchar(30)) AS DatabaseName,cast(d.state_desc as varchar(15)) as DBStatus,
COALESCE(CONVERT(VARCHAR(12), MAX(bs.backup_finish_date), 101),'BACKUP NEVER GENERATED') AS LastBackUpDate
FROM sys.sysdatabases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sd.name
join sys.databases d on sd.dbid=d.database_id
where d.state_desc='online'
GROUP BY sd.Name,d.state_desc

Print'

'
Print'Read SQL Error log for any errors

'
CREATE TABLE #SQL_ErrorLog (date varchar(100), processinfo varchar(100),Message varchar(500))
CREATE INDEX errorlog_msg ON #SQL_Errorlog(Message)
INSERT #SQL_ErrorLog EXEC master..xp_readerrorlog 0,1, "Error:"
SELECT cast(date as varchar(25)) AS Date,cast(processinfo as varchar(15)) AS Processinfo,
cast(Message as varchar(100)) AS Message  FROM #SQL_ErrorLog
DROP TABLE #SQL_ErrorLog

use msdb
Print 'List if SSIS packages'
select * from  sysssispackages where packagetype =5 and packageformat <>1

Print 'List of Maintanance Plans'
select * from  sysssispackages where packagetype=6