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: 1390

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 addition 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 2005%'
or @@version like '%SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%SQL Server 2014%'
begin   
   CREATE TABLE #space_used
   (
      [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 ) )
   )
   
   DECLARE @object_schema_name sysname

   SET @object_schema_name = OBJECT_SCHEMA_NAME(:OBJECT_ID) + '.' + :OBJECT_NAME --(:OBJECT_ID)
   
   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
        LEFT JOIN sys.stats s
            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
                    JOIN sys.types bt
                        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

   DROP TABLE #space_used
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
        LEFT JOIN sys.stats s
            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
                    JOIN sys.types bt
                        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
                  JOIN dbo.systypes bt
                     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.