SQL Server clustered instance dependencies – verification

Goal

To check and verify if the existent dependencies for sql server resources are in place

My way of achieving it

Using powershell. From time to time due to adding more disks to an instance or performing other activities we end up having a resource that is not tied to the parent resource and when it comes to sql server disks some of the databases residing on those disks are not accessible. In order to check if we have any of the resources not being tied to the right parent resource I have created the script below that will tell us which resources are not tied. The output of the script looks like below

disk_dep_powershell

The code can be downloaded from here

Split strings using tsql – a different method

Goal

To split a string separated by comma or other char using tsql

My way of achieving it

Using dynamic sql

The method that I will describe here is working good enough for the situations in which we want to split a char no longer than 50k chars and it has the benefit of less typing and more intuitive approach but this is only my opinion about it.  Most of the approaches are using CLR, XML, numbers table, CTE and others and are very good explained here (http://sqlperformance.com/2012/07/t-sql-queries/split-strings). The code that I am using to split the string is below

declare @databases nvarchar(max)
set @databases=’a,id,xyz,abcd,abcde,sa,foo,bar,mort,splunge,bacon’
–SET @databases = REPLICATE(@databases, POWER(10, 3));
select @databases=’select ”’+replace(@databases,’,’,”’ union all select ”’)+””
exec(@databases)

If you need to split the string based on a different char then replace comma in the replace function with the new char, for example

select @databases=’select ”’+replace(@databases,’|’,”’ union all select ”’)+””
exec(@databases)

It seems that in the form above the dynamic sql is very dangerous (was made very clear to me here, http://sqlperformance.com/2016/03/sql-server-2016/string-split#comment-121269 )  and I changed it to

select @string ='select '''+replace(replace(@string,'''',''),',',''' union all select ''')+''''

which removes the possibility of running some bad code but it also breaks words like don’t or won’t.