Simple string parsing using TSql using the SPLIT and CharIndex functions

Simple string parsing using TSql using the SPLIT and CharIndex functions
Here I need to extract the data AFTER the $ in the input.

--Split out instance Name
SELECT SUBSTRING('MSSQL$SQLDBT01',CHARINDEX('$','MSSQL$SQLDBT01')+1,len('MSSQL$SQLDBT01'))

Here is the output
------------
SQLDBT01

Simple way to add Clustered Primary Keys to multiple tables

I used this to create clustered primary keys for each table that i cared about within a database. I joined the
SELECT i.TABLE_NAME--, i.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES i
with a table that stored all of the objects that I cared about and created a list of all the obects.

So for example
SELECT i.TABLE_NAME--, i.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLES i
INNER JOIN
(select t.Trace_Table_Name + '_id' as Trace_Table_Name
from Trace_Directory t
join sysobjects o on t.Trace_Table_Name = o.name) t
ON t.Trace_Table_Name =i.table_name
ORDER BY i.TABLE_NAME

Complete script


DECLARE cPK CURSOR FOR
SELECT i.TABLE_NAME--, i.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES i
--Remember to remove this for all tables or add your own logic here
INNER JOIN
(select t.Trace_Table_Name + '_id' as Trace_Table_Name
from Trace_Directory t
join sysobjects o on t.Trace_Table_Name = o.name) t
ON t.Trace_Table_Name =i.table_name
ORDER BY i.TABLE_NAME
DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
--Loop through all tables
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable--, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT PK_' + @PkTable + '_1 PRIMARY KEY CLUSTERED ( id ASC , rowNumber ASC) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
SET @PKSQL= @PKSQL + char(10) + char(13)
SET @PKSQL= @PKSQL + 'GO '
-- Print the primary key statement
PRINT @PKSQL
FETCH NEXT FROM cPK INTO @PkTable--, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Here is the output

ALTER TABLE Trace_johnsqlp66sqlprod02_20080707_0001_id ADD CONSTRAINT PK_Trace_johnsqlp66sqlprod02_20080707_0001_id_1 PRIMARY KEY CLUSTERED ( id ASC , rowNumber ASC) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO