Tables with more than 30 columns (wide tables)

Query to Identify Tables with More Than 30 Columns in SQL Server

If you're working with a large SQL Server database, it's often helpful to analyze the structure of your tables, particularly when dealing with tables that contain a significant number of columns. This SQL query will help you find all tables that have more than 30 columns.

SQL Query:

DECLARE @threshold INT;
SET @threshold = 30;

WITH cte AS
(
    SELECT [object_id], COUNT(*) [Columns]
    FROM sys.columns
    GROUP BY [object_id]
    HAVING COUNT(*) > @threshold
)
SELECT 
    s.[name] + N'.' + t.[name] [Table],
    c.[Columns]
FROM cte c
INNER JOIN sys.tables t ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
ORDER BY c.[Columns] DESC;

This query performs the following tasks:

1. It defines a threshold (@threshold) set to 30, which filters tables based on the number of columns they contain.
2. It then uses a common table expression (CTE) to count the number of columns per table and returns the tables that exceed this threshold.
3. Finally, it joins the relevant system tables (sys.tables, sys.columns, sys.schemas) to list the tables and their respective column counts, ordered by the column count in descending order.

Additional Resources:

• Querying Statistics for a Single Table
• Getting Fragmentation Information for Tables
• SQL Server Tables and Row Counts
• Understanding Object Dependency in SQL Server

These articles offer additional insights and queries that can help you manage and optimize your SQL Server databases effectively.