Get definition text of stored procedures

INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION returns the first 4000 characters of the definition text of stored procedure only, if the function or stored procedure is not encrypted.

SELECT
r.ROUTINE_NAME,
OBJECT_DEFINITION(OBJECT_ID(SCHEMA_NAME(o.schema_id) + '.' + r.ROUTINE_NAME)) complete_text,
r.ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES r
INNER JOIN sys.objects o
ON r.ROUTINE_NAME = o.name
WHERE LOWER(r.ROUTINE_NAME) LIKE '%somestring%'
ORDER BY r.ROUTINE_NAME

Modify maximum server memory

In Microsoft SQL Server Management Studio, open Object Explorer. Right click on server and click on Properties. Select page Memory and modify Maximum server memory (in MB):; 1024 is a good value.

group_concat: concatenate aggregated rows

Taken from this blog post.

SELECT table_name, 
LEFT(column_names,LEN(column_names) - 1) AS column_names
FROM (SELECT table_name,
(SELECT column_name + ',' AS [text()]
FROM information_schema.columns AS internal
WHERE internal.table_name = table_names.table_name
FOR xml PATH ('')
) AS column_names
FROM (SELECT table_name
FROM information_schema.columns
GROUP BY table_name) AS table_names) AS pre_trimmed;