-1

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

Miguel Lourenço
  • 1
  • 2
    What do you mean by "column records"? "Record" isn't a *thing* is SQL Server. What are you defining a record as? – Thom A Oct 07 '21 at 10:14
  • Number of rows it is. – Miguel Lourenço Oct 07 '21 at 10:20
  • `COUNT(*)` maybe? – Charlieface Oct 07 '21 at 10:20
  • 1
    You can get the (estimated) number of rows a table has using the sys objects as well, @MiguelLourenço . A column can't have more (or less) rows that the table does, as *every* column in a row has a value. Have a look at [this answer](https://stackoverflow.com/a/28917736/2029983) – Thom A Oct 07 '21 at 10:29
  • Can you show us an illustration of what you mean? describe for example a database with 3 tables and several columns in each table, and present what is the output which you want to get according to the sample database. – Ronen Ariely Oct 07 '21 at 11:01

1 Answers1

-2

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;
Khribi Wessim
  • 287
  • 2
  • 12
  • 1
    That's always going to return 1 though... Unless you have tables with the same name and columns on different schemas on the same database. – Thom A Oct 07 '21 at 10:32
  • 1
    Do not use single quote for aliasses. Single quote are for strings values – SQLpro Oct 07 '21 at 15:27