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:

	q =<<-ENDSQL
	SELECT view_definition FROM information_schema.views
	WHERE table_name = '#{view}'
	ENDSQL

view raw gistfile1.rb hosted with ❤ by GitHub

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:

	q = ActiveRecord::Base.send(:sanitize_sql, ['EXEC sp_helptext ?', view])
	view_def = select_all(q, name).map {|r| r['Text']}.join

view raw gistfile1.rb hosted with ❤ by GitHub