SQL Server select sql table names using column count and name

yk9xbfzb 于 2023-04-04 发布在其他

关注(0)|答案(2)|浏览(168)

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

dbo.sample1
dbo.sample2
dbo.sample3
dbo.sample4
dbo.sample5
dbo.sample6
dbo.sample7
dbo.sample8
dbo.sample9
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?

sql-server

来源：https://stackoverflow.com/questions/36198627/select-sql-table-names-using-column-count-and-name

2条答案

按热度按时间

bvk5enib1#

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

赞(0）回复(0）举报 2023-04-04

svmlkihl2#

Something like this? (in case of SQL Server)

select t.name, c.name, count(*) as duplicate_count from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
group by t.name, c.name
having count(*) > 1

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server select sql table names using column count and name

2条答案

相关问题

热门标签

最新问答