Junaid Junaid - 6 months ago 8
SQL Question

Query to find Size for each table By Filtering Rows

The Following Code will display the Size of each table in database

USE DatabaseName
GO

CREATE TABLE #temp (
table_name SYSNAME
, row_count INT
, reserved_size VARCHAR(50)
, data_size VARCHAR(50)
, index_size VARCHAR(50)
, unused_size VARCHAR(50)
)
SET NOCOUNT ON
INSERT #temp
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
SELECT a.table_name
, a.row_count
, COUNT(*) AS col_count
, a.data_size
FROM #temp a
INNER JOIN INFORMATION_SCHEMA.columns b ON a.table_name COLLATE database_default
= b.table_name COLLATE database_default
GROUP BY a.table_name
, a.row_count
, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS INTEGER) DESC
DROP TABLE #temp


Is there any way to Find the Space of All table by filtering the Row.
Like I have Foreign Key
Company ID
In All Table would it be possible if i want to know the space Occupied by Company

---Example ---

Purchase Table :

P_ID P_Description P_Price P_CompanyID
------------------------------------------
1 Mobile 100 1
2 Laptop 2100 1
3 Table 50 2

Sale table:

S_ID s_Description S_Price S_CompanyID
------------------------------------------
1 Mobile 110 1
2 Laptop 2200 1
3 Table 100 2

OutPut table:

Table Size Company
---------------------------------
Purchase 1.5MB 1
Sale 1.5MB 1
Purchase 1MB 2
Sale 1MB 2

Answer

I have Found the solution as per my Requirement. Thanks to Devart Help.

DECLARE @tblNAME NVARCHAR(50) = ''
DECLARE @colNAME NVARCHAR(50) = ''
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE CUR CURSOR FOR
SELECT NAME
FROM   SYS.TABLES
WHERE  TYPE = 'U'
     AND SCHEMA_ID = 1
     AND name NOT LIKE 'tt_%'

OPEN CUR

FETCH NEXT FROM CUR INTO @tblNAME

WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @colNAME= c.name
        FROM sys.columns c,sys.objects o
        WHERE c.[object_id] = o.[object_id]
            AND c.name like '%_CompanyID' And o.name=@tblNAME
        if @colNAME is null or @colNAME =''
            begin 
             FETCH NEXT FROM CUR INTO @tblNAME
            end
        Else
        Begin    

             -- PRINT @tblNAME
              --Print '---'+ @colNAME



              SELECT @SQL += '

IF OBJECT_ID(''tt_' + o.name + ''') IS NOT NULL
DROP TABLE [tt_' + o.name + ']

SELECT *
INTO [tt_' + o.name + ']
FROM ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name) + '
WHERE '+@colNAME+' =10' -- your condition
FROM sys.objects o
WHERE o.[type] = 'U'
    AND o.name NOT LIKE 'tt_%'
    AND o.name LIKE @tblNAME
    AND o.is_ms_shipped = 0
    AND EXISTS(
        SELECT *
        FROM sys.columns c
        WHERE c.[object_id] = o.[object_id]
            AND c.name like '%_CompanyID' -- your column
        )



          Set @colNAME=null
              FETCH NEXT FROM CUR INTO @tblNAME

             end
  END

CLOSE CUR

DEALLOCATE CUR 


Print @SQL


 EXEC sys.sp_executesql @SQL

SELECT REPLACE(o.name, 'tt_', '') as TableName, t.Size, t.Total_Rows, '1' as IsClient
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , size = SUM(a.total_pages) * 8. / 1024
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1      THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id =     p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    GROUP BY i.[object_id]
) t ON o.[object_id] = t.[object_id]
WHERE o.name LIKE 'tt_%'
    AND o.is_ms_shipped = 0
    AND o.[type] = 'U'
    ORDER BY t.size DESC

SET @SQL = ''
SELECT @SQL += '
DROP TABLE [' + o.name + ']'
FROM sys.objects o
WHERE o.[type] = 'U'
AND o.name LIKE 'tt_%'
AND o.is_ms_shipped = 0

EXEC sys.sp_executesql @SQL