-- Run it from sms database

SET NOCOUNT ON

DECLARE @GlobalTable Table (TableName SYSNAME)
DECLARE @SiteTable Table (TableName SYSNAME)

-- Global tables
INSERT INTO @GlobalTable (TableName)
SELECT 'AdminCategoryMemberships' UNION
SELECT 'AdminCategoryMembershipsAll' UNION
SELECT 'AI_Generic' UNION
SELECT 'Alert' UNION
SELECT 'AlertClass' UNION
SELECT 'AlertTransitions' UNION
SELECT 'AlertVariable' UNION
SELECT 'AlertVariable_G0' UNION
SELECT 'AlertVariable_G1' UNION
SELECT 'AlertVariableClass' UNION
SELECT 'AlertVariableMap' UNION
SELECT 'ArticleData' UNION
SELECT 'BootImgPkg_References' UNION
SELECT 'CEP_CollectionExtendedProperties' UNION
SELECT 'CEP_CollectionVariables' UNION
SELECT 'CEP_ServiceWindows' UNION
SELECT 'CH_HealthCheckInfo' UNION
SELECT 'CH_Settings' UNION
SELECT 'CI_ApplicationModelInfo' UNION
SELECT 'CI_AssignmentTargetedCIs' UNION
SELECT 'CI_AssignmentTargetedGroups' UNION
SELECT 'CI_CategoryInstances' UNION
SELECT 'CI_CategoryInstances_Flat' UNION
SELECT 'CI_CIAssignments' UNION
SELECT 'CI_CICategories' UNION
SELECT 'CI_CICategories_All' UNION
SELECT 'CI_CIDocuments' UNION
SELECT 'CI_CIEULA' UNION
SELECT 'CI_CIStatus' UNION
SELECT 'CI_ConfigurationItemContents' UNION
SELECT 'CI_ConfigurationItemRelations' UNION
SELECT 'CI_ConfigurationItemRelations_Flat' UNION
SELECT 'CI_ConfigurationItems' UNION
SELECT 'CI_ContentFiles' UNION
SELECT 'CI_ContentPackages' UNION
SELECT 'CI_ContentPackages_Unverified' UNION
SELECT 'CI_Contents' UNION
SELECT 'CI_DocumentStore' UNION
SELECT 'CI_LocalizedCategoryInstances' UNION
SELECT 'CI_LocalizedCIClientProperties' UNION
SELECT 'CI_LocalizedCIClientTags' UNION
SELECT 'CI_LocalizedProperties' UNION
SELECT 'CI_Rules' UNION
SELECT 'CI_SDMPackageLocalizedData' UNION
SELECT 'CI_SDMPackageRelations' UNION
SELECT 'CI_SDMPackages' UNION
SELECT 'CI_SettingReferences' UNION
SELECT 'CI_Settings' UNION
SELECT 'CI_Types' UNION
SELECT 'CI_UpdateCategorySubscription' UNION
SELECT 'CI_UpdateCIs' UNION
SELECT 'CI_UpdateSources' UNION
SELECT 'CIContentPackage' UNION
SELECT 'ClientAgentProperty' UNION
SELECT 'ClientAgentProperty_Value' UNION
SELECT 'ClientKeyData' UNION
SELECT 'ClientKeyDataCertExtend' UNION
SELECT 'ClientPushMachin' UNION
SELECT 'ClientPushMachine_G' UNION
SELECT 'ClientSettings' UNION
SELECT 'ClientSettingsAssignments' UNION
SELECT 'Collection' UNION
SELECT 'Collection_Rules' UNION
SELECT 'Collection_Rules_SQL' UNION
SELECT 'Collection_SubCollections' UNION
SELECT 'CollectionOfferList' UNION
SELECT 'Collections_G' UNION
SELECT 'ContentDPMap' UNION
SELECT 'ConversionRules' UNION
SELECT 'DataItem' UNION
SELECT 'DataItemContext' UNION
SELECT 'DataItemProperty' UNION
SELECT 'DeviceDiscoveryTranslation' UNION
SELECT 'DeviceHinvTranslation' UNION
SELECT 'DeviceMPSettings' UNION
SELECT 'DiscItemAgents' UNION
SELECT 'DiscoveryArchitectures' UNION
SELECT 'DiscPropertyDefs' UNION
SELECT 'DistributionPointGroup' UNION
SELECT 'DPGroupCollections' UNION
SELECT 'DPGroupMembers' UNION
SELECT 'DPGroupPackages' UNION
SELECT 'DS_Activities' UNION
SELECT 'DS_Targets' UNION
SELECT 'EN_CertificateAuthorities' UNION
SELECT 'EN_EnrollmentProfiles' UNION
SELECT 'EN_EnrollmentRecords' UNION
SELECT 'EN_EnrollmentRecordState' UNION
SELECT 'EULA_Content' UNION
SELECT 'EULA_LocalizedContent' UNION
SELECT 'GlobalSchemaChangeHistory' UNION
SELECT 'HinvMOFs' UNION
SELECT 'InventoryAction' UNION
SELECT 'InventoryClass' UNION
SELECT 'InventoryClassContext' UNION
SELECT 'InventoryClassProperty' UNION
SELECT 'InventoryClassPropertyURIs' UNION
SELECT 'InventoryClassURIs' UNION
SELECT 'IP_Network_DISC' UNION
SELECT 'Library_ImportedObjects' UNION
SELECT 'LU_CAL_ProductList' UNION
SELECT 'LU_Category' UNION
SELECT 'LU_Category_Local' UNION
SELECT 'LU_CPU' UNION
SELECT 'LU_CPU_Local' UNION
SELECT 'LU_HardwareRequirements' UNION
SELECT 'LU_HardwareRequirements_Local' UNION
SELECT 'LU_MSProd' UNION
SELECT 'LU_SoftwareCode' UNION
SELECT 'LU_SoftwareHash' UNION
SELECT 'LU_SoftwareList' UNION
SELECT 'LU_SoftwareList_Local' UNION
SELECT 'MachineIdGroupXRef' UNION
SELECT 'MEP_MachineExtendedProperties' UNION
SELECT 'MEP_MachineVariables' UNION
SELECT 'MeterRules' UNION
SELECT 'MIG_Client' UNION
SELECT 'MIG_CollectionMembership' UNION
SELECT 'MIG_Entity' UNION
SELECT 'MIG_EntityReference' UNION
SELECT 'MIG_Job' UNION
SELECT 'MIG_JobCategory' UNION
SELECT 'MIG_JobCollection' UNION
SELECT 'MIG_JobEntity' UNION
SELECT 'MIG_SiteInfo' UNION
SELECT 'MIG_SiteMapping' UNION
SELECT 'NAP_ErrorCodeDescription' UNION
SELECT 'NextId' UNION
SELECT 'NextIds_G' UNION
SELECT 'PDF_Package' UNION
SELECT 'PDF_Program' UNION
SELECT 'PendingRegistrationData' UNION
SELECT 'PkgAccess' UNION
SELECT 'PkgProgram' UNION
SELECT 'PkgProgramOS' UNION
SELECT 'PkgPrograms_G' UNION
SELECT 'PkgServer' UNION
SELECT 'PkgServers_G' UNION
SELECT 'PkgStatu' UNION
SELECT 'PkgStatus_G' UNION
SELECT 'PkgStatus_VirtualApp' UNION
SELECT 'PortalInfo' UNION
SELECT 'ProceduresToForward' UNION
SELECT 'ProgramOffer' UNION
SELECT 'ProgramOffers_G' UNION
SELECT 'Quar_QuarantineCIs' UNION
SELECT 'Queries' UNION
SELECT 'RBAC_Admins' UNION
SELECT 'RBAC_Categories' UNION
SELECT 'RBAC_CategoryMemberships' UNION
SELECT 'RBAC_Permissions' UNION
SELECT 'RBAC_RoleOperations' UNION
SELECT 'RBAC_Roles' UNION
SELECT 'RCM_ReplicationLinkStatus' UNION
SELECT 'ReplicationData' UNION
SELECT 'SC_Address' UNION
SELECT 'SC_Address_Property' UNION
SELECT 'SC_Address_PropertyList' UNION
SELECT 'SC_AddressType' UNION
SELECT 'SC_Boundary' UNION
SELECT 'SC_BoundaryType' UNION
SELECT 'SC_ClientCfg_Property' UNION
SELECT 'SC_ClientCfg_PropertyList' UNION
SELECT 'SC_ClientComponent' UNION
SELECT 'SC_ClientComponent_Property' UNION
SELECT 'SC_ClientComponent_PropertyList' UNION
SELECT 'SC_ClientConfiguration' UNION
SELECT 'SC_Component' UNION
SELECT 'SC_Component_Property' UNION
SELECT 'SC_Component_PropertyList' UNION
SELECT 'SC_Configuration' UNION
SELECT 'SC_Configuration_Property' UNION
SELECT 'SC_Configuration_PropertyList' UNION
SELECT 'SC_GlobalProperty' UNION
SELECT 'SC_GlobalProperty_Property' UNION
SELECT 'SC_GlobalPropertyLis' UNION
SELECT 'SC_GlobalPropertyList_G' UNION
SELECT 'SC_GlobalPropertyList_PropertyList' UNION
SELECT 'SC_MISCItem' UNION
SELECT 'SC_MISCItemType' UNION
SELECT 'SC_PublicKeys' UNION
SELECT 'SC_RoleType' UNION
SELECT 'SC_SiteDefinition' UNION
SELECT 'SC_SiteDefinition_Property' UNION
SELECT 'SC_SiteDefinition_PropertyList' UNION
SELECT 'SC_SysResUse' UNION
SELECT 'SC_SysResUse_Property' UNION
SELECT 'SC_SysResUse_PropertyList' UNION
SELECT 'SC_UserAccount' UNION
SELECT 'SC_UserAccount_Property' UNION
SELECT 'SC_UserAccount_PropertyList' UNION
SELECT 'SearchFolders' UNION
SELECT 'ServerData' UNION
SELECT 'Sites_DATA' UNION
SELECT 'SMSContentHash' UNION
SELECT 'SMSPackage' UNION
SELECT 'SMSPackageHash' UNION
SELECT 'SMSPackages_G' UNION
SELECT 'SR_SummaryTask' UNION
SELECT 'SR_SummaryTasks_G' UNION
SELECT 'System_AUX_Info' UNION
SELECT 'System_DISC' UNION
SELECT 'System_IP_Address_ARR' UNION
SELECT 'System_IP_Subnets_ARR' UNION
SELECT 'System_IPv6_Addre_ARR' UNION
SELECT 'System_IPv6_Prefi_ARR' UNION
SELECT 'System_MAC_Addres_ARR' UNION
SELECT 'System_Resource_N_ARR' UNION
SELECT 'System_SMS_Assign_ARR' UNION
SELECT 'System_SMS_Instal_ARR' UNION
SELECT 'System_SMS_Resident_ARR' UNION
SELECT 'System_System_Container_Name_A' UNION
SELECT 'System_System_Group_Name_ARR' UNION
SELECT 'System_System_OU_Name_ARR' UNION
SELECT 'System_System_Rol_ARR' UNION
SELECT 'Templates' UNION
SELECT 'TS_References' UNION
SELECT 'TS_TaskSequence' UNION
SELECT 'Unknown_System_SMS_Assigned_Si' UNION
SELECT 'UnknownSystem_DISC' UNION
SELECT 'Update_SyncStatus' UNION
SELECT 'User_DISC' UNION
SELECT 'User_Group_DISC' UNION
SELECT 'User_Group_Group_OU_Name_ARR' UNION
SELECT 'User_User_Container_Name_ARR' UNION
SELECT 'User_User_Group_Name_ARR' UNION
SELECT 'User_User_OU_Name_ARR' UNION
SELECT 'UserApplicationRequestHistory' UNION
SELECT 'UserApplicationRequests' UNION
SELECT 'UserAppModelSoftwareRequest' UNION
SELECT 'UserMachineRelation' UNION
SELECT 'UserMachineSourceRelation' UNION
SELECT 'UserMachineSources' UNION
SELECT 'UserMachineTypeRelation' UNION
SELECT 'UserMachineTypes' UNION
SELECT 'UserSoftwareRequest' UNION
SELECT 'UserTargetedAppModelSoftware' UNION
SELECT 'UserTargetedSoftware' UNION
SELECT 'VirtualApp' UNION
SELECT 'WebServiceInfo' UNION
SELECT 'WOLScheduleMap' UNION
SELECT 'WOLStatusInfo' UNION
SELECT 'WSUSServerLocations'

-- Site tables
INSERT INTO @SiteTable(TableName)
SELECT 'AlertVariable_S' UNION
SELECT 'AppStateSummary' UNION
SELECT 'CAL_Device' UNION
SELECT 'CAL_User' UNION
SELECT 'CH_ClientSummary' UNION
SELECT 'CH_ClientSummaryHistory' UNION
SELECT 'CH_EvalResults' UNION
SELECT 'CH_PolicyRequestHistory' UNION
SELECT 'CI_AssignmentStatus' UNION
SELECT 'CI_ComplianceHistory' UNION
SELECT 'CI_ComplianceSummary' UNION
SELECT 'CI_CurrentClientData' UNION
SELECT 'CI_CurrentComplianceStatus' UNION
SELECT 'CI_CurrentComplianceStatusMachineInfo' UNION
SELECT 'CI_CurrentErrorDetails' UNION
SELECT 'CI_CurrentRuleDetail' UNION
SELECT 'CI_LatestReceivedStatusID' UNION
SELECT 'ClientDeploymentState' UNION
SELECT 'ClientHealthState' UNION
SELECT 'ClientOfferStatus' UNION
SELECT 'ClientOfferSummary' UNION
SELECT 'CollectedFiles' UNION
SELECT 'CollectionMembers' UNION
SELECT 'Collections_L' UNION
SELECT 'DeploymentSummaryByCollection' UNION
SELECT 'DeviceClientDeploymentState' UNION
SELECT 'DeviceClientHealthState' UNION
SELECT 'DS_ActivityFacts' UNION
SELECT 'DS_ActivityProgress' UNION
SELECT 'DS_Expectations' UNION
SELECT 'DS_TargetPerformance' UNION
SELECT 'FileUsageSummary' UNION
SELECT 'FileUsageSummaryIntervals' UNION
SELECT 'HinvChangeLog' UNION
SELECT 'INSTALLED_SOFTWARE_DATA_Summary' UNION
SELECT 'MonthlyUsageSummary' UNION
SELECT 'Quar_ClientRestrictionHistory' UNION
SELECT 'Quar_ClientRestrictionSummary' UNION
SELECT 'Quar_RestrictionErrorSummary' UNION
SELECT 'RemoteInvSchema' UNION
SELECT 'SoftwareFile' UNION
SELECT 'SoftwareFilePath' UNION
SELECT 'SoftwareInventory' UNION
SELECT 'SoftwareInventoryStatus' UNION
SELECT 'SoftwareProduct' UNION
SELECT 'SoftwareProductMap' UNION
SELECT 'SS_PrereqStatus' UNION
SELECT 'SS_Status' UNION
SELECT 'StatusMessageAttributes' UNION
SELECT 'StatusMessageInsStrs' UNION
SELECT 'StatusMessages' UNION
SELECT 'SummarizationInterval' UNION
SELECT 'Summarizer_Components' UNION
SELECT 'Summarizer_ComponentTallys' UNION
SELECT 'Summarizer_SiteSystem' UNION
SELECT 'Summarizers_Status' UNION
SELECT 'TaskExecutionStatus' UNION
SELECT 'Update_ComplianceStatus' UNION
SELECT 'Update_ComplianceSummary' UNION
SELECT 'Update_DeploymentSummary' UNION
SELECT 'Update_ScanStatus' UNION
SELECT 'Update_SyncStatus' UNION
SELECT 'Users' UNION
SELECT 'WOLApplicableScheduleMap' UNION
SELECT 'WOLClientTimeZones' UNION
SELECT 'WSUSServerLocations'

