-- table & field structure with data volume
  
  SELECT
  d.[TABLE_SCHEMA] As [schema],
  d.[TABLE_NAME] As [tableName],
  d.[COLUMN_NAME] As [fieldName],
  d.[DATA_TYPE] As [type],
  d.[CHARACTER_MAXIMUM_LENGTH] As [length],
  d.[NUMERIC_PRECISION] As [precision],
  d.[NUMERIC_SCALE] As [scale],
  d.[ORDINAL_POSITION] As [column_id],
  p.[rows] AS [RowCount],
  SUM(a.total_pages) * 8 AS [TotalSpaceKB],
  SUM(a.used_pages) * 8 AS [UsedSpaceKB]
  -- INTO DDIC
  
  FROM INFORMATION_SCHEMA.COLUMNS As d
  LEFT JOIN sys.tables t on t.[name] = d.[TABLE_NAME]
  LEFT JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
  LEFT JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
  
  -- WHERE d.[TABLE_SCHEMA] in ('')

  GROUP BY
  d.[TABLE_SCHEMA],
  d.[TABLE_NAME],
  d.[COLUMN_NAME],
  d.[DATA_TYPE],
  d.[CHARACTER_MAXIMUM_LENGTH],
  d.[NUMERIC_PRECISION],
  d.[NUMERIC_SCALE],
  d.[ORDINAL_POSITION],
  p.[rows]
  ORDER BY
  d.[TABLE_SCHEMA],
  d.[TABLE_NAME],
  d.[ORDINAL_POSITION]