Powershell Detecting DB state(Mirroring, Primary,Failover)

This script pulls back the status of your DBs on a selected server. Its just a base script; modify as needed.

$server = "dwhtest-new"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$dbs = $srv.Databases
foreach ($db1 in $dbs)
{
$db = New-Object Microsoft.SqlServer.Management.Smo.Database 
$db = $db1
$DatabaseName = $db.Name
Write-Host $DatabaseName
Write-Host "MirroringStatus:" $db.MirroringStatus
Write-Host "DBState:"  $db.Status
Write-Host
}     

If your DB's mirroring is still intact you will recieve 'Synchronized' for MirroringStatus and its its the Primary it will say "Normal" for the status and if its the failover it will say "Restoring". Unfortunately there is no way, that im aware of, to just pull out the status of "Mirror" or "principle". You will jsut have to build logic to check both fo those values. Restoring

String Concatenation

Concatenate multiple rows into a comma delimited string.

declare @table table (
id int
,val varchar(2)
)
insert @table
Values (1 ,'a'),
(1,'b'),
(1,'c'),
(1,'d'),
(2,'a'),
(2,'b'),
(2,'c'),
(3,'a'),
(3,'b')


select id, stuff((
select (','+val) from @table q1
where q1.id = q2.id
for xml path('')),1,1,'') val
from @table q2
group by q2.id

String Split in SQL Server (No storeproc or looping)

Common problem in sql server is manipulating strings. Whether this involves converting multiple rows of data into one row or splitting them there is a multitude of examples of either. Having some wextensive experience with manipulating xml directly in sql server in set based fashion, I decided to take a diffrent approach to the splitting of strings.
Consider the following example:
.

declare @String varchar(200)
set @string = 'test,test2,test3'

select @string

This would simply return a single row with the string showing as it was set. However, what if you wanted the query to return something like this.

row1: test
row2: test2
row3: test3

well it turns out it can be done pretty simply. The first step is constructing your delimited list into a roughly formated xml document. You can then use standard xquery syntax to shred that document into its row by row element values. Below is the finished code example:

declare @TableList varchar(100)
set @TableList = 'test,test2,test3'
--show what xml looks like for example purpose only.
select Convert(xml,('<R>'+replace(@TableList,',','</R><R>')+'</R>')) as xmldoc

select t.value('.','varchar(200)')
    from (select convert(xml,('<R>'+replace(@TableList,',','</R><R>')+'</R>')) as xmldoc)  as a
    Cross apply a.xmldoc.nodes('./R') as b(t)