Overcoming The Limitation Of Information_Schema.Views In SQL Server 2000

This morning I went on a hunt to find out where SQL Server 2000 keeps the view definitions for a database. I quickly came across information_schema feature (seems that having information_schema is part of the SQL92 standard) which exposes view details via information_schema.views. I was hoping that would be the end of my journey, but then I ran into a problem.

Go back and look at the details for the Information_Schema.Views, pay close attention to the VIEW_DEFINITION column. It turns out that if the view definition is more than 4000 characters then it returns NULL. Why they didn’t just make it a TEXT field I do not know. Of course one of the two view definitions that I was looking for was returning NULL. At least I had figured out why, now I needed to know how to get around it.

I knew I could not have been the only one to run into this, so it was back to searching for a solution. I eventually came across a post about sp_helptext that gets around the 4000 character limit. It turns out that sp_helptext will not only give you a view definition, but also triggers, functions, check constraints and stored procedures. Quite handy to have around. So I was able to get the view definition I was looking for (broken up across multiple rows) with:


EXEC sp_helptext 'my_view_name'


You’ll still have to put all the rows back together to get the complete view definition, but that it much better than just getting NULL. Of course if I could figure out what the Information_Schema.Views or sp_helptext is looking at to get the actual view definition I could do it myself. So far I haven’t been able to find that information, and I’m not sure that I’m going to spend much time looking since I have a workable solution at this point.

As an added bonus, go back to the Information_Schema.Views page again and take a look at the IS_UPDATABLE column notes. Turns out that it always returns NO.

While it was nice of Microsoft to include the Information_Schema.Views as part of SQL Server 2000, don’t depend on the data they provide, in some cases it will be wrong. I haven’t looked to see if this is something that has been fixed in SQL Server 2005.

UPDATE Tue 21 Mar 2006 @ 4:00pm: Turns out that getting the definitions of the Information_Schema.Views and sp_helptext is fairly simple. They are kept in the master database in SQL Server 2000. Connect to the master database and take a look at the views, you’ll see several that are owned by INFORMATION_SCHEMA. Then take a look at the listing for Stored Procedures. There are tons of sp_* procedures, one of which is sp_helptext. This lead me to running exec sp_helptext 'sp_helptext' to get the definition of sp_helptext. You can also look at it by looking at the properties of sp_helptext, but you’ll be doing a lot scrolling in a little tiny window. Ick.

After looking at the definitions of both I think I’m going to stick with sp_helptext.

UPDATE Tue 21 Mar 2006 @ 4:40pm: Here is the MSDN info on INFORMATION_SCHEMA and more specifically INFORMATION_SCHEMA.VIEWS which includes notes about the limit of 4000 characters to view_definition and that is_updatable always returns no.