CSV to Table Conversion

DECLARE @string varchar(250), @delim char(1)

SET @string = '1,12,15,875,100,9'

SET @delim = ','

CREATE TABLE #result(Element int)

DECLARE @sql varchar(4000)

SELECT @sql = 'INSERT INTO #Result SELECT ' + REPLACE(@string, @Delim, ' UNION ALL SELECT ')

exec (@sql)

SELECT * FROM #result

DROP TABLE #result

Convert the first character of each word to upper case

CREATE Function init_caps

(

@pi_string varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

DECLARE

@string varchar(max)

,@formatted_string varchar(max)

,@length int

SET @string = ltrim(rtrim(@pi_string))

SET @length = len(@string)

SET @formatted_string = upper(left(@string,1)) + lower(substring(@string,2,@length))

-- change first character after each space to upper

DECLARE @space int

SET @space = charindex(' ', @string)

WHILE @space > 0

BEGIN

SET @formatted_string = left(@formatted_string,@space)

+ upper(substring(@formatted_string,@space+1, 1))

+ substring(@formatted_string,@space+2, 2000)

SET @space = charindex(' ', @string, @space+1)

END

RETURN @formatted_string

END

GO

Powershell SMO Connection

# Load SMO libraries

[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

[Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

[Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

[Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, `

Version=9.0.242.0, Culture=neutral, `

PublicKeyToken=89845dcd8080cc91") > $null

# define connection properties

[string] $server_name = 'myserver'

[string] $db_name = 'mydb'

[bool] $windows_login = $true

# Create server object

$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name

# if not using windows authentication then prompt for credentials

if ($windows_login -eq $false)

{

# prompt for connection details

$server.ConnectionContext.LoginSecure=$false;

$credential = Get-Credential

$username = $credential.UserName -replace("\\","")

$server.ConnectionContext.set_Login($username)

$server.ConnectionContext.set_SecurePassword($credential.Password)

}

$db = $server.Databases[$db_name]

# list all tables

foreach ($table in $db.Tables)

{

write-host $table.name

}