SQL Server information_schema.views Length Limitation and rails_sql_views

Recently I was running into a problem with a plugin called rails_sql_views (part of the Active Warehouse project). This is a great plugin created by some friends of mine that helps you manage views in a rails project. The plugin manages the proper creation of the view in the schema.rb file. For the SQL Server adapter the code to produce view comes from a query that looks like this:



The problem was that the view queries were being cut off. The cut off point seemed to be around 4000 characters. I mentioned this to my colleague Semergence and he recommended I look into the column definition for the view_definition column in the information_schema.views table. Sure enough, the column was defined as a nvarchar(4000). Who would be crazy enough to create a view that required more than 4000 characters right? Well, errrr, me I guess.

So armed with this 4000 number my google searches became very effective and turned up this from Joseph Scott. In the blog article Joseph reveals the sp_helptext stored procedure. Using this stored procedure you can easily get the full text of a views create statement, however, you do have to reconstruct it as it returns the result in separate rows under the key "Text". Here is the simple code: