Get Columns in Table

http://beyondrelational.com/justlearned/posts/792/sql-server-to-get-comma-separated-value-for-every-column-name-in-tables-with-t-sql.aspx

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+', ' ,'') + Name
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID)='dat_groups'
SELECT @listStr

T-SQL Count Spaces in String

http://stanbiron.com/2010/07/29/SimpleWayToCountCharactersAndWordsUsingTSQL.aspx

-- Count the number of words

DECLARE @String VARCHAR(100)

SELECT @String = 'SQL Server 2005 Stan test code'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

Longest Running Queries

http://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx#bm1151148

SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 average_seconds
, qs.total_elapsed_time / 1000000.0 total_seconds
, qs.execution_count
, SUBSTRING (qt.text,qs.statement_start_offset/2
, ( CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
)/2
) individual_query
, o.name object_name
, DB_NAME(qt.dbid) database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
LEFT OUTER JOIN sys.objects o
ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC

SSMS REGEX - Remove Carriage Returns

http://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/

\n @\n
\n

SQL Regex

http://beyondrelational.com/blogs/naomi/archive/2011/10/19/remove-bad-characters-from-a-string.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Nov02

01.CREATE FUNCTION dbo.RemoveBadChars
02. (@String VARCHAR(8000),
03. @AllowedPattern VARCHAR(100))
04.RETURNS VARCHAR(8000)
05.AS
06. BEGIN
07. DECLARE @nBadChars INT,
08. @Rest VARCHAR(100)
09.
10. SET @String = '!@#$% 123 ??abc##rd,,,'
11.
12. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
13.
14. WHILE @nBadChars > 0
15. BEGIN
16. SET @Rest = Substring(@String,@nBadChars + 1,Len(@String))
17.
18. SET @String = Substring(@String,1,@nBadChars - 1)
19.
20. SET @Rest = Substring(@Rest,Patindex('%[' + @AllowedPattern + ']%',@Rest),
21. Len(@Rest))
22.
23. SET @String = @String + @Rest
24.
25. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
26. END
27.
28. RETURN @String
29. END
30.
31.GO



1.SELECT dbo.RemoveBadChars('1234@@@@@@@@@????????djwkejwk^&*-+=a',' 0-9a-z')