SQL Quick Reference

How to insert new line/line break in string

Tab char(9)

Line feed char(10)

Carriage return char(13)

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
PRINT @text

1. use PRINT @text rather SELECT to get this result. PRINT will have limit on charators

2. To test your output, if using SSMS make sure the option Retain CR/LF on copy or save is checked, else all pasted results will loose the line feed. You find this at settings, query results, sql server, results to grid.

Search for SQL objects

Declare @strFind varchar(max)

Set @strFind = 'facility\_shared\_flag'

--INFORMATION_SCHEMA.ROUTINES -> ROUTINE_DEFINITION only return first nvarchar(4000)

--SELECT object_Name(object_id),* FROM sys.sql_modules WHERE definition LIKE '%'+@strFind+'%'

--select * from sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%'+@strFind+'%'

DECLARE @command varchar(1000) 

SELECT @command = 'USE ? print ''?''; select ''?'' as DB_Name, object_Name(object_id),* from sys.sql_modules WHERE definition LIKE ''%'+@strFind+'%'' ESCAPE ''\'' order by object_Name(object_id)'

EXEC sp_MSforeachdb @command