UFN_TABLE_STATISTICS

CREATE function UFN_TABLE_STATISTICS()
    returns @DATA table 
        (SCHEMANAME nvarchar(128), 
        TABLENAME nvarchar(128), 
    CATALOGID uniqueidentifier,
        [ROWCOUNT] int, 
        RESERVEDSPACE int, 
        DATASIZE int, 
        INDEXSIZE int, 
    UNUSEDSPACE int,
    ISAUDITTABLE bit,
  ISCUSTOMTABLE bit)
with execute as owner
as
    begin
    insert into @DATA (SCHEMANAME, TABLENAME, CATALOGID, [ROWCOUNT], RESERVEDSPACE, DATASIZE, INDEXSIZE, UNUSEDSPACE, ISAUDITTABLE, ISCUSTOMTABLE)

    SELECT
    a3.name AS [schemaname],
    a2.name AS [tablename],
  case when RIGHT(a2.name, 5) = 'AUDIT' then
    case when exists (select 1 from dbo.TABLECATALOG where TABLENAME + 'AUDIT' = a2.name) then 
      (select top 1 TABLECATALOG.ID from dbo.TABLECATALOG where TABLECATALOG.TABLENAME + 'AUDIT' = a2.name)
    else
      case when exists (select 1 from dbo.CODETABLECATALOG where DBTABLENAME + 'AUDIT' = a2.name) then
        (select top 1 CODETABLECATALOG.ID from dbo.CODETABLECATALOG where CODETABLECATALOG.DBTABLENAME + 'AUDIT' = a2.name)
      end
    end
  else
    (select top 1 TABLECATALOG.ID from dbo.TABLECATALOG where TABLECATALOG.TABLENAME = a2.name)
  end
  CATALOGID,
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.data * 8 AS data,
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
    case when RIGHT(a2.name, 5) = 'AUDIT' then  
    case when exists (select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMNS.TABLE_NAME = a2.name and COLUMNS.COLUMN_NAME = 'AUDITID' and COLUMNS.TABLE_SCHEMA = 'dbo')
        and exists(select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMNS.TABLE_NAME = a2.name and COLUMNS.COLUMN_NAME = 'AUDITRECORDID' and COLUMNS.TABLE_SCHEMA = 'dbo')
        and exists(select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMNS.TABLE_NAME = a2.name and COLUMNS.COLUMN_NAME = 'AUDITKEY' and COLUMNS.TABLE_SCHEMA = 'dbo')
        and exists(select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMNS.TABLE_NAME = a2.name and COLUMNS.COLUMN_NAME = 'AUDITCHANGEAGENTID' and COLUMNS.TABLE_SCHEMA = 'dbo')
        and exists(select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMNS.TABLE_NAME = a2.name and COLUMNS.COLUMN_NAME = 'AUDITDATE' and COLUMNS.TABLE_SCHEMA = 'dbo')
        and exists(select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMNS.TABLE_NAME = a2.name and COLUMNS.COLUMN_NAME = 'AUDITTYPECODE' and COLUMNS.TABLE_SCHEMA = 'dbo') then   
        1   
    else   
        0   
    end  
  else 
    0
  end ISAUDITTABLE,
  (case when coalesce((select coalesce(TABLESPECXML.value('declare namespace bbfa="bb_appfx_table";/bbfa:TableSpec[1]/@IsBuiltIn','bit'), 0) from dbo.TABLECATALOG where TABLECATALOG.TABLENAME = a2.name), 1) = 1 then 0 else 1 end) ISCUSTOMTABLE

FROM
    (SELECT 
        ps.object_id,
        SUM (
            CASE
                WHEN (ps.index_id < 2) THEN row_count
                ELSE 0
            END
            ) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (
            CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
    GROUP BY ps.object_id) AS a1
--if using fulltext catalog or primary XML indexes you need to add in the following data

LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204) --202 = xml_index_nodes, 204 = fulltext_catalog_map

     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'

return 
end