Junaid Junaid - 1 year ago 32
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