Edit objects (SQL Server 2k8 + MySQL)

This one is for retrieving ddl for different objects such as views, functions (scalar, table or inline table), procedures or triggers (it requires some modifications to DB Queries or simply selecting the table). Far from being perfect or even complete but still much quicker than scanning the tree for the object. I name the snippet 'edit' and it is triggered on last character.

The SQL Server version:

Code:

$$
SET ARITHABORT ON
IF (
@@version LIKE 'Microsoft SQL Server 2005%'
OR @@version LIKE 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%')
BEGIN
DECLARE
   @sql NVARCHAR(MAX)

SELECT
   @sql = ''

SET @sql = (
   SELECT
      STUFF(
         aha.[definition]
         ,CHARINDEX('CREATE' ,aha.[definition])
         ,6
         ,'ALTER'
      )
      + CHAR(10) + 'GO' + CHAR(10) + CHAR(10)
      + CASE
         WHEN aha.disable_trigger <> ''    THEN aha.disable_trigger    ELSE ''
         END + CHAR(10) + CHAR(10)
   FROM
      (
         SELECT
            sm.[definition]
            ,'' AS disable_trigger
         FROM
            sys.all_sql_modules AS sm
         WHERE sm.[object_id] = OBJECT_ID('$OBJECT(ins_qualname)$')

         UNION

         SELECT
            sm.[definition]
            ,CASE
            WHEN tr.is_disabled = 1    THEN 'DISABLE TRIGGER ' + tr.name + ' ON ' + SCHEMA_NAME(t.[schema_id]) + '.' + t.name + CHAR(10) + 'GO'
               + CHAR(10)
               +
               CHAR(10)    ELSE ''
            END AS disable_trigger
         FROM
            sys.tables AS t
         INNER JOIN
            sys.triggers AS tr
               ON tr.parent_id = t.[object_id]
         INNER JOIN
            sys.all_sql_modules AS sm
               ON sm.[object_id] = tr.[object_id]
         WHERE t.[object_id] = OBJECT_ID('$OBJECT(ins_qualname)$')
      ) AS aha
      FOR XML PATH(''), TYPE
   ).value('(./text()[1])' ,'nvarchar(max)')

SELECT
   @sql
END
ELSE
SELECT
STUFF(sc.[text], CHARINDEX('CREATE', sc.[text], 1), 6, 'ALTER')
FROM
[$DB$].dbo.syscomments AS sc
WHERE sc.id = OBJECT_ID('$OBJECT(ins_qualname)$')
ORDER BY
sc.colid
$$

And the MySQL version:

Code:

$$
SELECT
   CONCAT(
      'DROP PROCEDURE IF EXISTS ',
      p.db,
      '.',
      p.name,
      ';\r\n\r\n',
      'DELIMITER %%%\r\n\r\n'
      'CREATE PROCEDURE ',
      p.db,
      '.',
      p.name,
      '\r\n( ',
      cast(p.param_list AS CHAR(10000)),
      ')\r\n',
      cast(p.body AS CHAR(10000)),
      ';\r\n%%%\r\n\r\nDELIMITER ;\r\n'
   )
FROM
   mysql.proc AS p
WHERE p.db = '$DB$'
   AND p.name = '$OBJECT(table, view, proc, func)$'
   AND p.type = 'PROCEDURE'
$$
$$
SELECT
   CONCAT(
      'DROP FUNCTION IF EXISTS ',
      p.db,
      '.',
      p.name,
      ';\r\n\r\n',
      'DELIMITER %%%\r\n\r\n'
      'CREATE FUNCTION ',
      p.db,
      '.',
      p.name,
      '\r\n( ',
      cast(p.param_list AS CHAR(10000)),
      ')\r\nRETURNS ',
      cast(p.returns AS CHAR(10000)),
      '\r\n',
      cast(p.body AS CHAR(10000)),
      ';\r\n%%%\r\n\r\nDELIMITER ;\r\n'
   )
FROM
   mysql.proc AS p
WHERE p.db = '$DB$'
   AND p.name = '$OBJECT(table, view, proc, func)$'
   AND p.type = 'FUNCTION'
$$
$$
SELECT
CONCAT(
   'DROP TRIGGER IF EXISTS ',
   t.TRIGGER_SCHEMA,
   '.',
   t.TRIGGER_NAME,
   ';\r\n\r\nDELIMITER %%%\r\n\r\nCREATE TRIGGER ',
   trigger_name,
   ' ',
   action_timing,
   ' ',
   event_manipulation,
   ' ON ',
   event_object_table,
   ' FOR EACH ',
   action_orientation,
   ' ',
   action_statement,
   ';',
   ';\r\n%%%\r\n\r\nDELIMITER ;\r\n'
)
FROM
information_schema.triggers AS t
WHERE
1 = 1
AND t.trigger_schema = '$DB$' -- volan' -- '$DB$'
AND t.event_object_table = '$OBJECT(table, view, proc, fun)$'
$$
$$
SELECT
CONCAT(
   'DROP VIEW IF EXISTS '
   ,v.TABLE_SCHEMA
   ,'.'
   ,v.TABLE_NAME
   ,';\r\n\r\n'
   ,'CREATE VIEW '
   ,v.TABLE_SCHEMA
   ,'.'
   ,v.TABLE_NAME
   ,' AS \r\n\r\n'
   ,v.view_definition
   )
FROM
information_schema.views AS v
WHERE table_schema = '$DB$'
AND table_name = '$OBJECT(table, view, proc, func)$'
$$