INSERT INTO @SiteTable(TableName)
SELECT Name FROM sys.tables WHERE RIGHT(Name, 5) in ('_DATA', '_HIST')

DECLARE @DatabaseSize INT  -- MB
DECLARE @DefaultPageSize FLOAT

SELECT @DefaultPageSize = v.low / 1024.0 FROM MASTER..SPT_VALUES V WHERE v.number = 1 AND v.type = 'E'
SELECT @DatabaseSize = ROUND(SUM(Size)* @DefaultPageSize / 1024, 0) FROM sysfiles WHERE STATUS & 0x40 != 0x40

-- All table size
DECLARE @AllTableSize INT -- MB
DECLARE @AllTableCount INT 

SELECT @AllTableCount = COUNT(*), @AllTableSize = SUM(Reserved) / 1024
FROM (
        SELECT SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved
        FROM sys.tables AS T 
        INNER JOIN sys.dm_db_partition_stats AS PS ON PS.object_id = T.object_id
        LEFT JOIN (
                    SELECT 
                        parent_id,
                        SUM(8 * reserved_page_count) AS LOBReserved,
                        SUM(8 * used_page_count) AS LOBUsed
                    FROM sys.dm_db_partition_stats p 
                        INNER JOIN sys.internal_tables it
                        ON p.object_id = it.object_id
                    WHERE it.internal_type IN (202,204)
                    GROUP BY IT.parent_id
                   ) AS LOBDATA
        ON LOBDATA.parent_id = T.object_id 
    GROUP BY T.name
) AS T

