Can't rely on INFORMATION_SCHEMA.view_definition?

Question

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?

Answer 1

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.

Answer 2

FWIW, I worked a bit on the info schema views back in the days. Among other things I created a database diagram for them (which I probably still have lying around, somewhere).

Then came 2005, and I realized how out-of-phase they were with the product. Just consider the number of catalog views (approx 280) with how many info schema views (21) we have. Also, prior to 2005 we didn't have the schema concept like we do nowadays, so as I recall "schema" in these views could in some context be the database name. And of course this couldn't be changed due to backward compatibility reasons. (I'm a bit hazy on the details, this was some 15 years ago, after all.)

For us who were more involved in this back then, it was pretty obvious that MS didn't spend much resources on this, and I for one dropped use usage of them like a hot potato when 2005 was released.

It is a nice idea to have ANSI/ISO SQL standard for this type of meta-data. But it just doesn't fly in reality, IMO.

(What surprised me now when looking is that I found one named SEQUENCES. This feature was introduced in 2012, so apparently some work is done for the info_schema views, still...)