SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Modified DB Query (MSSQL) : Object Info

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Modified DB Query (MSSQL) : Object Info
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Modified DB Query (MSSQL) : Object Info Reply with quote
Here is a slightly altered version of the Object Info (MSSQL) DB Query. It retrieves additional information about tables (row number, reserved/data/index/unused size).

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(NVARCHAR, 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(nvarchar, o.create_date) + CHAR(10) +
        'Last modified: ' + CHAR(9) + CASE WHEN o.modify_date IS NULL THEN 'none' ELSE convert(nvarchar, 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(NVARCHAR, 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(nvarchar(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(nvarchar(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(nvarchar, c.length) end + '')''
                     when bt.name in (''nvarchar'', ''nchar'')
                       then ''('' + case c.length when -1 then ''max'' else convert(nvarchar, c.length / 2) end + '')''
                     when bt.name in (''decimal'', ''numeric'')
                       then ''('' + convert(nvarchar, c.prec) + '','' + convert(nvarchar, 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


Tue Jul 01, 2014 6:55 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository 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.