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


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 '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?


2 Answers 2


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:


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.

  • 1
    Will read, thanks. ALTER/CREATE is fine with me. The way my app works, it knows if the view was modified/created, etc. In the case if in the original DB this view was Created, then Altered, but it does not exist in the target DB, I can substitute Alter to Create.
    – T.S.
    Feb 6, 2021 at 3:46
  • @T.S. Cool, no problem! Best of luck!
    – J.D.
    Feb 6, 2021 at 3:48
  • Hi again. doing some mods, noticed - after issuing ALTER VIEW... sql_modules still has Create view. Sql server ver. 14.0.3356.20
    – T.S.
    May 6, 2021 at 2:10

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...)

  • Wait a second, are you saying you helped develop the information schema views? :)
    – J.D.
    Feb 7, 2021 at 5:18
  • Hehe, no, that wasn't what I meant to say. (English isn't my native language.) I worked with them, as a user. And as a trainer I recommended using them instead of the system tables. :-) Feb 8, 2021 at 7:38
  • Ah ok, no worries, I understand. :) I've seen your answers on some old Microsoft question boards before so I know you've been around in the SQL community a while, and thought maybe you worked for Microsoft too. lol.
    – J.D.
    Feb 8, 2021 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.