[SA 9.1.256 Pro] - DB Query (SQL Server)

[SA 9.1.256 Pro] - DB Query (SQL Server)

I modified the original DB Query for Object Info so that it could display some index data for a table but it doesn't seem to work. Running it from SQL Editor does provide results though. Still, when invoking it by hovering over the object and clicking Info I only get a short message that says:

	Quote:
	There is no information for 'my_object'

Could you check it, please?

Code:

-- WARNING: This query works only for objects in the current database

IF :DB_NAME IS NOT NULL
IF :DB_NAME != DB_NAME()
BEGIN
SELECT 'WARNING: This query works only for objects in the current database'
RETURN
END

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
begin
DECLARE @object_id INT = :OBJECT_ID
DECLARE @schema_name SYSNAME = OBJECT_SCHEMA_NAME(@object_id)
DECLARE @object_schema_name SYSNAME = OBJECT_SCHEMA_NAME(@object_id) + '.' + OBJECT_NAME(@object_id)
DECLARE @object_name SYSNAME = OBJECT_NAME(@object_id)
DECLARE @space_used TABLE
(
   [name] SYSNAME NOT NULL
   ,[rows] INT NOT NULL
   ,[reserved_c] VARCHAR( 32 ) NOT NULL
   ,[reserved_kb] AS CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[reserved_mb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[reserved_gb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
   ,[data_c] VARCHAR( 32 ) NOT NULL
   ,[data_kb] AS CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[data_mb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[data_gb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
   ,[index_size_c] VARCHAR( 32 ) NOT NULL
   ,[index_size_kb] AS CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[index_size_mb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[index_size_gb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
   ,[unused_c] VARCHAR( 32 ) NOT NULL
   ,[unused_kb] AS CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[unused_mb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[unused_gb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
)

INSERT INTO @space_used
(
   [name]
   ,[rows]
   ,[reserved_c]
   ,[data_c]
   ,[index_size_c]
   ,[unused_c]
)
EXECUTE sp_spaceused @object_schema_name

SELECT '* Table info*' + ' - ' + @object_schema_name + CHAR(10) +
'Object created: ' + CHAR(9) + convert(varchar, o.create_date) + CHAR(10) +
'Last modified: ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(varchar, o.modify_date) END + CHAR(10) +
CASE WHEN s.auto_created = 1
THEN 'Stats mode: ' + CHAR(9) + 'automatically updated'
ELSE 'Stats mode: ' + CHAR(9) + 'user updated'
END + CHAR(10) +
'Stats time: ' + CHAR(9) + isnull(convert(VARCHAR, STATS_DATE(o.[object_id], s.stats_id)), 'never')
FROM sys.objects o WITH (NOLOCK)
LEFT JOIN sys.stats s WITH (NOLOCK)
ON s.[object_id] = o.[object_id]
AND s.stats_id = 1
WHERE o.[object_id] = @object_id

UNION ALL
SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value])
FROM fn_listextendedproperty (default, 'schema', @schema_name, 'table', @object_name, default, default)

UNION ALL

SELECT
   ISNULL(
   CHAR( 10 ) + '* Table usage*' + CHAR( 10 ) +
      'Rows: ' + CHAR( 9 ) + CAST( su.[rows] AS VARCHAR( 256 ) ) + CHAR( 10 ) +
      'Reserved: ' + CHAR( 9 ) + CASE
            WHEN su.reserved_kb < 1000 THEN CAST( su.reserved_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.reserved_mb < 1000 THEN CAST( su.reserved_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.reserved_gb AS VARCHAR( 256 ) ) + ' GB'
                           END + CHAR( 10 ) +
      'Data: ' + CHAR( 9 ) + CASE
            WHEN su.data_kb < 1000 THEN CAST( su.data_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.data_mb < 1000 THEN CAST( su.data_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.data_gb AS VARCHAR( 256 ) ) + ' GB'
                        END + CHAR( 10 ) +
      'Index size: ' + CHAR( 9 ) + CASE
            WHEN su.index_size_kb < 1000 THEN CAST( su.index_size_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.index_size_mb < 1000 THEN CAST( su.index_size_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.index_size_gb AS VARCHAR( 256 ) ) + ' GB'
                           END + CHAR( 10 ) +
      'Unused: ' + CHAR( 9 ) + CASE
            WHEN su.unused_kb < 1000 THEN CAST( su.unused_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.unused_mb < 1000 THEN CAST( su.unused_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.unused_gb AS VARCHAR( 256 ) ) + ' GB'
                        END + CHAR( 10 )
      ,'gebasz'
   )
FROM
@space_used AS su
UNION ALL
SELECT CHAR(10) + '* Columns*'

UNION ALL
SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type: ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
+ IsNull(convert(varchar(4000), c.[value]), '')
FROM fn_listextendedproperty(default, 'schema', @schema_name, 'table', @object_name, 'column', default) c
FULL JOIN
(SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
sys.all_columns c WITH (NOLOCK)
JOIN sys.types bt WITH (NOLOCK)
ON bt.user_type_id = c.system_type_id
AND bt.system_type_id = c.system_type_id
WHERE c.object_id = @object_id
) ut
ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
WHERE ut.colname IS NOT NULL
OR c.objname IS NOT NULL
END
ELSE IF @@version like '%SQL Azure%'
SELECT '* Table info*' + CHAR(10) +
'Object created: ' + CHAR(9) + convert(varchar, o.create_date) + CHAR(10) +
'Last modified: ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(varchar, o.modify_date) END + CHAR(10) +
CASE WHEN s.auto_created = 1
THEN 'Stats mode: ' + CHAR(9) + 'automatically updated'
ELSE 'Stats mode: ' + CHAR(9) + 'user updated'
END + CHAR(10) +
'Stats time: ' + CHAR(9) + isnull(convert(VARCHAR, STATS_DATE(o.[object_id], s.stats_id)), 'never')
FROM sys.objects o WITH (NOLOCK)
LEFT JOIN sys.stats s WITH (NOLOCK)
ON s.[object_id] = o.[object_id]
AND s.stats_id = 1
WHERE o.[object_id] = @object_id

UNION ALL
SELECT CHAR(10) + '* Columns*'

UNION ALL
SELECT CHAR(10) + ut.colname + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type: ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
FROM (SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
sys.all_columns c WITH (NOLOCK)
JOIN sys.types bt WITH (NOLOCK)
ON bt.user_type_id = c.system_type_id
               AND bt.system_type_id = c.system_type_id
WHERE c.object_id = @OBJECT_ID
) ut
ELSE
BEGIN

DECLARE @OBJECT_ID INT
DECLARE @OBJECT_NAME SYSNAME
DECLARE @SCHEMA_NAME SYSNAME

SELECT @OBJECT_ID = :OBJECT_ID, @SCHEMA_NAME = :SCHEMA_NAME, @OBJECT_NAME = :OBJECT_NAME

EXEC sp_executesql
   N'SELECT CHAR(10) + ''* Table comment*'' + CHAR(10) + convert(varchar(4000), [value])
   FROM ::fn_listextendedproperty (default, ''schema'', @SCHEMA_NAME, ''table'', @OBJECT_NAME, default, default)

   UNION ALL
   SELECT CHAR(10) + ''* Columns*''

   UNION ALL
   SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN ''Base data-type: '' + ut.basetype collate Latin1_General_CI_AS + ''; '' ELSE '''' END
      + IsNull(convert(varchar(4000), c.[value]), '''')
   FROM ::fn_listextendedproperty(default, ''schema'', @SCHEMA_NAME, ''table'', @OBJECT_NAME, ''column'', default) c
      FULL JOIN
         (SELECT c.name AS colname,
               bt.name + case
                  when bt.name in (''varchar'', ''char'', ''binary'', ''varbinary'')
                     then ''('' + case c.length when -1 then ''max'' else convert(varchar, c.length) end + '')''
                  when bt.name in (''nvarchar'', ''nchar'')
                     then ''('' + case c.length when -1 then ''max'' else convert(varchar, c.length / 2) end + '')''
                  when bt.name in (''decimal'', ''numeric'')
                     then ''('' + convert(varchar, c.prec) + '','' + convert(varchar, c.scale) + '')''
                  else ''''
                  end
               AS basetype
         FROM
            dbo.syscolumns c WITH (NOLOCK)
               JOIN dbo.systypes bt WITH (NOLOCK)
                  ON c.xtype = bt.xtype
                        AND c.xusertype = bt.xusertype
                     AND c.xusertype != c.xtype
         WHERE c.id = @OBJECT_ID
         ) ut
      ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
   WHERE ut.colname IS NOT NULL
      OR c.objname IS NOT NULL',
   N'@OBJECT_ID INT, @OBJECT_NAME SYSNAME, @SCHEMA_NAME SYSNAME',
   @OBJECT_ID = @OBJECT_ID, @OBJECT_NAME = @OBJECT_NAME, @SCHEMA_NAME = @SCHEMA_NAME
END

Can you please try appending table usage stats to the * Table info * section? I see that space usage stats added to that section in a similar query for Teradata. I'm not sure it knows what to do with ayou custom * Table Usage * section.

I'm not sure I understand what do you want me to do. What do you mean by

	Quote:
	I'm not sure it knows what to do with ayou custom * Table Usage * section.

If I simply add

	Quote:
	UNION ALL SELECT CHAR(10) + '* Table usage*' + CHAR(10)

between the

	Code:
	SELECT '* Table info*' + CHAR(10) + ...

and

	Code:
	SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value]) FROM ...

parts of the original DB Query for Object information, there's a new section * Table Usage * visible when I click Info, hence I think that should work. Is there perhaps a certain number of rows (added by UNION ALL) there as a limit and having more records in the result set will throw it all away?

I don't think there is a limit on number of rows. It expects each column description and comments on a separate rows which makes me think it's flexible.

I'm asking you not to introduce separate section for table usage. Instead add usage details to the existing info section as in Teradata version of the same query.

I might not want to touch this thing at the moment, it seems extremely volatile. By adding code line by line I got all the way to:

Code:

-- WARNING@ This query works only for objects in the current database

IF :DB_NAME IS NOT NULL
IF :DB_NAME != DB_NAME()
BEGIN
SELECT 'WARNING@ This query works only for objects in the current database'
RETURN
END

DECLARE @OBJECT_ID INT
DECLARE @OBJECT_NAME SYSNAME
DECLARE @SCHEMA_NAME SYSNAME
DECLARE @OBJECT_SCHEMA_NAME SYSNAME

SELECT @OBJECT_ID = :OBJECT_ID, @SCHEMA_NAME = :SCHEMA_NAME, @OBJECT_NAME = :OBJECT_NAME;
SELECT @OBJECT_SCHEMA_NAME = @SCHEMA_NAME + '.' + @OBJECT_NAME;

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
BEGIN
DECLARE @space_used TABLE
(
   [name] SYSNAME NOT NULL
   ,[rows] INT NOT NULL
   ,[reserved_c] VARCHAR( 32 ) NOT NULL
   ,[reserved_kb] AS CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[reserved_mb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[reserved_gb] AS CAST( CAST( SUBSTRING( [reserved_c] ,1 ,LEN( [reserved_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
   ,[data_c] VARCHAR( 32 ) NOT NULL
   ,[data_kb] AS CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[data_mb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[data_gb] AS CAST( CAST( SUBSTRING( [data_c] ,1 ,LEN( [data_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
   ,[index_size_c] VARCHAR( 32 ) NOT NULL
   ,[index_size_kb] AS CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[index_size_mb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[index_size_gb] AS CAST( CAST( SUBSTRING( [index_size_c] ,1 ,LEN( [index_size_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
   ,[unused_c] VARCHAR( 32 ) NOT NULL
   ,[unused_kb] AS CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) )
   ,[unused_mb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,3 ) AS DECIMAL( 18 ,2 ) )
   ,[unused_gb] AS CAST( CAST( SUBSTRING( [unused_c] ,1 ,LEN( [unused_c] ) - 3 ) AS DECIMAL( 18 ,2 ) ) / POWER( 10 ,6 ) AS DECIMAL( 18 ,2 ) )
)
;

INSERT INTO @space_used
(
   [name]
   ,[rows]
   ,[reserved_c]
   ,[data_c]
   ,[index_size_c]
   ,[unused_c]
)
EXECUTE sp_spaceused @OBJECT_SCHEMA_NAME
;

SELECT '* Table info*' + CHAR(10) +
'Object created@ ' + CHAR(9) + convert(varchar, o.create_date) + CHAR(10) +
'Last modified@ ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(varchar, o.modify_date) END + CHAR(10) +
CASE WHEN s.auto_created = 1
THEN 'Stats mode@ ' + CHAR(9) + 'automatically updated'
ELSE 'Stats mode@ ' + CHAR(9) + 'user updated'
END + CHAR(10) +
'Stats time@ ' + CHAR(9) + isnull(convert(VARCHAR, STATS_DATE(o.[object_id], s.stats_id)), 'never')
FROM sys.objects o WITH (NOLOCK)
LEFT JOIN sys.stats s WITH (NOLOCK)
ON s.[object_id] = o.[object_id]
AND s.stats_id = 1
WHERE o.[object_id] = @OBJECT_ID
UNION ALL SELECT CHAR(10) + '* Table usage*' + CHAR(10)
UNION ALL
SELECT
   CHAR( 10 ) + '* Table usage2*' + CHAR( 10 )
   + 'Rows: ' + CHAR( 9 ) + CAST( su.[rows] AS VARCHAR( 256 ) ) + CHAR( 10 )
   + 'Reserved: ' + char(10) + 'ahaaaa' + char(10)
/*
   + CASE
            WHEN su.reserved_kb < 1000 THEN CAST( su.reserved_kb AS VARCHAR( 256 ) ) + ' KB'
            -- WHEN su.reserved_mb < 1000 THEN CAST( su.reserved_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.reserved_gb AS VARCHAR( 256 ) ) + ' GB'
                           END + CHAR( 10 )
                           +
      'Data: ' + CHAR( 9 ) + CASE
            WHEN su.data_kb < 1000 THEN CAST( su.data_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.data_mb < 1000 THEN CAST( su.data_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.data_gb AS VARCHAR( 256 ) ) + ' GB'
                        END + CHAR( 10 )
                        +
      'Index size: ' + CHAR( 9 ) + CASE
            WHEN su.index_size_kb < 1000 THEN CAST( su.index_size_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.index_size_mb < 1000 THEN CAST( su.index_size_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.index_size_gb AS VARCHAR( 256 ) ) + ' GB'
                           END + CHAR( 10 ) +
      'Unused: ' + CHAR( 9 ) + CASE
            WHEN su.unused_kb < 1000 THEN CAST( su.unused_kb AS VARCHAR( 256 ) ) + ' KB'
            WHEN su.unused_mb < 1000 THEN CAST( su.unused_mb AS VARCHAR( 256 ) ) + ' MB'
            ELSE CAST( su.unused_gb AS VARCHAR( 256 ) ) + ' GB'
                        END + CHAR( 10 )
      ,'gebasz'
   )
   */
FROM
@space_used AS su
UNION ALL
SELECT CHAR(10) + '* Table comment*' + CHAR(10) + convert(varchar(4000), [value])
FROM fn_listextendedproperty (default, 'schema', @SCHEMA_NAME, 'table', @OBJECT_NAME, default, default)

UNION ALL
SELECT CHAR(10) + '* Columns*'

UNION ALL
SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type@ ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
+ IsNull(convert(varchar(4000), c.[value]), '')
FROM fn_listextendedproperty(default, 'schema', @SCHEMA_NAME, 'table', @OBJECT_NAME, 'column', default) c
FULL JOIN
(SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
sys.all_columns c WITH (NOLOCK)
JOIN sys.types bt WITH (NOLOCK)
ON bt.user_type_id = c.system_type_id
AND bt.system_type_id = c.system_type_id
WHERE c.object_id = @OBJECT_ID
) ut
ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
WHERE ut.colname IS NOT NULL
OR c.objname IS NOT NULL
END
ELSE IF @@version like '%SQL Azure%'
SELECT '* Table info*' + CHAR(10) +
'Object created@ ' + CHAR(9) + convert(varchar, o.create_date) + CHAR(10) +
'Last modified@ ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(varchar, o.modify_date) END + CHAR(10) +
CASE WHEN s.auto_created = 1
THEN 'Stats mode@ ' + CHAR(9) + 'automatically updated'
ELSE 'Stats mode@ ' + CHAR(9) + 'user updated'
END + CHAR(10) +
'Stats time@ ' + CHAR(9) + isnull(convert(VARCHAR, STATS_DATE(o.[object_id], s.stats_id)), 'never')
FROM sys.objects o WITH (NOLOCK)
LEFT JOIN sys.stats s WITH (NOLOCK)
ON s.[object_id] = o.[object_id]
AND s.stats_id = 1
WHERE o.[object_id] = @OBJECT_ID

UNION ALL
SELECT CHAR(10) + '* Columns*'

UNION ALL
SELECT CHAR(10) + ut.colname + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN 'Base data-type@ ' + ut.basetype collate Latin1_General_CI_AS + '; ' ELSE '' END
FROM (SELECT c.name AS colname,
bt.name + case
when bt.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when bt.name in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when bt.name in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end
AS basetype
FROM
sys.all_columns c WITH (NOLOCK)
JOIN sys.types bt WITH (NOLOCK)
ON bt.user_type_id = c.system_type_id
               AND bt.system_type_id = c.system_type_id
WHERE c.object_id = @OBJECT_ID
) ut
ELSE
BEGIN

EXEC sp_executesql
   N'SELECT CHAR(10) + ''* Table comment*'' + CHAR(10) + convert(varchar(4000), [value])
   FROM @@fn_listextendedproperty (default, ''schema'', @SCHEMA_NAME, ''table'', @OBJECT_NAME, default, default)

   UNION ALL
   SELECT CHAR(10) + ''* Columns*''

   UNION ALL
   SELECT CHAR(10) + isnull(c.objname collate Latin1_General_CI_AS, ut.colname collate Latin1_General_CI_AS) + CHAR(9) + CASE WHEN ut.basetype IS NOT NULL THEN ''Base data-type@ '' + ut.basetype collate Latin1_General_CI_AS + ''; '' ELSE '''' END
      + IsNull(convert(varchar(4000), c.[value]), '''')
   FROM @@fn_listextendedproperty(default, ''schema'', @SCHEMA_NAME, ''table'', @OBJECT_NAME, ''column'', default) c
      FULL JOIN
         (SELECT c.name AS colname,
               bt.name + case
                  when bt.name in (''varchar'', ''char'', ''binary'', ''varbinary'')
                     then ''('' + case c.length when -1 then ''max'' else convert(varchar, c.length) end + '')''
                  when bt.name in (''nvarchar'', ''nchar'')
                     then ''('' + case c.length when -1 then ''max'' else convert(varchar, c.length / 2) end + '')''
                  when bt.name in (''decimal'', ''numeric'')
                     then ''('' + convert(varchar, c.prec) + '','' + convert(varchar, c.scale) + '')''
                  else ''''
                  end
               AS basetype
         FROM
            dbo.syscolumns c WITH (NOLOCK)
               JOIN dbo.systypes bt WITH (NOLOCK)
                  ON c.xtype = bt.xtype
                        AND c.xusertype = bt.xusertype
                     AND c.xusertype != c.xtype
         WHERE c.id = @OBJECT_ID
         ) ut
      ON ut.colname collate Latin1_General_CI_AS = c.objname collate Latin1_General_CI_AS
   WHERE ut.colname IS NOT NULL
      OR c.objname IS NOT NULL',
   N'@OBJECT_ID INT, @OBJECT_NAME SYSNAME, @SCHEMA_NAME SYSNAME',
   @OBJECT_ID = @OBJECT_ID, @OBJECT_NAME = @OBJECT_NAME, @SCHEMA_NAME = @SCHEMA_NAME
END

Now if I change that CHAR(10) right before 'ahaaaa' to CHAR(9) it breaks completely.