Collect DB sizes across all Servers

drop table #tmp
create table #tmp (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),)

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
insert into #tmp
exec sp_helpfile
--exec sp_spaceused
end
'
go

select * From #tmp

---------------------------------------------
--ANOTHER METHOD
---------------------------------------------


drop table #tmp
create table #tmp (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),db nvarchar(100))
drop table #tmpfinal
create table #tmpfinal (name nvarchar(100),fileid int,[filename]nvarchar(100),filegroup nvarchar(100),size nvarchar(100),maxsize nvarchar(100),growth nvarchar(100),usage nvarchar(100),db nvarchar(100))

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
insert into #tmp (name,fileid,filename,filegroup,size,maxsize,growth,usage)
exec sp_helpfile
--exec sp_spaceused
insert into #tmpfinal (name,fileid,filename,filegroup,size,maxsize,growth,usage,db)
select name,fileid,filename,filegroup,size,maxsize,growth,usage,''?'' from #tmp
truncate table #tmp
end
'

select * From #tmpfinal where filename like '%ftrow_ft_modules.ndf%'

Find a string in Entire Server's Stored Procedures

EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use ?
select ''?'' as DB, o.name, c.[text] --,*
from dbo.sysobjects o inner join dbo.syscomments c on o.[id] = c.[id] where c.[text] like ''%MemberSite_Current%''
end
'
go

Drop User, Drop Owned Schema, Create User, Add RoleMember

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''IF EXISTS (SELECT * FROM sys.schemas WHERE name = N''''SPARKSTAGE\developers'''')''
print ''DROP SCHEMA [SPARKSTAGE\developers]''
print ''go''
print ''IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''''SPARKSTAGE\developers'''')''
print ''DROP USER [SPARKSTAGE\developers]''
print ''go''
print ''CREATE USER [SPARKSTAGE\developers] FOR LOGIN [SPARKSTAGE\developers]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''SPARKSTAGE\developers''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_datawriter'''', N''''SPARKSTAGE\developers''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''SPARKSTAGE\developers''''''
print ''go''
end
'
go

Writting into Win Application log from T-SQL

declare @msg varchar(1000)
set @msg = 'LADBREPORT.mnSubscription.dbo.MemberTran table is missing records'
RAISERROR (@Msg, 16, 1) WITH LOG --> this is Error
exec xp_logevent 60000,@Msg, WARNING --> this is Warning

GRANT EXECUTE ON StoredProcedures TO Account

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)''
print ''set @user = ''''db_executor''''''
print ''DECLARE Cur_name CURSOR FOR ''
print ''select specific_name From information_Schema.routines''
print ''OPEN Cur_name''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''WHILE @@FETCH_STATUS = 0''
print ''BEGIN''
print ''set @string =''''GRANT EXECUTE ON [dbo].[''''+@name+''''] TO [''''+@user+'''']''''''
print ''print @string''
print ''exec sp_executesql @string''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''END''
print ''CLOSE Cur_name''
print ''DEALLOCATE Cur_name''
print ''go''
end
'
go

Xml data type is not supported in distributed queries

we can use OPENQUERY to call the table that has XML data type.

decalre @string nvarchar(4000), @StartDate datetime, @EndDate datetime
set @StartDate = dateadd(dd,-30,getutcdate())
set @EndDate = dateadd(dd,0,getutcdate())

set @string = '
select * from
OPENQUERY([lasqlstage101.sparkstage.com],''
SELECT
OrderID,
Cast(OrderAttributeXML as Varchar(1000)) as OrderAttributeXML,
Insertdate
FROM
[eporder].[dbo].[OrderAttributeValue]
WHERE
insertdate >= '''''+convert(nvarchar(10),@StartDate,101)+' '+convert(nvarchar(10),@StartDate,108)+''''' and insertdate < '''''+convert(nvarchar(10),@EndDate,101)+' '+convert(nvarchar(10),@EndDate,108)+''''' ''
)
'

exec sp_executesql @string

Connecting to SQL Server Named Instances

In order to connect to a named instance of SQL server :

1-make sure remote access is open
2-RCP/IP of named instnace has a static SQL port configured
3-Open Firewall ports for Static SQL Port of named instance
4-Open Firewall ports for 1434 for SQL Server Browser