We can capture host address of a server through T_SQL using below script.
DECLARE @ipaddress varchar(max)
DECLARE @ipaddress varchar(max)
DECLARE @ipaddressLine varchar(max)
DECLARE @pos int
SET nocount on
SET @ipaddress = NULL
Create table #temp_ip (ipLine varchar(200))
INSERT #temp_ip EXEC master..xp_cmdshell 'ipconfig'
SELECT top 1
@ipaddressLine = ipLine FROM #temp_ip
WHERE upper (ipLine) like '%IPV4 ADDRESS%' or upper (ipLine) like '%IP ADDRESS%'
IF (isnull (@ipaddressLine,'***') != '***')
BEGIN
SET @pos = CharIndex (':',@ipaddressLine,1);
SET @ipaddress = rtrim(ltrim(substring (@ipaddressLine ,
@pos + 1 ,len (@ipaddressLine) - @pos)))
END
PRINT @ipaddress
DROP TABLE #temp_ip
SET nocount off
Query to Find out the XP_CommandShell Status
ReplyDeleteSELECT case CONVERT(INT, ISNULL(value, value_in_use)) when 0 then 'xp_cmdshell is disabled'
when 1 then 'xp_cmdshell is enabled' else 'null' end
FROM sys.configurations
WHERE name = 'xp_cmdshell' ;
To Add Row Values in a Single Column..
ReplyDeletedeclare @dblist nvarchar(max)
IF OBJECT_ID('tempdb..#temp_TLogDBList') IS NOT NULL DROP TABLE #temp_TLogDBList;
Create table #temp_TLogDBList (Names nvarchar(max))
insert into #temp_TLogDBList select 'MSSQL:' + name from master..sysdatabases where databasepropertyex(name,'Recovery') ='FULL' and DATABASEPROPERTYEX(name,'Status') = 'ONLINE'
and name not in('master','model','msdb','tempdb')
--select * from #temp_TLogDBList
SELECT @dblist =Stuff(
(SELECT N' ' + Names FROM #temp_TLogDBList FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
print @dblist
declare @dblist nvarchar(max)
ReplyDeleteIF OBJECT_ID('tempdb..#temp_TLogDBList') IS NOT NULL DROP TABLE #temp_TLogDBList;
Create table #temp_TLogDBList (Names nvarchar(max))
insert into #temp_TLogDBList select 'MSSQL:' + name from master..sysdatabases where databasepropertyex(name,'Recovery') ='FULL' and DATABASEPROPERTYEX(name,'Status') = 'ONLINE'
and name not in('master','model','msdb','tempdb')
--select * from #temp_TLogDBList
SELECT @dblist =Stuff(
(SELECT N' ' + Names FROM #temp_TLogDBList FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
print @dblist
Adding Row Values in a Single Column..
ReplyDeletedeclare @dblist nvarchar(max)
IF OBJECT_ID('tempdb..#temp_TLogDBList') IS NOT NULL DROP TABLE #temp_TLogDBList;
Create table #temp_TLogDBList (Names nvarchar(max))
insert into #temp_TLogDBList select 'MSSQL:' + name from master..sysdatabases where databasepropertyex(name,'Recovery') ='FULL' and DATABASEPROPERTYEX(name,'Status') = 'ONLINE'
and name not in('master','model','msdb','tempdb')
--select * from #temp_TLogDBList
SELECT @dblist =Stuff(
(SELECT N' ' + Names FROM #temp_TLogDBList FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
print @dblist