Thursday, 27 February 2014

trimming characters

declare @T table
  (
  Col varchar(20)
  )



  insert into @T
  Select 'WO-012345' --'images/test1.jpg'
  --union all
  --Select 'images/test2.png'
  --union all
  --Select 'images/test3.jpg'
  --union all
  --Select 'images/test4.jpeg'
  --union all
  --Select 'images/test5.jpeg'

 Select substring( col,charindex('-',Col)+1,6 )
from @T

find a column name in all databases

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%gradient%'
ORDER BY schema_name, table_name;