I am trying to find out the number of records from all the columns at sys.tables that start with a specific letter.

At the moment I have something like this:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c JOIN sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE 'u%'
ORDER BY    TableName
            ,ColumnName;

Is there a way I can add the number of column records to this?

Tks, Miguel

CodePudding user response：

Try this:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName',
            count(*) AS num
                        
FROM        sys.columns c JOIN sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE 'u%'
GROUP BY    c.name, t.name
ORDER BY    TableName
            ,ColumnName;

CodePudding user response：

Difficult to understand what number of column records means, but here are two options.

1. Total count

   SELECT TotalColumns = COUNT(*)
     FROM sys.columns
     WHERE name LIKE N'u%'; -- always use N prefix for system metadata
   

2. Count per table

   SELECT t.name, TotalColumns = COUNT(*)
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.name LIKE N'u%'
      GROUP BY t.name;
   

If you don't mean either of those things, then please show us an example of what you want as output.