SQL Server – Issues with INFORMATION_SCHEMA.view_definition

ddlsql server

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'

is that 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 nvarchar(max).

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?

Best Answer

As Erik mentioned in the article he linked, INFORMATION_SCHEMA is a deprecated schema from the classic days of SQL Server 2000. You should read the article he linked as it goes through the reasons it's deprecated, and cites Microsoft's docs that support those reasons.

You can instead use the following query that leverages the sys.objects view to get the definition of a View:

SELECT m.[definition] AS ObjectDefinition
FROM sys.objects o
INNER JOIN sys.sql_modules m 
    ON m.[object_id] = o.[object_id]
WHERE o.[object_id] = OBJECT_ID('dbo.YourViewName')
    AND o.[type] = 'V';

Or the function OBJECT_DEFINITION() to also get the definition as follows:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.YourView')) AS ObjectDefinition;

One caveat to doing it either way, is it'll contain the last script used to modify the object. So if the View was altered since it's creation, it'll start with the ALTER VIEW statement otherwise it'll have the CREATE VIEW statement.

Related Question