-- Global table size
DECLARE @GlobalTableSize INT -- MB
DECLARE @GlobalTableCount INT 

SELECT @GlobalTableCount = COUNT(*), @GlobalTableSize = SUM(Reserved) / 1024
FROM (
        SELECT SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved
        FROM @GlobalTable G 
        INNER JOIN sys.tables AS T ON OBJECT_ID(G.TableName) = T.object_id 
        INNER JOIN sys.dm_db_partition_stats AS PS ON PS.object_id = T.object_id
        LEFT JOIN (
                    SELECT 
                        parent_id,
                        SUM(8 * reserved_page_count) AS LOBReserved,
                        SUM(8 * used_page_count) AS LOBUsed
                    FROM sys.dm_db_partition_stats p 
                        INNER JOIN sys.internal_tables it
                        ON p.object_id = it.object_id
                    WHERE it.internal_type IN (202,204)
                    GROUP BY IT.parent_id
                   ) AS LOBDATA
        ON LOBDATA.parent_id = T.object_id 
    GROUP BY T.name
) AS T

-- Site table size
DECLARE @SiteTableSize INT -- MB
DECLARE @SiteTableCount INT 

SELECT @SiteTableCount = COUNT(*), @SiteTableSize = SUM(Reserved) / 1024
FROM (
        SELECT SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved
        FROM @SiteTable S 
        INNER JOIN sys.tables AS T ON OBJECT_ID(S.TableName) = T.object_id 
        INNER JOIN sys.dm_db_partition_stats AS PS ON PS.object_id = T.object_id
        LEFT JOIN (
                    SELECT 
                        parent_id,
                        SUM(8 * reserved_page_count) AS LOBReserved,
                        SUM(8 * used_page_count) AS LOBUsed
                    FROM sys.dm_db_partition_stats p 
                        INNER JOIN sys.internal_tables it
                        ON p.object_id = it.object_id
                    WHERE it.internal_type IN (202,204)
                    GROUP BY IT.parent_id
                   ) AS LOBDATA
        ON LOBDATA.parent_id = T.object_id 
    GROUP BY T.name
) AS T

-- Summary
SELECT @SiteTableCount AS [Site Table Count]
     , @SiteTableSize AS [Site Table Size (MB)], (@SiteTableSize * 1.0) / (@DatabaseSize * 1.0) * 100.0 AS [Site Table / DB %], (@SiteTableSize * 1.0) / (@AllTableSize * 1.0) * 100.0 AS [Site Table / All Table %]
     , @GlobalTableCount AS [Global Table Count]
     , @GlobalTableSize AS [Globle Table Size (MB)], (@GlobalTableSize * 1.0) / (@DatabaseSize * 1.0) * 100.0 AS [Global Table / DB %], (@GlobalTableSize * 1.0) / (@AllTableSize * 1.0) * 100.0 AS [Global Table / All Table %]