In this post, I am sharing one script to find a Table which has more than 25 columns in SQL Server.
As per the MSDN, we can create 1024 columns in a single table, but here immediately next questions should be,
What is the default size of one single page?
It is only 8KB so if we create more columns and it exceeds 8KB size, it creates internal page fragmentation.
I am not totally againts on this, but we should analyze before creating more columns in a table which gives us better performance.
Below is a script to find tables which are having more columns:
DECLARE @threshold INT;
SET @threshold = 25;
;WITH cte([object_id], [column count]) AS
SELECT [object_id], COUNT(*)
GROUP BY [object_id]
HAVING COUNT(*) > @threshold
[s].[name] + '.' + [t].[name] AS TableName
INNER JOIN sys.tables AS t
ON [cte].[object_id] = [t].[object_id]
INNER JOIN sys.schemas AS s
ON [t].[schema_id] = [s].[schema_id]
ORDER BY [cte].[column count] DESC