Modified DB Query (MSSQL) : Object Info

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