select sql table names using column count and name

Question

I am having total 10 tables in my SQL Server database. Out of that 5 table have 21 columns and the first 5 column and last 3 column are of same name.

How can I write a SQL query which will select me the name of these 5 tables.

[Selecting table names using column count and column name from a SQL Server database]

Eg :-

Database Name - SampleDB

Tables available in SampleDB are

1. dbo.sample1
2. dbo.sample2
3. dbo.sample3
4. dbo.sample4
5. dbo.sample5
6. dbo.sample6
7. dbo.sample7
8. dbo.sample8
9. dbo.sample9
10. dbo.sample10

In this the tables[dbo.sample1,dbo.sample4,dbo.sample5,dbo.sample7,dbo.sample9] contains 21 columns each.Also the first 5 column name and last 3 column name are same for these 5 tables. I need a Query which will select the name of these 5 tables.

Output will be like

1 dbo.sample1
2 dbo.sample4
3 dbo.sample5
4 dbo.sample7
5 dbo.sample9

Is it make sense?

Answer 1

i write MS SQL 2012 Server syntax (TSQL)

Fist step solving your problem is to find out, wich tables have x (21) fields.

SELECT
    So.Name AS TableNames,
    COUNT(Sc.Name) AS FieldCounter
FROM
    Sysobjects AS So    -- List of Tables
LEFT OUTER JOIN
    SysColumns AS Sc    -- List of Fields
ON So.id = sc.ID
WHERE
    So.xtype = 'U'      -- only show for **U**ser sables
GROUP BY
    So.name
HAVING COUNT(Sc.Name) = 21 -- 21 fields in table

After this you have to compare the filed names