Situatie
Mai jos este o procedura ce returneza toate tabelele ce au mai mult de x coloane (care este parametru al procedurii) de pe o baza de date Microsoft SQL Server.
Solutie
create procedure SearchWideTables_prc
@threshold INT
as
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 2 DESC;
Leave A Comment?