Do you know we can get the definition of all the database programmable objects using a single query only? You can get the definition of procedure, view, trigger and function using following query:
SELECT OBJECT_NAME(object_id), definition, is_schema_bound, uses_ansi_nulls, uses_quoted_identifier, uses_database_collation FROM sys.all_sql_modules
We can also get the information about object is schema bound or not, ansi nulls on or off, quoted identifier on or off.
Published under: SQL Server Tips · · · ·
I knew about all_sql_modules, but never thought of using it to get the definition. Good one! Thanks for sharing, Hardik!
all_sql_modules
Yes, same here like nakul, Though know about that, never used in that way... :) nice tip hardik..
Cheers for sharing
Does anyone know how to get the carriage return/line feeds back into the definition?
If you are on SQL Server 2012, the grid control that displays the results will preserve CR/LF. On other versions, you will need to set output to TEXT either through the toolbar or by pressing CTRL+T
CTRL+T
Interesting! Btw, when items are created WITH ENCRYPTION the definition is NULL.