SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.256 Pro] - DB Query (SQL Server)

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.256 Pro] - DB Query (SQL Server)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post [SA 9.1.256 Pro] - DB Query (SQL Server) Reply with quote
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


Fri Mar 03, 2017 5:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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.
Fri Mar 03, 2017 11:04 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
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?
Fri Mar 03, 2017 11:26 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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.
Fri Mar 03, 2017 11:55 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
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.
Fri Mar 03, 2017 12:09 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.