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
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.