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
