[BUG] [SQL Server] Getting definition of a view

Hi,

HeidiSQL is using a SQL like

SELECT VIEW_DEFINITION 
  FROM INFORMATION_SCHEMA.VIEWS 
 WHERE TABLE_NAME='XXX' 
   AND TABLE_SCHEMA='XXX';

for getting view's definition, but INFORMATION_SCHEMA has a column limit of 4000, which cause truncation of the view definition. The correct SQL to get the full definition is

SELECT MODS.DEFINITION
  FROM SYS.OBJECTS OBJ
  JOIN SYS.SQL_MODULES MODS 
    ON OBJ.OBJECT_ID = MODS.OBJECT_ID
 WHERE OBJ.TYPE = 'V'
   AND OBJ.NAME = 'CIMA_MEDICAMENTOS'

Regards,

Fernando

Thanks for the good hint!

I'm just wondering where the schema name comes into account in that query? How does MSSQL know you want the definition of CIMA_MEDICAMENTOS from schema a, not b?

You can join with SYS.SCHEMAS

SELECT MODS.DEFINITION
  FROM SYS.OBJECTS OBJ
  JOIN SYS.SQL_MODULES MODS 
    ON OBJ.OBJECT_ID = MODS.OBJECT_ID
  JOIN SYS.SCHEMAS SCHS
    ON OBJ.SCHEMA_ID = SCHS.SCHEMA_ID
 WHERE OBJ.TYPE = 'V'
  AND SCHS.NAME = 'XXX'
   AND OBJ.NAME = 'XXXXXXCCC'

r5072 uses the above query on MSSQL to get the VIEW code. For me it works, on a local MSSQL 2014 server. Please also check if that works for you. I'm wondering if that works on older versions too?

It works fine in SQL Server 2012 too.

Thanks!!!