I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code
select * from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'V_SC_ALL_CUSTOMER_DOCUMENTS'
VIEW_DEFINITION cuts view DDL short. Then I've learned to use
sys.sql_modules to get full definition.
I ran these
sp_help 'INFORMATION_SCHEMA.VIEWS' sp_help 'sys.sql_modules'
and found out that
INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION nvarchar size = 8000 and
sys.sql_modules.DEFINITION nvarchar size = -1. I believe
What is the point then to have
VIEW_DEFINITION if you can't rely on it? And are there other types of objects that I should retrieve in
sql_modules rather than from their corresponding tables, like Procedures, triggers, etc?